Finding wildcard dictionaries in VBA
Is it possible to search for a specific pattern of words (such as a pattern that contains wildcards) for the spell check dictionary built into Excel?
Debug.Print Application.CheckSpelling("hel*")
For example, I want the above code to return True
if I search "hel*"
because some of the actual words match this pattern, eg. "hello", "hell", etc.
However, this doesn't work as expected: it returns False
.
source to share
It was just a little fun to write this one ... It works with single character wildcards, i.e. ?
... It doesn't work with multi-character wildcards ( *
), but I still thought it was an interesting way forward.
Warning. This uses recursion, and the execution time increases exponentially with the number ?
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
Usage example:
?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
source to share