Scope vs. Extent in VBA

Mike Wolfe -

Scope and extent are closely related, yet distinct, concepts in VBA.

The scope of a variable refers to the portion of the code in which the variable is visible and can be used. The extent of a variable, on the other hand, refers to the duration of time during which the variable exists and retains its value.

To summarize:

  • Scope = Visibility
  • Extent = Lifetime

Scope

There are three levels of scope in VBA:

  1. Procedure-level scope: A variable with procedure-level scope is only visible and can only be used within the procedure in which it is declared. Once the procedure ends, the variable is no longer available. (Also known as local variables.)
  2. (Private) Module-level scope: A variable with module-level scope is visible and can be used within any procedure in the module in which it is declared. The variable remains available as long as the module is open.
  3. Public (module-level) scope: A variable with public scope is visible and can be used in any procedure in any module in the project. The variable remains available as long as the project is open. (Also known as global variables.)

Extent

From the Section 2.3 of the VBA Language Specification:

The span from the time a variable is created to the time it is destroyed is called the extent of the variable.

VBA supports the following extents:

  • Program Extent
  • Module Extent
  • Procedure Extent
  • Object Extent
  • Aggregate Extent

Relationship Between Scope and Extent

In general, wider scopes have longer extents.

For example, a global variable (declared as Public in the header of a standard module) has Module Extent and Public Scope.  This type of variable has one of the longest lifetimes and the widest visibility.

A local variable (declared with Dim inside a procedure) has Procedure Extent and Procedure-Level Scope.  This type of variable has the shortest lifetime and narrowest visibility.

However, a local static variable (declared with Static inside a procedure) has Module Extent and Procedure-Level Scope.  This type of variable has one of the longest lifetimes, but the narrowest visibility.

While this is not an oft-discussed topic, understanding the concepts of scope and extent is critical to writing robust VBA applications that are easy to maintain.

Acknowledgements
  • Article excerpt generated with the help of ChatGPT
  • Portions of this article's body generated with the help of ChatGPT

Cover image created with Microsoft Designer

UPDATED [2023-01-11]: Added the full official names of the three different levels of scope in VBA along with a link to the VBA scope documentation page within the "Scope" section at the top of the article.