The Macro Recorder in VBA Programming

The macro recorder is a good introduction into the world of VBA programming, but it’s not meant to be your only teacher. It provides a simplistic approach to coding with Excel’s object model, but is by far not a teacher of advanced or efficient programming methods. You can even pick up some bad habits if you rely on it as your only means of learning VBA. Like many other programmers, I did start off with the recorder but eventually moved to the next level.

Here are 10 things I had to learn to take my programming skill up a notch.


1. The Macro Recorder Is a Terrible Teacher, But You Can Learn from It.

I’m not saying to throw out the recorder and never use it again. In truth, most of the time I find it more useful then Microsoft’s help files when I need to look up an object or its properties and methods. Need the code for creating a pivot table? Then go ahead and record it so you can see the objects and steps involved. But then improve the code by using the advice below.


2. Declare Your Variables!

In the early days when RAM was so expensive, every byte counted. That was a major argument for declaring variables: Undeclared variables are of type variant, with a minimum size of 16 bytes, whereas if you declare a variable as type integer, you use only 2 bytes.

Now that high RAM is so common, some have thrown out the argument and don’t bother declaring variables. But then, they’ve forgotten the other reason for variable declaration, one which has saved me a lot of frustration: When you require variable declaration, Excel will point out unknown variables during compilation. And if you mix upper-and lowercase in your variable naming, you can spot mistakes right away, because Excel will keep the case the same for you as you are typing your code.

You have to manually turn on the variable declaration requirement: In the VBE, go to Tools, Options and check the box for Require Variable Declaration. Once that’s done, any new workbooks will have Option Explicit at the top of every module. For your older workbooks, you can type in Option Explicit at the top of a module, forcing variable declaration.


3. There’s No Need to Use Select or Activate.

Probably one of the worst actions the recorder teaches is that objects must be selected before they can be manipulated. If you provide Excel with the specific object you want to manipulate, such as a sheet name or cell address, then you don’t need to activate the sheet or select the cell. So, while the macro recorder provides this:

ActiveCell.FormulaR1C1 = "5"
ActiveCell.FormulaR1C1 = "6"
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"

All that’s really needed is this:

Sheets("Sheet1").Range(“A1”).Value = 5
Sheets("Sheet1").Range(“A2”).Value = 6
Sheets("Sheet1").Range("A3").Formula = "=SUM(A1:A2)"

Not only does this shorten the number of lines, but it also makes it easier to read the code because right away you know what object (Sheet1, cell A1) is being modified. Changing FormulaR1C1 to Value is mainly a style preference; I prefer to use FormulaR1C1 when actually entering an R1C1-style formula. The change to Formula in the last line is also personal; I’m an A1-style gal, most of the time. By including the sheet object (Sheets("Sheet1")) each time, it doesn’t matter what sheet is actually the active sheet. Excel will always update Sheet1. This is a great way to hide data you’re entering from users.


4. Indent Your Code to Improve Readability.

It seems to be such a small matter, but indention of loops goes a long way in making code easier to read. Whenever someone posts unindented code at a forum, the first thing I do is indent it