Back to index

Qualified References

And Contextual Defaults

VBA uses an object model where each object can have attributes, methods, and subobjects. Those subobjects can also have attributes, methods, and subobjects. The way of identifying which parent a subobject belongs to is qualification.

For example, in a Module, suppose we want to increment the value of a cell. A reference to a Range object on Sheet1 looks like this

Sheet1.Range("A1") = Sheet1.Range("A1") + 1

The Range is a child of Sheet1. VBA uses dot notation to show that Sheet1 is the parent of the referenced range. This is a qualified reference; we are explicitly stating that the parent is Sheet1. It is valid VBA syntax to omit the qualification:

Range("A1") = Range("A1") + 1

but now it means something different. Instead of being a child of Sheet1, the Range now refers to whatever sheet is the active sheet at the time this code is executed. It is equivalent to

ActiveSheet.Range("A1") = ActiveSheet.Range("A1") + 1

This default applies to only Module code modules. It is generally best practice to explicitly qualify your references, and not use a default of ActiveSheet or use ActiveSheet as a qualifier. This is because the code that references the active sheet may depend on the active sheet to be established somewhere else--in the same module, some other module, or by user action--and doesn't have any control over how that happens. This means that later on if there is a change in code that modifies what sheet becomes active, it will create a bug in the referencing code that is hard to trace.

There is an exception to this practice. If you are writing a macro that is executed by the user then it makes sense for it always to refer to the active sheet.

If the code is in a Worksheet module, then the default for unqualified references is that worksheet. In this case it is fine to allow the default.