Excel VBA 1.2

Declaring Variables

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.

Option Explicit

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

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

Private like:

Private CounterVariable as Integer

Private variables can be used only by procedures in the same module.

Be the first to comment

Leave a Reply