excel-vba Tutorial => Declaring Variables

Example

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.

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

 Dim this$  'String
 Dim this%  'Integer
 Dim this&  'Long
 Dim this!  'Single
 Dim this#  'Double
 Dim this@  'Currency

Other ways of declaring variables are:

Static like: 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 like: 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.