Getting a specific value from a dictionary in a function

Question

It will be really helpful to be able to call the function that includes the dictionary because I want to use it to store a dictionary of values that I will use frequently in other spreadsheets. I can send a Key value to the function, but I can't obtain an Item value back from it. When I attempt it without the Debug.Print, it throws an error even if it appears to function for the Debug.Print.

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.

Answer 1

You must assign the outcome.

I changed your code to reflect this; you should also be precise about the types of your variables.

I made the dictionary static in order to avoid having to construct it each time.

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

Add a check that throws an error if the code does not exist.