Excel VBA Variables Scope and Lifetime
Category: [Excel] Demo Available
Excel VBA Variables Scope and Lifetime
The Scope and Lifetime of Excel VBA Variables
Got any Excel Questions? Excel Help.
Excel VBA Variables Lifetime & Scope
In Excel, when coding in VBA, we can use what are know as variables to store information. These variables (as the name suggests) can be varied and changed to store different data information. As soon as a variable loses scope it loses its stored value.
Excel VBA Variables Levels
There are 3 levels at which we can dimension (Dim) variables. These are;
1) Procedure-Level
2) Module-Level
3) Project-Level, Workbook Level, or Public Module-Level
Each of these levels differ in scope and lifetime. This is discussed below
Procedure-Level Variables
These are probably the best known and widely used variables. They are dimensioned (Dim) inside the Procedure itself. See Example below;
Sub MyMacro () Dim lRows as Long 'Code Here End Sub
All variables dimensioned at this level are only available to the Procedure that they are within. As soon as the Procedure finishes, the variable is destroyed.
Module-Level Variables
These are variables that are dimensioned (Dim) outside the Procedure itself at the very top of any Private or Public Module. See Example below;
Dim lRows as Long
Sub MyMacro ()
'Code Here
End Sub
All variables dimensioned at this level are available to all Procedures that they are within the same Module the variable is dimensioned in. Its value is retained unless the Workbook closes or the End Statement is used.
Project-Level, Workbook Level, or Public Module-Level
These variables are dimensioned at the top of any standard public module, like shown below;
Public lRows as Long
All variables dimensioned at this level are available to all Procedures in all Modules. Its value is retained unless the Workbook closes or the End Statement is used.
See also:
| Index to Excel VBA Code |
| Update Links in Excel |
| Stop UserForm From Closing via X |
| Excel VBA Code For Excel UserForms & Controls |
| TextBox for Numbers Only |
| TextBox for Text Only |
| Using Variables in Excel VBA Macro Code |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.