Going On from Here

GOING ON FROM HERE

In this chapter, you’ve learned how to create macros with the help of the macro recorder. As you learn more about the VBA programming language, you’ll notice that the macro recorder often creates more code for a task than you really need. In our MyPolyFont macro, for example, the following lines that the recorder recorded were unnecessary because all you wanted to do was set the font name, point size, and weight:

.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic

The recorder added these lines because it didn’t (and couldn’t) know they weren’t necessary. You can edit them out without changing the functionality of the macro in any way.

Earlier you saw that it is possible, using VBA, to change an object’s property settings (a cell’s font formats, for example) without selecting the cell. Nevertheless, the recorder always selects objects before taking actions that affect those objects. It does so because it must mimic everything you do when you create the recording.

As you acquire more proficiency with VBA, you’ll learn ways to edit the recorder’s code to make it more efficient. As you move toward expertise in VBA, you will probably find yourself creating most of your code directly in the VBE, bypassing the recorder altogether.

Chances are, though, you’ll still return to the recorder now and then. The Excel object model includes so many objects, methods, properties, and arguments, that it’s difficult—perhaps pointless—to try to remember them all. When you can’t remember what property, object, or method is required in a certain programming situation, one of the easiest ways to get the information you need is by turning on the macro recorder, working through by hand the actions that you want to program, and then seeing what code the recorder generates.