VBA Variables

Filed in Software on March 15, 2020

So you are you using Microsoft Office and you want to automate something. In Microsoft Visual Basic for Applications (VBA), every MS Office package (e.g. MS Word, Excel, Access, PowerPoint) has its own DOM or Document Object Model. The DOM provides the application programming interface (API) we can use to build full-blown programs or to simply automate something within Office. Excel has objects with names like “Workbooks” and “Sheets”, whereas MS Access has objects named “Tables” and “Queries.” So VBA is the scripting language for Microsoft Office and it’s helpful to know.

VBA is generally stored in 2 common areas:

  • The object itself (a Sheet in Excel)
  • Or an external Module called a Class

VBA has both local and global variables which are referred to as Procedure and Module Level, respectively.

Procedure level (Local scope)

A local variable is recognized only within the procedure in which it is declared. A local variable can be declared with a Dim or Static statement.


When a local variable is declared with the Dim statement, the variable remains in existence only as long as the procedure in which it is declared is running.


A local variable declared with the Static statement remains in existence the entire time Visual Basic is running. The variable is reset when any of the following occur:

  • The macro generates an untrapped run-time error.
  • Visual Basic is halted.
  • You quit Microsoft Excel.
  • You change the module.

All variables within a local procedures can also be static by declaring the procedure as static.

Static Function RunningTotal(num)
' Code here
End Function

Module Level (Private)

A “module level” variable is recognized among all of the procedures on a single sheet. A module-level variable remains in existence while Visual Basic is running until the module in which it is declared is edited. Module-level variables may be declared with a Dim or Private statement at the top of the module above the first procedure definition. At the module level, there is no difference between Dim and Private.

Module Level (Public)

A public variable is recognized by every module in the active application. A public variable is declared at the top of the module, above the first procedure definition. A public variable is always declared with a “Public” statement. A public variable may be declared in any module sheet.

It is possible for multiple module sheets to have public variables with the same name. To avoid confusion and possible errors, it is a good idea to use unique variable names.


'Declare module level variables (on top)
Dim undoStore(20) As Variant
Dim undoCurrent() As String
Dim numUndos As Integer

Private Sub btn_handle_click()
    'Declare local variables
    Dim numRows As Integer, numCols As Integer
    numRows = ActiveSheet.UsedRange.Rows.Count
    numCols = ActiveSheet.UsedRange.Columns.Count
    'Handle button click
End Sub

In the example image below, the blue button on the right (a shape object) is mapped to the procedure named “btn_handle_click()”, which is in the Workbook Module named “Module1.” For demonstration, the procedure writes the text “Button Clicked” in the Immediate Window using Debug.Print.

Example using a button to run a procedure


About the Author ()

Shep is a GenX'er. Raised during the time before technology became a part of everyday life. Shep wrote essays for English class on a Compaq PC in 1982 and remembers the World's first arrival of 'Copy-&-Paste.' Shep enjoys nature, jogging, reading, and developing creative skills.

Leave a Reply

Your email address will not be published.