Excel VBA Variables Scope and Lifetime

Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

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: Excel Variables

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS