Daily Dose of Excel

Most objects in Excel’s object model have a Select or Activate method. Sometimes you want to select or activate an object. Other times you need to select or activate an object. The other 99% of the time, you should not be selecting or activating anything.

When Not To Select

When you have code that looks like this

Range(”A1″).Select
Selection.Formula = “=NOW()”

then you have inefficient code. It is generally not necessary to select or activate objects in order to work with them. The Formula property works pretty well when you use it directly with the Range object. You don’t need to use it with the Selection object, and you shouldn’t. The above code snippet should look like this

Range(”A1″).Formula = “=NOW()”

Why Not To Select

Two main reasons: Selecting and activating objects takes time; and the Selection object makes your code harder to read. When you select something, VBA has to perform the task of selecting. That’s one more step than it would have to do compared to working with the object directly. You may not care about that extra milisecond, but I do, and so will the users who use your programs. Also, when you use the Selection object, VBA has to figure out what’s selected. Is it a Range? Is it a Shape? Another milisecond wasted while VBA does its internal housekeeping on the Selection object. Those miliseconds are adding up now.

Look at the two code fragments above. Which is easier to read? If you said the first, then you’re just being difficult - it’s the second. I can look at a line like this

Sheets(”Sheet1″).Name = “Data”

and see right away that we’re changing the name of Sheet1 to Data. Now look at this line

ActiveSheet.Name = “Data”

No problem, we’re changing the name of the ActiveSheet to Data. But which sheet is active? To know that, you need to read back up the code and determine when the last time a sheet was activated. If you’re lucky, it will be the line directly above. If it’s not, start reading. Avoiding selections makes your code easier to read and more self-documenting.

When To Select

I don’t want you to think that I have some personal vendetta against selecting. I do, I just don’t want you to think it. There are definitely times when you want to select. For example, if you run a macro that clears the input cells of a worksheet, you may want to end that macro with statement that selects the first input cell so the user is ready to re-enter data.

Some macros you want to work on the selection, and not a particular object. When you write a macro to change the font color, you generally write it to change the font color of the selected range of cells. In that case, you have to use the Selection object because the user is dictating (by selecting) which object gets the formatting.

Sub ChangeFontColor()
 
    If TypeName(Selection) = “Range” Then
        Selection.Font.ColorIndex = 3
    End If
 
End Sub

If all this sub did was change the font color of a specific cell, it would be of little value. The benefit of the sub is that it works on what the user selects. And it brings up a good point about the Selection object. When you use the Selection object, always check its data type using the TypeName function. If you don’t, some user will select something that you don’t expect and run your sub. You, as the programmer, are responsible for catching those potential problems.

Another pitfall that you need to avoid is trying to select something that you can’t, like a cell on a sheet that’s not active. This code will fail every time

Sheet1.Activate
Sheet2.Range(”A1″).Select

You can’t select a cell unless the sheet it’s on is the ActiveSheet. If you must select a cell, either make sure that the proper sheet is active, or use an unqualified Range object (don’t identify the sheet) so that the ActiveSheet is assumed.

Tomorrow: Navigating The Object Model