For years I’ve been saying don’t Select or Activate in your VBA code unless you really need to. But I’ve forgotten when I need to, so I want to make a list.
Conditional Formatting: When you add conditional formatting, a formula with relative references (no dollar signs) will be relative to the ActiveCell rather than the cell to which you are adding the CF. I think there’s a way around this, but I’ve found it’s just easier to select the cell, apply the CF, and get on with your life.
Sheet2.Range(“B10”).Select
Sheet2.Range(“A1”).FormatConditions.Add xlExpression, , “=G5>1”
End Sub
Zooming: I think Tushar once said about zooming, “Don’t do that”. Good advice. But if you must zoom in VBA, you have to select the sheet and change the Zoom property of the ActiveWindow object. I couldn’t believe this was true, but Jon Peltier confirmed it recently via Twitter. I still think it’s a poor design, but the Excel object model has so few of these oddities that I tend to give it a pass when I come across one.
That’s what I’ve got – two whole instances where selecting is necessary or preferred. I seem to remember something about shapes having to be selected, but I work with shapes even less than zooming, which is to say almost never. When do you have to select or activate?
Freeze panes is another example (at least in xl2003 and older).
Thanks for the reminder, Dick. I’m frequently guilty of an over-reliance on using elements like Activecell, even though I know better. I appreciate your simple prodding to be better.
To get around activating a cell to freeze panes, I always use the .SplitRow and .SplitColumn properties, then set the .FreezePanes to True.
Something like
With ActiveWindow
.SplitRow = 2
.SplitColumn = 2
.FreezePanes = True
End With
End Sub
Of course, this still needs the use of ActiveWindow.
One thing that I recently encountered with Shapes is trying to set the Formula in a shape. The problem is that the macro recorder does it wrong as well! it sets the formula n R1C1, which was failing for me, had to switch the formula to A1 style.
But to get around having to select the shape, I used the deprecated “TextBox” object instead, which does have a .Formula property. So, instead of doing:
Selection.Formula = “=A1”
I did
set txt = ws.TextBoxes(“MyShape”)
txt.Formula = “=A1”
Don’t know why we have to use a deprecated object to be able to do that, but oh well…
Another item: if you are defining a Name with a relative reference, the relative reference will be with respect to the active cell. So, just as with Conditional Formatting in which the formula uses a relative reference, to get the right result you must be sure to have the right ActiveCell.
Set DisplayGridlines to true/false for sheet cant be done without activating the sheet as it is part of the ActiveWindow rather than the sheet
When defining names with relative references I find it easier to use R1C1 rather than A1: its more obvious what you are referring to: that’s why Name Manager has a convenient A1/R1C1 toggle
You have to select the shape in order to get at the shaperange, unless someone knows a way around that.
Jon, unless you consider a “with” as a “Select”…
Dim Obj as Object
Set Obj = Ws_Intro.Pictures.Insert(sImage_Name)
With Obj.ShapeRange
.Top = 2
.Left = 2
.Height = 2
.Width = 2
.ZOrder msoSendToBack
End With
… where “Ws_Intro” is the codename for some worksheet.
So no need for a select in this case.
There are a bunch of things available for ActiveChart that you can’t access in other ways. Haven’t used this for a long time, so I don’t dare to post examples, anyway, selecting a chart may be necessary.
In some cases I have seen that working with a ListObject requires you to select the sheet with the List (AKA table in 2007/2010) first. Especially if you’re trying to paste rows beneath the listobject which you want included in the list.
Avoid cell selection for conditional format by using R1C1 references.
As a relative VBA novice who uses select and activate all the time (Because that’s what the macro recorder showed me and i have copied it, of course), I’d like to hear why it should be avoided?
Is it as important if you write relatively short/simple programs?
Chris: Read http://www.dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/ for some reasons why to avoid. However, the simpler your code, the less important it is to avoid selecting and activating. When your simple, useful code gradually turns into complex, more useful code, you’ll be glad you used good programming practices from the start. As I’ve said before, code should (in order)