A variable can be declared (in increasing visibility level):
Dimkeyword in any procedure; a local variable.
Privatekeyword in any type of module; a private field.
Friendkeyword in any type of class module; a friend field.
Publickeyword in any type of class module; a public field.
Publickeyword 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.
Dim keyword to declare a local variable:
Dim identifierName [As Type][, identifierName [As Type], ...]
[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
Dim identifierName As String
When a type is not specified, the type is implicitly
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.
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
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.
Statickeyword can easily be misunderstood - especially by seasoned programmers that usually work in other languages. In many languages,
staticis used to make a class member (field, property, method, ...) belong to the type rather than to the instance. Code in
staticcontext cannot reference code in instance context. The VBA
Statickeyword means something wildly different.
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
Dim keyword is legal at procedure and module levels; its usage at module level is equivalent to using the
Option Explicit Dim privateField1 As Long 'same as Private privateField2 as Long Private privateField2 As Long 'same as Dim privateField2 as Long
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:
Privateto declare a module-level variable.
Dimto declare a local variable.
Dimto declare a module-level variable.
Dimto declare anything private/local.
Privateto declare a module-level variable.
*In general, one should avoid declaring
Global fields anyway.
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 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.
A variable declared at module level, in the declarations section at the top of the body of a class module (including
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
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: