VBA Variables

A variable is a named location in memory which serves as a mutable container for data. Variables are used to store, retrieve, and update data in a program. Variables make programs dynamic and flexible because programs can alter their behavior based on the specific values stored in variables. Variables in VBA all have a data type which determines what kind of values they can store. Variables can either store data directly or they can store references to data. Variables that store data directly are called value types. Variables that store references to data are called reference types. Reference types include Objects, Arrays, and User-Defined Types which will each be discussed in their own sections. This section will focus on value type variables.

Explicit Variable Declaration

By default, variables in VBA do not need to be declared. By default, variables which are not declared explicitly have a data type of Variant which allows them to store any other type of data. The data type for undeclared variables can be changed using a DefType statement.

It is best programming practice to explicitly declare all variables. Explicit variable declaration makes code more reliable, more efficient, easier to read, and can catch or avoid potential errors such as typos in variable names. Declaring variables explicitly also makes them available to intellisense and auto code completion which make it easier to write code.

The Option Explicit statement can be added to the top of any code module to strictly enforce explicit variable declaration for that module. Using Option Explicit will cause a compile error when there are any undeclared variables in the module, preventing the code from running.

To have Option Explicit automatically added whenever a module is created navigate to Tools → Options → Editor and check "Require Variable Declaration".

Note: It is recommended to always use Option Explicit.

Declaring Variables

Declaring a variable tells VBA to reserve a space in memory for storing a certain type of data and to give that space a name. When a variable is declared it is given a name, a scope, and a data type.

Variable declaration syntax: {Dim|Static|Public|Private} VariableName [()] [As Type]

Option Explicit

Public Sub Example()

    Dim ExampleVariable1 As Variant
    Dim ExampleVariable2 As Variant
    Dim ExampleVariable3 As Variant

End Sub

Option Explicit

Public a  As Long 'Public Scope
Private b As Long 'Module Scope
Dim c     As Long 'Module Scope

Public Sub Example()

    Dim x    As Long 'Procedure Scope
    Static y As Long 'Procedure Scope. Maintains value between calls.

End Sub

Option Explicit

Public Sub Example()

    Dim i As Long
    Dim d As Double
    Dim s As String
    Dim b As Boolean
    Dim x As Variant
    
    'Variant by default. Same as using "As Variant".
    Dim y 

End Sub

Variable Declaration Best Practices

To improve the readability of code it is best to declare variables using clean style and syntax.

Option Explicit

Public Sub Example()

    'This is bad practice
    Dim i As Long, j As Long 

    'This is best practice
    Dim i As Long
    Dim j As Long

End Sub

Option Explicit

Public Sub Example()

    'i is a Variant and j is a Long
    Dim i, j As Long 

    'Both i and j are Longs
    Dim i As Long, j As Long

End Sub

Option Explicit

Public Sub Example()

    'This is bad form
    Dim MyStringVariable As String
    Dim MyInteger As Integer
    Dim MyDouble As Double

    'This is better form
    Dim MyStringVariable  As String
    Dim MyInteger         As Integer
    Dim MyDouble          As Double

End Sub

Option Explicit

Public Sub Example()

    'Using As Type
    Dim MyStringVariable As String
    Dim MyInteger        As Integer
    Dim MyDouble         As Double

    'Using Type Identifier Characters
    Dim MyStringVariable$
    Dim MyInteger%
    Dim MyDouble#

End Sub

Assigning Variables

Variable assignment is when a variable is given a value. To assign a variable, have the variable name on the left side, use the "=" assignment operator, and have the variable's value on the right side. If Option Explicit is used the variable must be declared before it is assigned.

Option Explicit

Public Sub Example()

    Dim x As Long
    x = 100

End Sub

Variables can be assigned with or without the Let keyword but it is not recommended to use Let.

Option Explicit

Public Sub Example()

    Dim x As Long
    Let x = 100 'Let keyword is not required nor recommended

End Sub

Variables can be assigned literal values or they can be assigned the values of other variables.

Option Explicit

Public Sub Example()

    Dim x As Long
    Dim y As Long

    x = 100
    y = x

    Debug.Print y 'Prints 100

End Sub

Variables can be reassigned throughout a program.

Option Explicit

Public Sub Example()

    Dim x As Long

    x = 100

    Debug.Print x

    x = 200

    Debug.Print x

End Sub

Object variables will be discussed in their own section but it is worth mentioning here that Object variables are assigned using the required Set keyword.

Option Explicit

Public Sub Example()

    Dim c As Collection
    Set c = New Collection

End Sub

Default Values

In VBA, variables are implicitly assigned a default value when they are declared. The specific default value is determined by the variable's data type and is whatever the False-like, or Falsey, value is for the data type. Because variables are implicitly given a value when they are declared, it is not possible for a variable's value to be undefined. The specific default values for each data type are discussed in the data types section.

Option Explicit

Public Sub Example()

    Dim i As Long
    Debug.Print i 'Prints 0

    Dim d As Double
    Debug.Print d 'Prints 0

    Dim b As Boolean
    Debug.Print b 'Prints False

    Dim s As String
    Debug.Print s 'Prints an empty string

End Sub

Using Variables

To use a variable simply refer to the variable by name in the same context as you would refer to a literal value.

Option Explicit

Public Sub Example()

    Dim x As Long
    Dim y As Long
    Dim z As Long

    x = 100
    y = 200
    z = x + y

    Debug.Print z

End Sub