Finding the key corresponding to an item in a dictionary

Question

Is there any way to find the key that corresponds to a given item in a VBA dictionary?

http://msdn.microsoft.com/en-us/library/aa164502%28v=office.10%29.aspx MSDN suggests that the "Key" property can be used, but when I try using it I get an error ("Compile error: invalid use of property"). I've found in the past that the "Exists" method given here doesn't work for me either, so I assume that they were the commands in a previous version of Office and are now outdated. However I haven't been able to find an equivalent for the latest version of Office.

I could use a for each loop to create a new dictionary where the keys in the old dictionary are the items in the new dictionary (and vice versa) and then use ".Item", but I was wondering if there was an inbuilt command that would allow me to avoid this.

Solution 1

An alternate solution(..?)

Instead of going through each item in the dictionary for a match, how about you maintain 2 dictionary objects? The second one, using value as the key and key as its value. When u add an item, u add it both the dictionaries. If you have a key, you look it up in the first dictionary and if you have the value, u look it up in the second one.

Solution 2

but I was wondering if there was an inbuilt command that would allow me to avoid this.

Nope there is no inbuilt command as such. You will have to resort to some kind of looping. Here is one example. I created a small function to get the key corresponding to an item in a dictionary.

Dim Dict As Dictionary

Sub Sample()
    Set Dict = New Dictionary

    With Dict
      .CompareMode = vbBinaryCompare
      For i = 1 To 10
        .Add i, "Item " & i
      Next i
    End With

    Debug.Print GetKey(Dict, "Item 3")
End Sub

Function GetKey(Dic As Dictionary, strItem As String) As String
    Dim key As Variant
    For Each key In Dic.Keys
        If Dic.Item(key) = strItem Then
            GetKey = CStr(key)
            Exit Function
        End If
    Next
End Function