To explicitly declare variables in VBA, use the Dim statement, followed by the variable name and type. If a variable is used without being declared, or if no type is specified, it will be assigned the type Variant.
Use the Option Explicit statement on first line of a module to force all variables to be declared before usage (see ALWAYS Use “Option Explicit” ).
Always using Option Explicit is highly recommended because it helps prevent typo/spelling errors and ensures variables/objects will stay their intended type.
Multiple variables can be declared on a single line using commas as delimiters, but each type must be declared individually, or they will default to the Variant type.
Dim Str As String, IntOne, IntTwo As Integer, Lng As Long
Debug.Print TypeName(Str) ‘Output: String
Debug.Print TypeName(IntOne) ‘Output: Variant <— !!!
Debug.Print TypeName(IntTwo) ‘Output: Integer
Debug.Print TypeName(Lng) ‘Output: Long
Variables can also be declared using Data Type Character suffixes ($ % & ! # @), however using these are increasingly discouraged.
Other ways of declaring variables are:
|Static CounterVariable as Integer|
When you use the Static statement instead of a Dim statement, the declared variable will retain its value between calls.
|Public CounterVariable as Integer|
Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared.
|Private CounterVariable as Integer|
Private variables can be used only by procedures in the same module.