CODEWITHSUNDEEP
excelvba
Selkie
Selkie

Reputation: 1255

Is there an easier/better way to use a pre-defined dictionary?

I have a dictionary of numbers corresponding to dates. I have to imagine there's a better method than the one I have to store all the dates, then recall them.

To put it another way, can I make a dictionary in common that's then accessible to other pieces in the code, so I'm not hiding a global dictionary in a function?

 Function Get_Val_Date(Val_Date_Key As Long)
        Dim Date_Dict As Scripting.Dictionary

    Set Date_Dict = New Scripting.Dictionary
    Date_Dict.Add 1, "9/30/1997"
    Date_Dict.Add 2, "9/30/1998"
    Date_Dict.Add 3, "9/30/1999"
    Date_Dict.Add 4, "9/30/2000"
    Date_Dict.Add 10, "9/30/2001"
    Date_Dict.Add 11, "9/30/2002"
    Date_Dict.Add 12, "9/30/2003"
    Date_Dict.Add 13, "9/30/2004"
    Date_Dict.Add 14, "9/30/2005"



    Get_Val_Date = Date_Dict(Val_Date_Key)

 End Function

Upvotes: 2

Views: 54

Answers (1)

Comintern
Comintern

Reputation: 22185

Just cache it the first time you use it:

 'Module level
 Private Date_Dict As Scripting.Dictionary

 Function Get_Val_Date(Val_Date_Key As Long)
    If Date_Dict Is Nothing Then
        Set Date_Dict = New Scripting.Dictionary
        Date_Dict.Add 1, "9/30/1997"
        Date_Dict.Add 2, "9/30/1998"
        Date_Dict.Add 3, "9/30/1999"
        Date_Dict.Add 4, "9/30/2000"
        Date_Dict.Add 10, "9/30/2001"
        Date_Dict.Add 11, "9/30/2002"
        Date_Dict.Add 12, "9/30/2003"
        Date_Dict.Add 13, "9/30/2004"
        Date_Dict.Add 14, "9/30/2005"
    End If

    Get_Val_Date = Date_Dict(Val_Date_Key)

 End Function

If you intend to use the same dictionary in other functions, you can split the initializer out into its own Sub, something like this:

Function Get_Val_Date(Val_Date_Key As Long)
   InitializeDateLookup
   Get_Val_Date = Date_Dict(Val_Date_Key)
End Function

Sub InitializeDateLookup()
    If Date_Dict Is Nothing Then
        Set Date_Dict = New Scripting.Dictionary
        Date_Dict.Add 1, "9/30/1997"
        Date_Dict.Add 2, "9/30/1998"
        Date_Dict.Add 3, "9/30/1999"
        Date_Dict.Add 4, "9/30/2000"
        Date_Dict.Add 10, "9/30/2001"
        Date_Dict.Add 11, "9/30/2002"
        Date_Dict.Add 12, "9/30/2003"
        Date_Dict.Add 13, "9/30/2004"
        Date_Dict.Add 14, "9/30/2005"
    End If
End Sub

Then any procedure that needs it can just call the one-liner InitializeDateLookup.

Upvotes: 5

Related Questions