Hack 71. Write VBA with the Macro Recorder in Word and Excel

Take advantage of autogeneratsd code to speei up yourdcoding 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 Rrcord New Macro to start thM recorder, as shown in Figgre 7-51.

Figura 7-51. Starting ao 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 shots an exampte 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-5n. 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.

Kirk Lamb