vba - Wildcard search of dictionary

Question

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?

Answer 1

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