Recording Macros

Excel has the capability of recording very simple macros. When we ask Excel to record a macro by selecting Macro Record New Macro from Excel's (not Excel VBA's) Tools menu, it takes note of our keystrokes and converts them into a VBA subroutine (with no parameters).

For example, suppose we record a macro that does a find and replace, replacing the word "macro" by the word "subroutine." When we look in the Projects window under the project in which the macro was recorded, we will find a new subroutine in a standard code module:

Sub Macro1()

' Macrol Macro

' Macro recorded 9/13/98 by sr

Cells.Replace What:="macro", Replacement:="subroutine",

LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End Sub

This is the same code that we might have written in order to perform this find and replace operation.

In certain situations, the macro recorder can serve as a very useful learning tool. If we can't figure out how to code a certain action, we can record it in a macro and cut and paste the resulting code into our own program. (In fact, you might want to try recording the creation of a pivot table.)

However, before you get too excited about this cut-and-paste approach to programming, we should point out that it is not anywhere near the panacea one might hope. One problem is that the macro recorder has a tendency to use ad hoc code rather than code that will work in a variety of situations. For instance, recorded macro code will often refer to the current selection, which may work at the time the macro was recorded but is not of much use in a general setting, because the programmer cannot be sure what the current selection will be when the user invokes the code.

Another problem is that the macro recorder is only capable of recording very simple procedures. Most useful Excel programs are far too complicated to be recorded automatically by the macro recorder.

Finally, since the macro recorder does such a thorough job of translating our actions into code, it tends to produce very bloated code, which often runs very slowly.

0 0

Post a comment