Assigning Scope to a Range Object

By assigning scope to a range, a VBA script will know precisely where to implement the range object. This post provides a comprehensive overview of the many approaches to assigning scope.

Overview

This post within the Getting Started with VBA series continues the focus on The Range Object.

The scope of a range identifies the full address of a range. If you think about  the location of a home, at its most specific level it can be identified by a street address, followed a city, then state. In Excel terms, your street address would be the range, while your town and state would be the home’s scope.

When you create a new Excel workbook, the new file is assigned a default name of “Book1.xlsx”. The new workbook will also open directly to worksheet “Sheet1”, with cell “A1” selected. Cell “A1” is the selected range, within the scope of workbook “Book1.xls” and worksheet “Sheet1”.

As is inferred from the previous paragraph, the scope of a range specifies the precise workbook and sheet where a range is located. Given that there can be several workbooks open, each containing several sheets, it’s important to specify exactly where a VBA script should force a range object to read/write/modify.

Default Scope

If a workbook or worksheet is not specified as part of a range’s scope, the current selected workbook and current selected worksheet will be assigned scope by default. This may not be the intended action, so you may want to override these default properties.

Regardless of intentions, it’s still a good habit to specify scope with all ranges. This will ensure that any alterations a script makes to a worksheet will appear as planned. Likewise, if a user has many worksheets open or is performing other actions while a script is running, there is potential for the VBA script to begin modifying the wrong workbook.

The only situation where scope should not be specified is if you intend to have a script run on any open/active workbook or worksheet. In these cases, it’s a good practice to specify the scope as ActiveWorkbook and ActiveSheet (which will be discussed later in this post), just to make intentions clear.

Assigning Scope (Overriding Defaults)

In the previous post, a script was created that copied text from cell A3, then pasted the copied text into cell D3.

Scope has not been specified in this script. This means the code would affect whichever workbook and worksheet is currently in the user’s active state. As previously stated, it’s generally a good practice to specify scope, even when the scope should always be the active workbook/worksheet.

There are a few approaches to properly specifying a range’s scope.

Approach 1: Selecting scope directly.

By selecting scope directly, the VBA script will forcibly select the proper workbook and worksheet through the Excel interface. This isn’t the most efficient way of selecting scope, but it’s a common approach for new VBA users.

As you can see, lines 3 and 4 have been added to (3) select/activate the workbook, and (4) select the worksheet.

This can even be refined slightly by combining lines 3 and 4 into one line, shown as follows:

The fewer selection/activation operations in your script, the more efficient the script will run. Selecting workbook elements, especially in complex scripts, can be processor intensive and lead to system crashes.

Approach 2: Reference object directly.

Even more efficient than the second example provided in approach 1, the range object can be referenced directly.

This approach eliminates the selection/activation operations and runs very quickly, even without a screen flicker.

Approach 3: Specify workbook and worksheet objects.

This approach is a bit more complicated and relates to topics that will be discussed later in this series. The most efficient way to specify a range’s scope is through predefined objects. To do this, the workbook and worksheet will need to be identified as objects prior to running a script.

In this example, line 3 declares the worksheet object “scopeObj”, while line 5 defines the worksheet object as belonging within “Sheet1” inside of Workbook “Book1”. By appending the worksheet object to the Range object, separating with a period, the range will be assigned the scope of the scopeObj worksheet.

This approach is especially useful in large subroutines and functions that reference many workbooks and worksheets. Each worksheet/workbook can be declared and define above the main script, then used repeatedly.

Approach 4: Specify ActiveWorkbook and ActiveWorksheet.

This approach has the identical effect of the default (no scope specified) treatment, but as previously stated, it’s generally a good idea to specify scope regardless. This will provide for consistent code, and make your intentions clear to others who may read or modify the script.

With-End With Statement

VBA provides a useful technique to make code run more efficiently, look cleaner, and reduce duplication. The With-End With statement allows you to specify an instance of an object once, then simply follow with object properties or methods. Seeing that the range’s copy and paste actions are methods within the workbook object, we can reference the workbook as the object, and follow with both methods individually.

While this approach is not particularly necessary on a very small procedure like this, you can begin to understand how this will save effort for larger projects which reference many objects and many methods/properties. This also leads to some efficiency gains in terms of processing.

Declared objects can be used with With-End With statements as well.

Here’s a group of nested With-End With statements that run an operation on two different sheets.

As you can see, the worksheet objects have left the workbook’s With-End With statement and were duplicated into two separate nested With-End With statements to affect two different worksheets within the same  workbook. Given that the worksheets are methods of the workbook object (while also objects of the subsequent methods), they must be preceded by periods.

Other Notes

  • One quirk with referencing a workbook directly (Workbooks(“Book1”)) is that .xls and .xlsx files do not require the file extension and actually will not work if specified. However, workbooks with macros (.xlxm) or any other extensions require the extension (Workbooks(“Book1.xlsm”))

Leave a Reply