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
Option Explicit statement on first line of a module to force all variables to be declared before usage (see ALWAYS Use "Option Explicit" ).
Option Explicit is highly recommended because it helps prevent typo/spelling errors and ensures variables/objects will stay their intended type.
Option Explicit Sub Example() Dim a As Integer a = 2 Debug.Print a 'Outputs: 2 Dim b As Long b = a + 2 Debug.Print b 'Outputs: 4 Dim c As String c = "Hello, world!" Debug.Print c 'Outputs: Hello, world! End Sub
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
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.
Dim this$ 'String Dim this% 'Integer Dim this& 'Long Dim this! 'Single Dim this# 'Double Dim this@ 'Currency
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.
Source and more info: