VBA function to search for item contained in a Dictionary

Question

I declare a Dictionary in VBA and fill it in with keys and values:

Sub UseDictionary()
Dim Z As New Dictionary

Z.Add key:="Norway", Item:="AAA"
Z.Add key:="Germany", Item:="AA"
Z.Add key:="USA", Item:="A"
Z.Add key:="France", Item:="AC"
Z.Add key:="Spain", Item:="B"
Z.Add key:="Greece", Item:="BC"

Dim numItems As Integer
numItems = Z.Count

MsgBox "The collection has " & numItems & " items."
End Sub

When I try to create a Function that sends back a value for a given key (a rating for a given country), it displays a #Value! error on my Excel sheet:

Function GetValueFromDict(Z As Object, key As String) As Variant
Dim dict As New Scripting.Dictionary
Set dict = Z
If dict.Exists(key) Then
    GetValueFromDict = dict.Item(key)
Else
    GetValueFromDict = Null
End If
 
End Function

Answer 1

Use a Dictionary UDF

In Excel

• Use e.g. the formula

  =GetCountryRating("Germany")
  

• If you want the function to return an empty string when the country is not found, declare its result As String (instead of As Variant) and remove the Else clause.

Option Explicit

Private CountryDict As Scripting.Dictionary

Sub PopulateCountryDict()
    
    Dim Z As New Scripting.Dictionary
    Z.CompareMode = TextCompare
    
    Z.Add Key:="Norway", Item:="AAA"
    Z.Add Key:="Germany", Item:="AA"
    Z.Add Key:="USA", Item:="A"
    Z.Add Key:="France", Item:="AC"
    Z.Add Key:="Spain", Item:="B"
    Z.Add Key:="Greece", Item:="BC"
    
    Set CountryDict = Z
    
End Sub

Function GetCountryRating(ByVal Country As String) As Variant
    If CountryDict Is Nothing Then PopulateCountryDict
    If CountryDict.Exists(Country) Then
        GetCountryRating = CountryDict(Country)
    Else
        GetCountryRating = Null
    End If
End Function

In VBA

Sub UseDictionary()
    
    Dim Z As New Scripting.Dictionary
    Z.CompareMode = TextCompare
    
    Z.Add Key:="Norway", Item:="AAA"
    Z.Add Key:="Germany", Item:="AA"
    Z.Add Key:="USA", Item:="A"
    Z.Add Key:="France", Item:="AC"
    Z.Add Key:="Spain", Item:="B"
    Z.Add Key:="Greece", Item:="BC"
    
    Dim numItems As Long: numItems = Z.Count
    
    MsgBox "The dictionary has " & numItems & " items.", vbInformation
 
    Dim Country As String: Country = "Germany"
    Dim Rating As Variant: Rating = GetCountryRating(Z, Country)
    
    If IsNull(Rating) Then
        MsgBox "No rating found for " & Country & ".", vbExclamation
    Else
        MsgBox "The rating for " & Country & " is """ & Rating & """.", _
            vbInformation
    End If

End Sub

Function GetCountryRating( _
    ByVal dict As Scripting.Dictionary, _
    ByVal Country As String) _
As Variant
    If dict.Exists(Country) Then
        GetCountryRating = dict(Country) ' short for 'dict.Item(Country)'
    Else
        GetCountryRating = Null
    End If
End Function