Hack 71. Write VBA with the Macro Recorder in Word and Excel
Take advantage of autogenerated code to speed up your coding efforts.
Let's say you need to work with Word or Excel from within Access. And let's say the project involves writing VBA for Word or Excel that will be used from Access via automation. Well, you don't have to climb as steep a learning curve as you might think. That's because both Word and Excel can generate VBA code automatically.
To make this work, you first turn on the Macro Recorder, perform certain actions in the application, and then stop the recorder. Just select Tools Macro Record New Macro to start the recorder, as shown in Figure 7-51.
Figure 7-51. Starting to record an Excel macro
With the recorder running, you can perform a few actions of entering data and creating a chart. After stopping the recorder (while it is recording, a toolbar with a Stop button is visible), the code is in an Excel code module. Figure 7-52 shows an example of the code Excel generates.
Usually, you'll need to work with the generated code. It will have hardcoded cell references that might not make sense for your application.
Figure 7-52. Excel autogenerated VBA code
However, using the Macro Recorder, you can generate most of what you need and then just edit it to make it work right. Code such as this that works in Excel will run fairly well from Access when a reference is set to the Excel library (everything said here also applies to Word). You will need to make some changes, but this is still a big timesaver.