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 millisecond, 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 millisecond wasted while VBA does its internal housekeeping on the Selection object. Those milliseconds 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
Thanks again…this is really helping me.
I’m saving all these in a Word doc and was wondering if you’re going to do something similar…like a spot with all your VBA training sessions in one place for us to stalk.
Oh, Geeze…ignore that last comment…I’m an idiot.
When I started out VBA I was doing a lot of it but I now realize the advantages of not selecting. However, the other day I was struggling with setting a Conditional Formatting in code (Formula related, not cell value) and found that the formulas that the code puts are modified/distorted by Excel unless the first cell in the range that I was trying to do conditional formatting for was selected?
Alok Joshi
Alok: Me too. Selecting a lot when I first started out, that is. If you learn from the recorder, that’s what you get.
That conditional formatting issue you raise is legitimate. I thought I did a post once on how you can use Application.ConvertFormula to get the job done without changing the selection, but i can’t find it now.
Generally the only indication for an activation is to employ ActiveCell property and some other Range properties. The other cases waste “our keyboard” (not so expensive part of the computer, however)
Just kinda stumbled on to this site. Glad I did. I’ve been trying to understand how to write VBA code and one of the first questions I had was “Why and when do I select”? I understand the concepts, but the mechanics just are sinking in. Plan on visting your blog often. Thanks!
A related link on this rather important topic:
Beyond Excel’s recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm
Use R1C1 notation to apply conditional format with formulas in code (& not select cells).
Another great response to the difference of Activate and Select
http://www.analytics-tools.com/2009/10/vba-difference-between-sheetsselect-and.html
Hi Dick,
Great text.
I agree with you about avoiding activating and selecting, and I’m always trying to clean my codes removing activating and selecting, but sometimes I have problems like in the example bellow (maybe I’m doing something wrong, but I don’t know what):
sht.ChartObjects(“Graf_Name”).SeriesCollection(SerieNumber).Delete
‘returns: error 438 “object doesn’t support this property or method”
‘while
sht.ChartObjects(“Graf_Name”).Activate
ActiveChart.SeriesCollection(SerieNumber).Delete
‘works fine.
Andre:
A ChartObject object is different than a Chart object. When you Activate a ChartObject, you make the underlying Chart object the ActiveChart. That’s why the second way works. The first way works if you reference the ChartObject.Chart property
sht.ChartObjects("Graf_Name").Chart.SeriesCollection(SeriesNumber).Delete