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.