VBA Variables

Filed in Computer Science by on March 15, 2020 0 Comments

When building an application to be used as a platform for others to build on, Software Engineers design what is called a Document Object Model (DOM). The DOM provides the application programming interface (API) developers must use to build programs using their application platform. By design, each application has its own DOM. In Microsoft Visual Basic for Applications (VBA), every MS Office package (e.g. MS Word, Excel, Access, PowerPoint) has its own DOM. For example, Excel has Workbooks and Sheets, whereas MS Access has Tables and Queries.

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.

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.

Examples:

'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

Tags:

About the Author ()

Whether it's programming, troubleshooting, cooking, or playing music, Marshall enjoys being creative.

Leave a Reply

Your email address will not be published. Required fields are marked *