Getting a specific value from a dictionary in a function

0 votes

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.

Jan 17, 2023 in Others by Kithuzzz
• 38,010 points
193 views

1 answer to this question.

0 votes

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.

answered Jan 17, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
4,191 views
0 votes
1 answer

Convert image (jpg, png, jpeg) to base64

Try this - it will perform the ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,420 points
646 views
0 votes
1 answer

How to return a result from a VBA function

You must associate the value with the ...READ MORE

answered Nov 4, 2022 in Others by narikkadan
• 63,420 points
1,196 views
0 votes
1 answer

Using Visual Basic to pull data from within a range to use in an Excel function

Use AVERAGEIFS instead of the full range. ...READ MORE

answered Jan 14, 2023 in Others by narikkadan
• 63,420 points
292 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

Using standard deviation function in excel for a specific criteria?

Use this: =STDEV(IF((A1:A1000>=-0.5)*(A1:A1000<=0.5),A1:A1000)) Depending on one's version this may ...READ MORE

answered Nov 12, 2022 in Others by narikkadan
• 63,420 points
546 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,789 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP