VBA get key by value in Collection

Question

Possible Duplicate:
VBA collection: list of keys

Maybe it is a very simple question, but I am new in VBA. I have some data that I want use as Collection(key,value). How can I get key by the value?

Answer 1

I don't think you can do this with the Collection object.

You can, however, use the alternative Dictionary, which you need to include in your Excel project from the VBA editor: click the "Tools" menus, select "References" and locate "Microsoft Scripting Runtime", after which you should be able to do something like this:

Public Sub Test()

Dim dict As New dictionary
    dict.Add "a", 1     ' "Add" parameters are reversed compared to Collection
    dict.Add "b", 2
    dict.Add "c", 3

    If KeyFromvalue(dict, 2) = "b" Then
        Debug.Print "Success!"
    Else
        Debug.Print "fail..."
    End If

End Sub

Public Function KeyFromvalue(dict As dictionary, ByVal target)

Dim idx As Long

    For idx = 0 To dict.Count - 1
        If dict.Items(idx) = target Then
            KeyFromvalue = dict.Keys(idx)
            Exit Function
        End If
    Next

End Function