Integrating Excel and Access

Formatting Techniques

There are essentially two formatting methods, which are on a worksheet using VBA. The first method is to learn where all of the formatting options are and write the code from scratch. This method is most effective when doing the same thing many times (formatting numbers as currency, for example); however, trying to remember how to perform every type of formatting becomes a daunting task. So, this brings us to the second method: using the Macro Recorder .

To access the Macro Recorder from the Excel user interface, go to Tools → Macro → Record New Macro. If all you need is the formatting, pick one cell for each type of formatting that you need to perform. When you are done doing the formatting, press the Stop button to stop recording, or go to Tools → Macro → Stop Recording. Go to the Visual Basic Editor to view the code that the Excel Macro Recorder wrote to perform the formatting. This gives you the syntax needed for your code. If you always have the same number of rows, etc., you can just do all the formatting and then save the macro to run in the future. If you need to customize it, the next couple of examples show you how.

Determining how to refer to your range becomes tricky. For example, let's assume that you want to format a column as currency ($0.00). If you are certain that you want every cell in that column to be currency, you can set your range to be that column. In Excel, there is a property called Columns that is available on a Range object and a ...