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

data-structures, excel, vba

Solution

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

Problem

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

Original source

Related problems