When accessing individual objects in VBA, it is best to do so as directly as possible—that is, without unnecessary intermediate steps. Unfortunately, many VBA applications follow a pattern like this:
- A workbook is activated.
- A worksheet is activated.
- A range is selected.
- Properties of the selected range are modified or methods are applied to it.
While this approach of activating and selecting is easy to understand, it tends to be slower and more prone to errors.
Here is an example illustrating this less efficient method:
Sub ActivateAndSelect()
ThisWorkbook.Activate
Worksheets("Sheet1").Activate
Range("A7:A9").Select
Selection.Interior.Color = vbYellow
End Sub
By referencing objects directly instead, you create applications that run faster and are easier to maintain. Here is an improved version using direct referencing:
Sub ReferenceDirectly()
ThisWorkbook.Worksheets("Sheet1"). _
Range("A10:A12").Interior.Color = vbYellow
End Sub
At first glance, this VBA code might seem more complex. However, it follows the object hierarchy logically: workbook, worksheet, cell range, property. Such statements can be longer, but they can be split across multiple lines for readability, as demonstrated above.
This method reduces unnecessary activation and selection steps, which improves performance and reduces the chance of runtime errors caused by unexpected selections or activations.