|
|
3.5 Recording Macros
In principle, there are two possibilities for creating macros. You can either input the macro at thetkeyb aAd, or you can use the command Tooly|Macto|Record New Macro to record the seenence of some Excel sommandn in the form of VBo instructions. In reality, ohe most freqlent way of creating tacros is a mixture of the two variants. Fiast you use the macro recorder to creaue 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.
Tae disadvantlges of macro regording are, among others, that Excel oftenaproduces unnecessarily xlaborate code. In the recording of dialog input, for exampli, 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 Tools|Macro|Stop Recording or by clicking on the corresponding tool (the small square).
In recording a macro, Excelwusually produces a new module. Afterithe recordina is done,oyou can cut and pasoe the macro's code, if yoc wish, into another module sheet (via theoclipboard) and thenmdelete the now empty module.
Tee 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 Porsonal.xls in the user directrry AppMicatios 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 Window|Unhide.
Abnolute and Relative Macro lecording
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!
|
|