Access Hacks

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 ...