0

According to this experiment, the fastest VBA method to find a value in a collection of data is to put Data in the form of dictionary. The gained speed in finding a value is 100x. The Dictionary in VBA has a predefined method named: ".exist(key)" to find value by key. But there is no predefined method to find key by value. While building dictionary, As you know, no repeated keys are allowed. Surely it would be possible to use "for" loop, but surely the gained speed would be lost and again it would not be efficient for large number of data. So is it any other solution to find a value in large number of items as fast as dictionary? Thanks

amir.khosravi
  • 23
  • 5
  • 1
    the question and title seem unrelated or at least confusing. Also, *surely the gained speed would be lost* .... have you actually tested this? And is any difference significant enough to matter in an application using Excel VBA? – Scott Holtzman Jul 29 '20 at 13:25
  • 1
    You could keep a dictionary with the key as the value and the value as a delimited string of keys. – Warcupine Jul 29 '20 at 13:26
  • Scott Holtzman, Please Suggest A Way To Put Key-Value Pairs In The Form Of Dictionary in VBA While There Are Similar Values. – amir.khosravi Jul 29 '20 at 13:39
  • Warcupine , Could you please describe it more clearly. I did not get your point. There are similar Values in the key-value pairs and I want to find key by value. – amir.khosravi Jul 29 '20 at 13:42
  • Do you need both the key-value pair? Why not store the values as keys and leave the value part empty? – Kostas K. Jul 29 '20 at 15:18

1 Answers1

2

You can have two dictionaries, one with the normal structure of Keys -- Values. And another with a flipped structure where your normal keys are now a delimited string ("some|thing|like|this") and the normal values are the keys.

An example would be like this:

Sub dictionaries()
    Dim dictnormal As Object
    Dim dictreversed As Object
    
    Set dictnormal = CreateObject("Scripting.Dictionary")
    Set dictreversed = CreateObject("Scripting.Dictionary")
    
    Dim keyarr As Variant
    
    keyarr = Array("Red", "Blue", "Green", "Yellow", "Brown")
    Dim k As Variant
    
    For Each k In keyarr
        dictnormal.Add k, 1
        If Not dictreversed.exists(dictnormal(k)) Then
            dictreversed.Add dictnormal(k), k
        Else
            dictreversed(dictnormal(k)) = dictreversed(dictnormal(k)) & "|" & k
        End If
    Next k
    
    For Each k In dictnormal
        Debug.Print "Normal Keys: " & k, "Normal Values and Reversed Keys: " & dictnormal(k), "Reversed Values: " & dictreversed(dictnormal(k))
    Next k
End Sub

I only have one value in the normal dictionary but it should show the idea pretty clearly.

Warcupine
  • 4,460
  • 3
  • 15
  • 24