Partial exists in dictionary

I m looking for a way to check if a value included in a dictionary, Excel - VBA. However, simple dict.Exists("") is not the right approach in this situation because the searching value with not be exactly the same with the value in the dictionary.

For example I m look for the word apple in the dictionary which includeds the phrase fruit apple. So apple included in the dictionary as fruit apple. Case sensitivity much be avoided.

Currently, I looping the dictionary which is time consuming.

Is ther any additions like dict.Exists("*apple*")

Any ideas?

Solution

Sounds like a perfect job for the (relatively unknown) Filter() function.

Sub Test()

Dim Dict As Object: Set Dict = CreateObject("Scripting.Dictionary")

Dict.Add "Apples", "Found You!"
Dict.Add "Banana", 2
Dict.Add "Cherry", 3
Dict.Add "Date", 4
Dict.Add "Elderberry", 5

r_out = Filter(Dict.Keys, "apple", 1, 1)
If UBound(r_out) > -1 Then
    'Do something with r_out, which is an array of all matched keys
    'For example, traverse filtered keys and return values:
    For i = 0 To UBound(r_out)
        Debug.Print Dict(r_out(i))
    Next
Else
    'Something if no matches are found
    Debug.Print "Bummer"
End If

End Sub

4 Parameters:

One-dimensional array of strings which Dict.Keys can provide;
A string to search for (case insensitive so don't worry here);
A boolean (or equivalent, 1) to tell the function we want to return values that include the string;
An integer 1 (vbTextCompare) to tell the function we want to compare text.

I'm unsure what you want to do next...