3.5 Recording Macros

In principle, there are two possibilities for creating macros. You can either input the macro at the keyboard, or you can use the command ToolsMacroRecord New Macro to record the sequence of some Excel commands in the form of VBA instructions. In reality, the most frequent way of creating macros is a mixture of the two variants. First you use the macro recorder to create the basic form of the macro, and then you edit the details of the macro with keyboard input.

The great advantage in recording macros is that you are spared the endless search for the correct keywords. Even if the recorded macro represents your ultimate goal only in bare outline, at least the objects, properties, and methods that the recording produces will be useful.

The disadvantages of macro recording are, among others, that Excel often produces unnecessarily elaborate code. In the recording of dialog input, for example, all input possibilities are given (even if only one setting was changed).

Starting and Ending Macros

The recording of macros normally begins in a worksheet with the New Macro command mentioned above. Then you must give the name of the macro to be recorded and into which workbook the macro is to be recorded (usually in "this workbook"). See Figure 3-3.

Figure 3-3: The beginning of macro recording

The macro recording is terminated with ToolsMacroStop Recording or by clicking on the corresponding tool (the small square).

In recording a macro, Excel usually produces a new module. After the recording is done, you can cut and paste the macro's code, if you wish, into another module sheet (via the clipboard) and then delete the now empty module.

The Personal Macro Workbook

Macros that are to belong to a particular workbook should always be recorded in "this workbook." Macros that are to be universally available (for example, for general improvement of Excel's operation or for special tools) should, on the other hand, be saved in the "personal macro workbook." This workbook is saved under the name Personal.xls in the user directory Application Data\Microsoft\Excel\ Xlstart and is loaded automatically when Excel is launched. The macros in this file are therefore always available.

Frequently, the personal macro workbook's window is kept hidden, so as not to take up valuable monitor real estate. This has the additional benefit of protecting this workbook against accidental changes. The workbook can be made visible via WindowUnhide.

Absolute and Relative Macro Recording

During macro recording cell references can be made either relative to the start position or with an absolute address. Which of these two variants is to be chosen depends on the use to which your macro will be put. To switch between the two modes you must use the tool Relative/Absolute Reference on the "Stop Recording" toolbar.

One can switch back and forth between these two macro recording settings as many times as one wishes. The wrong choice of setting is often the reason that a previously recorded macro does not function properly!