CODEWITHSUNDEEP
vbadictionaryexcel
Bridget
Bridget

Reputation: 175

Finding the key corresponding to an item in a dictionary

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.

Upvotes: 4

Views: 38488

Answers (4)

Rolf
Rolf

Reputation: 1

From this, I first thought that .exists(key) is enterely useless. But there IS an easy circumvention.

First, let me mention a futile attempt:

  1. make sure the first time you refer to the value to check, you assign .exists(key) to a boolean variable.
  2. if the value of that boolean is FALSE, immediately remove the dictionary
    entry that was inadvertently created when you tested the key

Well, that works, but the next time you test for existence again with this code

itExists = a.exists(key)

you may get a 424 error -- the implementor of .exists REALLY failed. But the following will work (or at least, for me it does... so far)

if isempty(a.item(key)) then  ' checking on value of the object
  a.remove(key)
  a.add key, value
else ' you have a key duplicate
  ' do something about dupe, like quit
end if

For a little clarification, you can look at the following example code below

Sub aDict()
Dim a As Dictionary
Dim x As Long
Set a = New Dictionary
With a
  On Error Resume Next
' first add
  .Add 1, "sumpn"
' second add
  .Add "dog", "beagle"
  x = 66
' third add
  .Add "sixty", x
  printd a, 1, "added with numerical key"
  printd a, 2, "added with string key = dog, using numeric key=2"
  Stop    ' look at count of items: we added 3, but have 4 in local vars
  printd a, "2", "searching string key '2', not retrieving 2nd added"
  printd a, 9, "should not exist, first try"
     ' but the .exists has created it!!
  printd a, 9, "should not exist, second try, *** but now created ***"
  printd a, 8, "never seen anywhere"
Stop    ' look at a in local vars!! #8 exists now as item 7
  a.Remove 8  ' so we kill it
' *************************** the great fixit *******  
Stop    ' observe that #8 (item 7) is gone again
  printd a, "dog", "added as second position (Item 2)"
' fourth add
  .Add 1, "else" ' doublette
  printd a, 1, "position 1 is taken, Err=457 is correct"
' fifth add
  .Add 3, "beagle"
  printd a, "3", "string key='3' <> numeric 3"
' 6th add
  .Add 5, "beagle"
  printd a, "beagle", "value is already there with key 'dog'"
  printd a, 5, "numeric key=5"
End With
End Sub

Sub printd(a As Dictionary, mkey, Optional msg As String)
Dim ex As Boolean
With a
  If Err.number <> 0 Then
      Debug.Print mkey, "error " & Err.number, Err.Description
  End If
Err.clear
  ex = .Exists(mkey) ' very first reference to a.Exists(mkey)
  Debug.Print "key " & mkey, "a(" & mkey & ")" & a(mkey), _
              "Exists", ex, "a.item " & .Item(mkey), msg
  If Err.number <> 0 Then
    Debug.Print mkey, "error " & Err.number, Err.Description
  End If
End With
End Sub

Upvotes: 0

Tre Jones
Tre Jones

Reputation: 61

Actually, there is an Exists method that will do exactly what you want. Here's how it works:

...
Dim dict
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "utensil", "spork"
Debug.Print dict.Exists("utensil")

The above returns True.

Upvotes: 0

Romeo
Romeo

Reputation: 1093

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.

Upvotes: 4

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 12

Related Questions