Best practice for creating a public object in Excel-VBA?

Question

What is the best practice for creating an Excel-VBA data object (dictionary, list, etc.) which is accessible by all members of the application? Should it be declared as a separate module or a class module?

For example, I want to create a dictionary object which different subroutines will want to check a user input against (if it contains or not). Should this dictionary object be its own module, class module, or part of the module which contains the subroutines who use it?

Note: this question is an extension of Checking if a value is a member of a list

Answer 1

You can use following construction (declare your myList object as Public in the top of your module):

Public myList As Object

Sub Main()

    Call InitializeList

    'Do something with your Dictionary object

End Sub

Sub InitializeList()
    If Not myList Is Nothing Then Exit Sub

    Set myList = CreateObject("Scripting.Dictionary")

    myList.Add "item1", 1
    myList.Add "item2", 2
    myList.Add "item3", 3
End Sub

Answer 2

VBA can be frustrating to people who are used to nice OOP-friendly languages like Java and C#. We need to accept VBA's limitations and simply do the best we can with what it offers.

What you're describing almost sounds like something you would declare as a Singleton in other languages.

My solution would be to create a "Main" module (not Class module). In there, create a private dictionary, and create a Public accessor function for it. This will allow your other methods - er - functions/subs to access it blindly.

Private pMyList as Scripting.Dictionary

Public Property Get MyList() as Scripting.Dictionary

    If pMyList = Nothing Then
        Set pMyList = new Scripting.Dictionary
        pMyList("One") = "Red"
        pMyList("Two") = "Blue"
        pMyList("Three") = "Green"
    EndIf

    Set MyList = pMyList

End Property

Public Sub Cleanup
    Set pMyList = Nothing
    ' To deallocate arrays, use:
    ' Erase pArray
End Sub

'--------------------------------

Public Sub SomeRandomSubInAnotherModule()

    Dim theList As Scripting.Dictionary

    Set theList = MyList    ' If not yet initialized, will initialize
    ' Do whatever you need to do with theList
    Set theList = Nothing   ' Release the memory

End Sub

BTW, the "Cleanup" subroutine is just good practice. At the end of your macro, you should call the "Cleanup" subroutine to release memory that Excel may have allocated for any objects you've created. For Class Modules, you can put your cleanup code in

Public Sub Class_Terminate()

and it will be called automatically.

Note - the previous code would require you to add the "Microsoft Scripting Runtime" as a reference. This gives you the helpful type hints when you're working with the dictionary while you code. If you don't want to do that for some reason, use this code:

Private pMyList as Object

Public Property Get MyList() as Object

    If pMyList = Nothing Then
        Set pMyList = CreateObject("Scripting.Dictionary")
        pMyList("One") = "Red"
        pMyList("Two") = "Blue"
        pMyList("Three") = "Green"
    EndIf

    Set MyList = pMyList

End Property

Public Sub Cleanup
    Set pMyList = Nothing
End Sub