CODEWITHSUNDEEP
vbaexcelexcel-2010
Salvatore Fanale
Salvatore Fanale

Reputation: 113

Wildcard search of dictionary

After searching google and SO, I see that there is a way for me to search a dictionary for an existing key:

dict.exists("search string")

My question is how can I search a dictionary using a wildcard:

dict.exists("search*")

I want to search the dictionary for a term first because my macro has the user select a group of files (the file name as dictionary key and the full path as the value) and I want to determine if files of a certain naming convention are present in the group BEFORE I iterate the dictionary elements to apply the file processing.

If a certain naming convention is found, X processing is run on each file in the dictionary instead of Y processing. The trick is that if ANY of the elements follow the certain naming convention, then they all need to be processed accordingly. That is to say, if elements 1-19 fail to meet the convention but 20 passes, then all elements 1-20 need specific processing. This is the reason I cant just check each name as I go and process selectively one file at a time.

My current solution is to iterate the entire dictionary once searching for the naming convention, then reiterating the dictionary after I know which method to use in processing the files. I am looping through all the elements twice and that doesn't seem efficient...

Do you guys have a reasonable solution for wildcard searching the dictionary keys?

Upvotes: 4

Views: 6404

Answers (4)

Luis Alfonzo Gonzalez
Luis Alfonzo Gonzalez

Reputation: 1

If you want to use a wildcard to search in dictionary keys you can use the method [yourdictionary].Keys and the function Application.Match

For example: Dim position As Variant 'It will return the position for the first occurrence

position = Application.Match("*Gonzalez", phoneBook.Keys, 0)

If phoneBook has Keys: (JuanCarlos, LuisGonzalez, PedroGonzalez)

It will return the position for LuisGonzalez

Upvotes: 0

user6432984
user6432984

Reputation:

You can use Filter combined with the array of dictionary keys to return an array of matching keys.

Function getMatchingKeys(DataDictionary As Dictionary, MatchString As String, Optional Include As Boolean = True, Optional Compare As VbCompareMethod = vbTextCompare) As String()

    getMatchingKeys = Filter(DataDictionary.Keys, MatchString, Include, Compare)

End Function

Here are some examples of what can be done when you apply a filter to the dictionary's keys.

Option Explicit

Sub Examples()
    Dim dict As Dictionary
    Dim arrKeys() As String
    Dim key

    Set dict = New Dictionary

    dict.Add "Red Delicious apples", 10
    dict.Add "Golden Delicious Apples", 5
    dict.Add "Granny Smith apples", 66
    dict.Add "Gala Apples", 20
    dict.Add "McIntosh Apples", 30
    dict.Add "Apple Pie", 40
    dict.Add "Apple Sauce", 50

    dict.Add "Anjuo Pears", 60
    dict.Add "Asian Pears", 22
    dict.Add "Bartlett Pears", 33
    dict.Add "Bosc Pears", 44
    dict.Add "Comice Pears", 3

    arrKeys = getMatchingKeys(dict, "Apple")
    Debug.Print "Keys that contain Apple"
    Debug.Print Join(arrKeys, ",")
    Debug.Print

    arrKeys = getMatchingKeys(dict, "Apple", False)
    Debug.Print "Keys that do not contain Apple"
    Debug.Print Join(arrKeys, ",")
    Debug.Print

    arrKeys = getMatchingKeys(DataDictionary:=dict, MatchString:="Apple", Include:=True, Compare:=vbBinaryCompare)
    Debug.Print "Keys that contain matching case Apple"
    Debug.Print Join(arrKeys, ",")
    Debug.Print

    arrKeys = getMatchingKeys(DataDictionary:=dict, MatchString:="Pears", Include:=True, Compare:=vbTextCompare)
    Debug.Print "We can also use the array of keys to find the values in the dictionary"
    Debug.Print "We have " & (UBound(arrKeys) + 1) & " types of Pears"
    For Each key In arrKeys
        Debug.Print "There are " & dict(key) & " " & key
    Next

End Sub

Output:

enter image description here

Upvotes: 3

Dick Kusleika
Dick Kusleika

Reputation: 33145

The Dictionary Items method returns an array of all the items. You can Join those into a big string then use Instr() to determine if your search string is in the big string.

From your example, you have the asterisk at the end, so I'm assuming you care how an item starts, not that a sub-string exists anywhere. So I look for delimiter+substring and add the delimiter to the front of the Join (for the sake of the first item). If you have different requirements, you'll have to adjust, but the theory is the same.

I used two pipes as a delimiter because it's unlikely to be in the data and return a false positive. That may not be appropriate for your data.

Public Function WildExists(ByRef dc As Scripting.Dictionary, ByVal sSearch As String) As Boolean        
    Const sDELIM As String = "||"        
    WildExists = InStr(1, sDELIM & Join(dc.Keys, sDELIM), sDELIM & sSearch) > 0        
End Function

test code

Sub Test()

    Dim dc As Scripting.Dictionary            
    Set dc = New Scripting.Dictionary

    dc.Add "Apple", "Apple"
    dc.Add "Banana", "Banana"
    dc.Add "Pear", "Pear"

    Debug.Print WildExists(dc, "App") 'true
    Debug.Print WildExists(dc, "Ora") 'false

End Sub

Upvotes: 4

Vasily
Vasily

Reputation: 5782

this method can help you with wildcard searching in Dictionary

Sub test()
Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
Dim KeY, i&: i = 1
For Each oCell In Range("A1:A10")
    Dic.Add i, Cells(i, 1).Value: i = i + 1
Next
For Each KeY In Dic
    If LCase(Dic(KeY)) Like LCase("Search*") Then
        MsgBox "Wildcard exist!"
        Exit For
    End If
Next
End Sub

Upvotes: 1

Related Questions