vba 查找字典中某项对应的键

Finding the key corresponding to an item in a dictionary

提问by Bridget

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

有没有办法在 VBA 字典中找到与给定项目对应的键？

http://msdn.microsoft.com/en-us/library/aa164502%28v=office.10%29.aspxMSDN 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.

http://msdn.microsoft.com/en-us/library/aa164502%28v=office.10%29.aspxMSDN 建议可以使用“Key”属性，但是当我尝试使用它时出现错误（ “编译错误：无效使用属性”）。我过去发现这里给出的“Exists”方法对我也不起作用，所以我假设它们是以前版本的 Office 中的命令，现在已经过时了。但是，我一直无法找到最新版本的 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.

我可以使用 for each 循环来创建一个新字典，其中旧字典中的键是新字典中的项（反之亦然），然后使用“.Item”，但我想知道是否有一个内置命令会让我避免这种情况。

采纳答案by Romeo

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.

与其通过字典中的每个项目进行匹配，不如维护 2 个字典对象？第二个，使用值作为键，键作为其值。当你添加一个项目时，你将它添加到字典中。如果你有一个键，你在第一个字典中查找，如果你有值，你在第二个字典中查找。

回答by Siddharth Rout

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

回答by Tre Jones

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

实际上，有一个 Exists 方法可以完全满足您的要求。这是它的工作原理：

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

The above returns True.

以上返回True。

回答by Rolf

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

由此，我首先认为 .exists(key) 是无用的。 但是有一个简单的规避方法。

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

1. 确保第一次引用要检查的值时，将 .exists(key) 分配给布尔变量。
2. 如果该布尔值的值为 FALSE，请立即删除
   测试密钥时无意中创建的字典条目

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 willwork (or at least, for me it does... so far)

您可能会收到 424 错误 - .exists 的实现者真的失败了。但以下将起作用（或者至少，对我来说它确实......到目前为止）

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