CODEWITHSUNDEEP
vbaexcel
Karthik Mohan
Karthik Mohan

Reputation: 63

Wildcard dictionary search in VBA

Is it possible to search for a particular pattern of words (i.e. a pattern containing wildcards) against the spell check dictionary built in Excel? 

Debug.Print Application.CheckSpelling("hel*")

For example, I want the above code to return True if I am searching for "hel*", because some actual words do match this pattern, e.g. "hello", "hell" etc.

However, this does not work as intended: it returns False.

Upvotes: 3

Views: 938

Answers (1)

Jean-François Corbett
Jean-François Corbett

Reputation: 38500

Just had a little fun writing this one... It works with single-character wildcards, i.e. ?. It doesn't work with multi-character wildcards (*), but I still thought this was an interesting path forward.

Warning: This uses recursion, and execution time increases exponentially with the number of ? in the input string!

Function CheckSpellingWithWildcards(ByVal s As String)
    Const wildcardChar As String = "?"
    Dim i As Integer
    Dim firstWildcardPos As Long
    firstWildcardPos = InStr(s, wildcardChar) 'Find first wildcard
    If firstWildcardPos = 0 Then
        'No wildcards left — look it up in the dictionary.
        CheckSpellingWithWildcards = Application.CheckSpelling(s)
    Else
        CheckSpellingWithWildcards = False
        For i = 97 To 122 'a to z. Adjust if you need e.g. çæøåéëï as well
            Mid(s, firstWildcardPos, 1) = Chr(i)  'Replace wildcard with letter
            If CheckSpellingWithWildcards(s) Then 'note: recursion!
                'Found a match! Get out.
                CheckSpellingWithWildcards = True
                Exit Function
            End If
        Next i
    End If
End Function

Example usage:

?CheckSpellingWithWildcards("Hel?")
True
?CheckSpellingWithWildcards("Hel?o")
True
?CheckSpellingWithWildcards("Hel?p")
False
?CheckSpellingWithWildcards("Comm?nica?ion")
True
?CheckSpellingWithWildcards("C?mm?nyca?ion") '30 seconds later...
False

Upvotes: 3

Related Questions