Last time, we tried to get our head around objects, properties, and methods. It’s one of those subjects that may be tough to understand, but once you get it you’ll wonder what all the fuss was about.

Now that you know about objects, how will you know what object you need (remember there are lots of them available). Excel provides a feature that let’s you record a macro. When you record a macro, Excel keeps track of everything you do and creates a macro. The benefit of this is that you can see which objects, properties, and methods you need to use for a specific task.

There are two things you should know about the recorder: First, there are some things that you cannot record, like use of the MsgBox function and looping. Second, the recorded code is almost never what you want your final code to look like – it’s highly inefficient.

Let’s write a macro that changes the font color of the activcell to red. To figure out which objects and properties we need, we’ll first record a macro. Go to Tools>Macros>Record New Macro

You can use the default settings for recording a macro because, usually, you’ll only be looking at the recorded code to see what you need, not actually using the recorded code. Once you start the recorder, everything (and I mean everything) you do will be recorded, so act judiciously. Excel displays the Stop Recording toolbar. You’ll need the Stop Recording button to tell it that you’re done.

With the recorder running, go to Format>Cells, select the Font tab, change the Color combobox to red, and click OK.

Now that you’ve done what you wanted to do, make sure you click the Stop Recording button to turn off the recorder. Open the VBE (Alt-F11) to view the code that you just created. Here’s what it should look like:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/25/2004 by Dick Kusleika
'
'
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
End Sub

This is a terrible piece of code, but it can be instructive. First, what’s right. We can learn that in order to change the font color, we need to use the Font property of the Selection object. We can further learn that setting the ColorIndex property to 3 will change the font color to red. All good stuff.

Knowing what’s wrong with recorded code is as important as knowing what’s right. And here it is: What if we select a shape instead of a range? The code will fail. What if the range we selected has the font set to Times New Roman? This code will change it to Arial, which may not be what we want. In fact, it changes a lot of things that we really didn’t intend to change.

Now that we know its weaknesses, let’s take the good stuff and write our own macro.

Sub FontColorToRed()
If TypeName(Selection) = "Range" Then
Selection.Font.ColorIndex = 3
End If
End Sub

First, we add an If Then block to make sure a Range has been selected. If Then’s are another thing you can’t get from the recorder. To test the Selection object, we use the TypeName function to determine the data type. If the data type is Range, we know we’re working with a Range and can continue. If it’s not a Range, the (one line of) code in the If block is not executed and we have avoided an error.

You may have noticed that this macro is a bit shorter than the other. We didn’t want to change the font, subscript, and all that other stuff that the recorder recorded, we only wanted to change the font color. All that other stuff was left out of the final macro and only the ColorIndex was used.

When you want to write a macro and you don’t know which objects to use, record your actions as you do it manually and look at the resulting code. The above example is the right way to use the recorder: Learn the objects, properties and methods that you need and rewrite the code properly.

One more thing: I said that everything you do gets recorded. I lied. Excel will not record actions while using an Add-in, like Solver. If you get a blank macro when you try to record, it’s probably because you were using an Add-in.

Tomorrow: Select and Activate