VBA Variables

So you are using Microsoft Office and you want to automate something. Microsoft Visual Basic for Applications (VBA) is the place to do it. Since every MS Office package (e.g. MS Word, Excel, Access, PowerPoint) has its own pre-defined DOM or Document Object Model, it makes it easier to interact with each program in its own unique way. For example, Excel has its own objects with names like “Workbooks” and “Sheets”. Microsoft Access has objects named “Tables” and “Queries.” Word has its own objects and so does Outlook. VBA is the programming language used within Microsoft Office. This note includes a brief overview of variables in the world of VBA and concludes with an example to jog my memory. Every language has its own syntax.

VBA code 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.

Dim

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.

Static

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.

Example:

'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
    Debug.Print "----------------"
    Debug.Print " Button Clicked "
    Debug.Print "----------------"    
    
End Sub

To demonstrate, here is a simple example. In the image below, the blue button on the right (a shape object) is mapped to the procedure named “btn_handle_click()”. The procedure is visible below in the VBA editor (center.) The VBA code is in the Workbook Module named “Module1” (on the left.) The procedure simply writes the text “Button Clicked” in the Immediate Window using Debug.Print.

The “Immediate Window” is a part of the VBA editor and provides a simple way to test your function outputs. Debug.Print simply prints your output to the Immediate Window.

This demonstration shows the connection between the Excel Sheet (front end) and the back end VBA. The shape object is clicked, Excel registers the click, and the code produces the text output “Button Clicked.” It’s pretty basic, but it gets the point across.