VBA Objects

Objects are constructs with state and behavior. Objects are instantiated from Classes. Each object created from a class is called an instance of the class. Classes are are essentially blueprints for objects. Classes define members which can be variables (state) and procedures (behavior). Classes in VBA are defined in Class modules and libraries.

Object Variables

An Object variable stores a reference to an object, not the object itself. Object variables are called reference type variables because they store a reference to an object located somewhere else in memory. An Object variable is assigned an object using the Set statement. Objects variables can be given an explicit class type or can be given the general types Object or Variant.

Object Instantiation

An object is instantiated when a new instance of a class is created in memory. Objects are instantiated using the New keyword or the CreateObject function. Objects can be explicitly instantiated using the Set statement, or they can be declared as auto-instancing object variables that are instantiated automatically when they are first used in a program.

Explicit Instantiation

Objects can be explicitly instantiated in a few ways. An object can be instantiated and assigned to an object variable, an object can be instantiated and passed as an argument to a procedure, and an object can be instantiated in a With statement. Either the New keyword or the CreateObject function can be used to create an object instance.

Public Sub Example()

    '''Using the New keyword

    'Object variable
    Dim Coll As Collection
    Set Coll = New Collection

    'Argument
    Coll.Add New Collection

    'With statement
    With New Collection
        .Add Coll
    End With

End Sub

Public Sub Example()

    '''Using the CreateObject function

    'Object variable
    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

    'Argument
    Dict.Add 1, CreateObject("Scripting.Dictionary")

    'With statement
    With CreateObject("Scripting.Dictionary")
        .Add 1, Dict
    End With

End Sub

Auto-Instancing

Auto-instancing object variables automatically instantiate an object when they are first used in a program. Auto-instancing objects should be used when they are necessary but using explicit instantiation provides more control over the lifetime of objects. Auto-instancing object variables can be set to Nothing but they can never be tested to see if they are Nothing becausing referring to the object variable instantiates the object. To create an auto-instancing object variable, declare the object variable using the New keyword in the same line as the declaration. Although the variable is declared using the New keyword, the object is not instantiated until it is first used.

Public Sub Example()

    'Auto-instancing Collection object declared
    Dim Coll As New Collection

    'Collection object instantiated
    Coll.Add "Hello, World!" 

    'Collection object terminated
    Set Coll = Nothing 

    'Collection object instantiated in conditional statement
    If Coll Is Nothing Then 
        Debug.Print "This will NEVER print"
    End If

End Sub

Object Lifetime

The lifetime of an object begins when the object is instantiated and ends when the object is terminated. An object is terminated when there are no live references to the object. To remove a reference to an object, set any object variables pointing to the object to Nothing. If an object's data members contain a reference to an object, terminating the object containing the reference will also remove the reference to the other object.

Public Sub Example()

    Dim C1 As Collection
    Dim C2 As Collection

    'Both object variables refer to the same Collection object in memory.
    Set C1 = New Collection
    Set C2 = C1 

    'Object still has a live reference to it so it is not removed from memory.
    Set C1 = Nothing  

    'Object has no more live references so it is removed from memory.
    Set C2 = Nothing

End Sub

Public Sub Example()

    Dim C1 As Collection
    Dim C2 As Collection

    'C1 and C2 are references to different object in memory.
    Set C1 = New Collection
    Set C2 = New Collection

    'C1 contains a reference to the object C2 is referencing.
    C1.Add C2

    'The C2 reference is removed but the reference to the object
    'still exists inside C1 so the object is not terminated.
    Set C2 = Nothing

    'Both Collections are terminated. Terminating C1's object
    'also removes the only live reference to C2's object.
    Set C1 = Nothing

End Sub

When a procedure exits, local object variables are unloaded from memory which can cause the objects they reference to be terminated.

Public Sub Example()

    Dim C As Collection
    Set C = New Collection
    
    'C is unloaded when procedure ends and the object is terminated.
End Sub

If an outside reference still exists to an object, the object will not be terminated when a local object variable goes out of scope.

Public Coll As Collection

Public Sub Example()

    Dim C As Collection
    Set C = New Collection

    Set Coll = C

    'C is unloaded but the Public Coll variable still refers to the Collection
    'so the Collection object is not terminated.
End Sub

When an object variable is declared Static, it must be auto-instancing, otherwise setting the variable to a new instance replaces the original reference and terminates the original object.

Public Sub Example()

    Static C As New Collection

    C.Add "Hello, World!"

    Debug.Print C.Count

End Sub

Comparing Objects

The Is keyword can be used to check if two object variables refer to the same object in memory. When objects are compared using Is, the data stored in the object's fields are not compared. A function would need to be created that compares each member of an object, given the object's type.

Public Sub Example()

    Dim C1 As Collection
    Dim C2 As Collection

    Set C1 = New Collection
    Set C2 = New Collection

    Debug.Print C1 Is C2 'Prints: False

    Set C2 = C1

    Debug.Print C1 Is C2 'Prints: True

End Sub

TypeOf...Is

TypeOf...Is can be used to check an object's type. This should be used when an object is declared using the generic Object type and a certain action requires the object to be of a certain type.

Public Sub Example()

    Dim Obj As Object
    Set Obj = New Collection

    If TypeOf Obj Is Collection Then
        Debug.Print Obj.Count
    Else
        Debug.Print "Obj is not Collection object"
    End If

End Sub

Early-Binding and Late-Binding

Objects can be declared and instantiated using early-binding or late-binding. Early-bound objects are declared as a specific class type, whereas late-bound objects are declared with as the generic Object or Variant type. Early-bound objects have a specific type at compile-time while late-bound objects do not have a specific type and can be assigned an object of any type at run-time. Early-bound objects can be instantiated using the New keyword but late-bound objects must be instantiated using the CreateObject function.

Early-Binding

To use early-binding, declare the object's type explicitly.

Public Sub Example()

    Dim Coll As Collection
    Set Coll = New Collection

End Sub

To use early-binding with external libraries, set a reference to the external library in the Visual Basic Editor by navigating to Tools → References.

Public Sub Example()

    Dim Dict As Scripting.Dictionary
    Set Dict = New Scripting.Dictionary

End Sub

Late-Binding

Note: Intellisense is not available for late-bound objects.

To use late-binding, declare the object as type Object or Variant and instantiate the object using the CreateObject function. When using late-binding with the CreateObject function, it is not necessary to set a reference when using an external library.

Public Sub Example()

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

End Sub

When using late-binding, the compiler does not infer the type of object that will be assigned to an object variable so it does not infer the return types of properties and methods of objects. When a method returns an array it is necessary to use open and closed parentheses to call the function before trying to access elements of the returned array, otherwise the compiler may interpret the index as an argument and an error will occur. Because the compiler does not know what type of object the object variable will hold, intellisense and auto-code-completion are not available for late-bound objects.

Public Sub Example()

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

    Dict.Add "Key1", 1

   'Debug.Print Dict.Keys(0), Dict.Items(0) 'Causes an error
    Debug.Print Dict.Keys()(0), Dict.Items()(0)

End Sub

CreateObject and GetObject

The CreateObject and GetObject functions are used to create new object instances or retrieve existing object instances respectively.

CreateObject

The CreateObject function creates a new instance of a class from the class's ProgID. A class's ProgID can be found as an entry in the Windows Registry. ProgID's are in the format [Program].[Component].[Version]. For example, the Dictionary class is defined in the Microsoft Scripting Runtime Library and has the ProgID "Scripting.Dictionary".

Public Sub Example()

    Dim Dict As Object
    Set Dict = CreateObject("Scripting.Dictionary")

End Sub

GetObject

The GetObject function can be used to set an object variable to an existing object.

Public Sub Example()

    'Get running Excel application
    Dim ExcelApp As Object
    Set ExcelApp = GetObject(, "Excel.Application")

    'Get open workbook
    Dim WB As Object
    Set WB = GetObject("C:\example.xlsx")

End Sub