You Usually Don’t Need to Select an Object in Excel VBA

An Excel VBA user asked in a forum recently how to select a sheet in VBA when the sheet name is stored as a variable. The odds are about 95% that he didn’t need to select the worksheet in the first place.

To illustrate, I turned on my macro recorder, selected cell D9, and entered “x” in the cell. Here’s how Excel recorded the macro:

Sub Macro1()
   Range("D9").Select
   ActiveCell.FormulaR1C1 = "x"
End Sub

Excel had to record this macro in two steps because I really did (1) select cell D9, and then I did (2) enter a value into that cell. But in your VBA programs, you seldom need to select an object (like a worksheet, a range, a chart, or whatever) in order to do something with that object. Instead, you can specify the object and then apply the action directly.

To illustrate, this macro enters “x” in the workbook, worksheet, and cell shown, without activating Book1, Sheet1, or cell D9:

Sub Macro2()
   Workbooks("Book1").Worksheets("Sheet1").Range("D9").FormulaR1C1 = "x"
End Sub

Similarly, you could perform two actions, like this:

Sub Macro3()
   With Workbooks("Book1").Worksheets("Sheet1").Range("D9")
      .FormulaR1C1 = "x"
      .Font.Bold = True
   End With
End Sub

There are at least two advantages to writing code that doesn’t select the objects you’re working with. First, your code will work a lot faster. Second, it will be shorter and easier to write.