VBA Variables
Scope
A variable can be declared (in increasing visibility level):
.At procedure level, using the Dim keyword in any procedure; a local variable.
.At module level, using the Private keyword in any type of module; a private field.
.At instance level, using the Friend keyword in any type of class module; a friend field.
.At instance level, using the Public keyword in any type of class module; a public field.
.Globally, using the Public keyword in a standard module; a global variable.
Variables should always be declared with the smallest possible scope: prefer passing parameters to procedures, rather than declaring global variables.
See Access Modifiers for more information.
Local variables
Use the Dim keyword to declare a local variable:
Dim identifierName [As Type][, identifierName [As Type], ...]The [As Type] part of the declaration syntax is optional. When specified, it sets the variable's data type, which determines how much memory will be allocated to that variable. This declares a String variable:
Dim identifierName As StringWhen a type is not specified, the type is implicitly Variant:
Dim identifierName 'As Variant is implicitThe VBA syntax also supports declaring multiple variables in a single statement:
Dim someString As String, someVariant, someValue As LongNotice that the [As Type] has to be specified for each variable (other than 'Variant' ones). This is a relatively common trap:
Dim integer1, integer2, integer3 As Integer 'Only integer3 is an Integer.
'The rest are Variant.Static variables
Local variables can also be Static. In VBA the Static keyword is used to make a variable "remember" the value it had, last time a procedure was called:
Private Sub DoSomething()
Static values As Collection
If values Is Nothing Then
Set values = New Collection
values.Add "foo"
values.Add "bar"
End If
DoSomethingElse values
End SubHere the values collection is declared as a Static local; because it's an object variable, it is initialized to Nothing. The condition that follows the declaration verifies if the object reference was Set before - if it's the first time the procedure runs, the collection gets initialized. DoSomethingElse might be adding or removing items, and they'll still be in the collection next time DoSomething is called.