Getting a specific value from a dictionary in a function-CodePudding

I want to use a function to store a dictionary of values that I will use repeatedly in other spreadsheet thus being able to call this function that contains the dictionary will be extremely useful. However, I'm unable to get an Item value back from the function by passing an Key value. It seems to work for the Debug.Print but when I try it without the debug.print, it throws an error.

Function location_Dict(loc_Code)

    Dim loc_dict As Dictionary
    Set loc_dict = New Dictionary
    
    Debug.Print "In loc_dic and value is " & loc_Code
    
    With loc_dict()
    
        .Add Key:=21, Item:="Alamo, TN"
        .Add Key:=27, Item:="Bay, AR"
        .Add Key:=54, Item:="Cash, AR"
        .Add Key:=3, Item:="Clarkton, MO"
        .Add Key:=42, Item:="Dyersburg, TN"
        .Add Key:=2, Item:="Hayti, MO"
        .Add Key:=59, Item:="Hazel, KY"
        .Add Key:=44, Item:="Hickman, KY"
        .Add Key:=56, Item:="Leachville, AR"
        .Add Key:=90, Item:="Senath, MO"
        .Add Key:=91, Item:="Walnut Ridge, AR"
        .Add Key:=87, Item:="Marmaduke, AR"
        .Add Key:=12, Item:="Mason, TN"
        .Add Key:=14, Item:="Matthews, MO"
        .Add Key:=51, Item:="Newport, AR"
        .Add Key:=58, Item:="Ripley, TN"
        .Add Key:=4, Item:="Sharon, TN"
        .Add Key:=72, Item:="Halls, TN"
        .Add Key:=13, Item:="Humboldt, TN"
        .Add Key:=23, Item:="Dudley, MO"
    
    End With

    Debug.Print loc_dict.Item(loc_Code)

End Function

I would pass loc_Code as "51" as an example and it makes it to the function if I try loc_dict.Item(loc_Code) without debug.print it will not accept it.

CodePudding user response：

You have to assign the result.

I updated your code accordingly - you should also be specific with the variable types.

To not re-create the dictionary every time - I defined it as static.

Function location_Dict(loc_Code As Long) As String

    Static loc_dict As Dictionary
    
    If loc_dict Is Nothing Then
        Set loc_dict = New Dictionary
        With loc_dict
        
            .Add Key:=21, Item:="Alamo, TN"
            '.... deleted to shorten the example
           .Add Key:=23, Item:="Dudley, MO"
        
        End With
    End If

    If loc_dict.Exists(loc_Code) = False Then
        Err.Raise vbObjectError, , loc_Code & " does not exist"
    End If
    
'--->>> this is the important part, so that the function really returns a value
    location_Dict = loc_dict(loc_Code)

End Function

I also added a check that throws an error if the code does not exist.

CodePudding user response：

Another approach to the one above, is to encapsulate everything in a class module and expose only the function to get the code.

Then you can call it like this:

Dim location As String

With New LocationManager
    location = .GetByKey(51)
End With

which is the same as this:

Dim manager As LocationManager
Set manager = New LocationManager

Dim location As String
location = manager.GetByKey(51)

The class named LocationManager. You can rename it as you wish.

Option Explicit

Private m_dictionary As Object

Public Function GetByKey(ByVal Key As Variant) As Variant
    GetByKey = m_dictionary(Key)
End Function

Private Sub Class_Initialize()
    Set m_dictionary = CreateObject("Scripting.Dictionary")
    Build
End Sub

Private Sub Build()
    With m_dictionary
        .Add Key:=21, Item:="Alamo, TN"
        .Add Key:=27, Item:="Bay, AR"
        .Add Key:=54, Item:="Cash, AR"
        .Add Key:=3, Item:="Clarkton, MO"
        .Add Key:=42, Item:="Dyersburg, TN"
        .Add Key:=2, Item:="Hayti, MO"
        .Add Key:=59, Item:="Hazel, KY"
        .Add Key:=44, Item:="Hickman, KY"
        .Add Key:=56, Item:="Leachville, AR"
        .Add Key:=90, Item:="Senath, MO"
        .Add Key:=91, Item:="Walnut Ridge, AR"
        .Add Key:=87, Item:="Marmaduke, AR"
        .Add Key:=12, Item:="Mason, TN"
        .Add Key:=14, Item:="Matthews, MO"
        .Add Key:=51, Item:="Newport, AR"
        .Add Key:=58, Item:="Ripley, TN"
        .Add Key:=4, Item:="Sharon, TN"
        .Add Key:=72, Item:="Halls, TN"
        .Add Key:=13, Item:="Humboldt, TN"
        .Add Key:=23, Item:="Dudley, MO"
    End With
End Sub

Private Sub Class_Terminate()
    Set m_dictionary = Nothing
End Sub

The example above uses late-binding so no reference to the Microsoft.Scripting.Runtime is required.