Caching Object Instances in VBA Classes

When I want to refer to object instances inside of a class module, I tend to use an approach that I call "on-demand initialization":

1. Declare a private object variable
2. Declare a public/private object Property Get
3. Inside the Get, check to see if the private variable is nothing
4. If it is nothing, instantiate the private variable
5. Return the private variable

Here's what this looks like in practice:

'--== clsExcel Class Module ==--
Option Compare Database
Option Explicit

Private mExcelApp As Object  'Excel.Application

Public Property Get ExcelApp() As Object
    If mExcelApp Is Nothing Then
        Set mExcelApp = CreateObject("Excel.Application")
    End If
    Set ExcelApp = mExcelApp
End Property

Private Sub Class_Terminate()
    Set mExcelApp = Nothing
End Sub

Here's some test code to show how it works:

Sub TestExcel()
    Dim xl As clsExcel
    
    Set xl = New clsExcel
    Debug.Print "A: "; xl.ExcelApp.Hwnd
    Debug.Print "B: "; xl.ExcelApp.Hwnd
    Set xl = Nothing
    
    Set xl = New clsExcel
    Debug.Print "C: "; xl.ExcelApp.Hwnd
    Set xl = Nothing
End Sub

Why Bother?

I use this approach for two main reasons:

1. It centralizes the object initialization code
2. It improves performance by eliminating unnecessary initialization

There are a few additional benefits to this approach:

• During development, it helps avoid annoying "Object invalid or no longer set" errors when the code gets reset
• It provides an easy way to reset class state (for classes where it makes sense), by having a Reset method that simply sets all the private object variables to Nothing

Image by www_slon_pics from Pixabay