VBA Declaring Variables Variables

Example

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 String

When a type is not specified, the type is implicitly Variant:

Dim identifierName 'As Variant is implicit

The VBA syntax also supports declaring multiple variables in a single statement:

Dim someString As String, someVariant, someValue As Long

Notice 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 Sub

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

Alternative

VBA's Static keyword can easily be misunderstood - especially by seasoned programmers that usually work in other languages. In many languages, static is used to make a class member (field, property, method, ...) belong to the type rather than to the instance. Code in static context cannot reference code in instance context. The VBA Static keyword means something wildly different.

Often, a Static local could just as well be implemented as a Private, module-level variable (field) - however this challenges the principle by which a variable should be declared with the smallest possible scope; trust your instincts, use whichever you prefer - both will work... but using Static without understanding what it does could lead to interesting bugs.

---

Dim vs. Private

The Dim keyword is legal at procedure and module levels; its usage at module level is equivalent to using the Private keyword:

Option Explicit
Dim privateField1 As Long 'same as Private privateField2 as Long
Private privateField2 As Long 'same as Dim privateField2 as Long

The Private keyword is only legal at module level; this invites reserving Dim for local variables and declaring module variables with Private, especially with the contrasting Public keyword that would have to be used anyway to declare a public member. Alternatively use Dim everywhere - what matters is consistency:

"Private fields"

• DO use Private to declare a module-level variable.
• DO use Dim to declare a local variable.
• DO NOT use Dim to declare a module-level variable.

"Dim everywhere"

• DO use Dim to declare anything private/local.
• DO NOT use Private to declare a module-level variable.
• AVOID declaring Public fields.*

_{*In general, one should avoid declaring Public or Global fields anyway.}

---

Fields

A variable declared at module level, in the declarations section at the top of the module body, is a field. A Public field declared in a standard module is a global variable:

Public PublicField As Long

A variable with a global scope can be accessed from anywhere, including other VBA projects that would reference the project it's declared in.

To make a variable global/public, but only visible from within the project, use the Friend modifier:

Friend FriendField As Long

This is especially useful in add-ins, where the intent is that other VBA projects reference the add-in project and can consume the public API.

Friend FriendField As Long 'public within the project, aka for "friend" code
Public PublicField As Long 'public within and beyond the project

Friend fields are not available in standard modules.

---

Instance Fields

A variable declared at module level, in the declarations section at the top of the body of a class module (including ThisWorkbook, ThisDocument, Worksheet, UserForm and class modules), is an instance field: it only exists as long as there's an instance of the class around.

'> Class1
Option Explicit
Public PublicField As Long

'> Module1
Option Explicit
Public Sub DoSomething()
    'Class1.PublicField means nothing here
    With New Class1
        .PublicField = 42
    End With
    'Class1.PublicField means nothing here
End Sub

---

Encapsulating fields

Instance data is often kept Private, and dubbed encapsulated. A private field can be exposed using a Property procedure. To expose a private variable publicly without giving write access to the caller, a class module (or a standard module) implements a Property Get member:

Option Explicit
Private encapsulated As Long

Public Property Get SomeValue() As Long
    SomeValue = encapsulated
End Property

Public Sub DoSomething()
    encapsulated = 42
End Sub

The class itself can modify the encapsulated value, but the calling code can only access the Public members (and Friend members, if the caller is in the same project).

To allow the caller to modify:

• An encapsulated value, a module exposes a Property Let member.
• An encapsulated object reference, a module exposes a Property Set member.