Mike,
After much trial and error, I've determined the cause of the error you have reported. As it turns out, it has nothing to do per se with the extraneous characters added to the TextBox. Rather, the CheckSpelling method of the Application object takes a string parameter having a maximum length of 255 characters. If the string exceeds this limit, a Type Mismatch error is generated (for reasons only Microsoft understands). The VBA Help does indicate this method checks a single word, but no length limit is given. In fact, it will check a multi-word string, within the 255-character maximum.
A first attempt at correcting for this limit was the following, which uses the Split function to parse the text string into individual words, which are then fed to the CheckSpelling method:
Code:
Function IsSpellingCorrect(ByVal sInput) As Boolean
Dim i As Long
Dim vWords As Variant
IsSpellingCorrect = True
vWords = Split(strInput, Chr(32), -1, vbBinaryCompare)
For i = LBound(vWords) To UBound(vWords)
If Not Application.CheckSpelling(vWords(i)) Then
IsSpellingCorrect = False
Exit For
End If
Next i
End Function
While this function works, it is too slow. The CheckSpelling method is itself the bottleneck. Operating on a ~500 character string produces a noticeable delay. A revamped version of the function divides the original string into substrings of <= 255 characters, resulting in far fewer calls to the CheckSpelling method.
Code:
Function IsSpellingCorrect(ByVal sInput As String) As Boolean
Dim i As Long
Dim lLength As Long
Dim lWordLen As Long
Dim sTmp As String
Dim bSpellingOK As Boolean
Dim vWords As Variant
Dim sArr() As String
Dim iCount As Integer
If Len(sInput) < 256 Then
IsSpellingCorrect = Application.CheckSpelling(sInput)
Exit Function
End If
vWords = Split(sInput, Chr(32), -1, vbBinaryCompare)
sTmp = ""
lLength = 0
iCount = 0
For i = LBound(vWords) To UBound(vWords)
lWordLen = Len(vWords(i))
If lLength + lWordLen < 256 Then
sTmp = sTmp & vWords(i) & " "
Else
iCount = iCount + 1
ReDim Preserve sArr(1 To iCount)
sArr(iCount) = sTmp
sTmp = ""
End If
lLength = Len(sTmp)
Next i
If lLength > 0 Then
iCount = iCount + 1
ReDim Preserve sArr(1 To iCount)
sArr(iCount) = sTmp
End If
For i = 1 To iCount
bSpellingOK = Application.CheckSpelling(sArr(i))
If Not bSpellingOK Then Exit For
Next i
IsSpellingCorrect = bSpellingOK
End Function
The second function is approximately 13 times faster (tested on a ~700 character text string with no spelling errors); probably good enough unless your users are entering a novel into the TextBox. [Note: The ReDim call is also relatively time consuming. Some additional speed gains could be made by estimating the number of substrings then ReDim'ing once]
Recommend adding the second function to your code then trying it out on one TextBox. If this works OK for you, I'll send some examples of streamlined code that incorporates this function. So, don't necessarily add this function call throughout your code just yet. Here is example code using a snippet of your check spelling CommandButton event handler for the txtXtra TextBox, with the new function call highlighted:
Code:
Private Sub cmdSpellXtra_Click()
[highlight]If IsSpellingCorrect(txtXtra.Text) Then[/highlight]
cmdSpellXtra.BackColor = &HFF00&
Else
'NOT OK - Copy text data to hidden XLA SpellCheck worksheet
cmdSpellXtra.BackColor = &HFF&
strSpellCheckXtra = txtXtra.Text
ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Value = strSpellCheckXtra
'Now Check spelling
ThisWorkbook.Worksheets("SpellCheck").Cells(1,1).CheckSpelling AlwaysSuggest:=True
txtXtra.Text = ThisWorkbook.Worksheets("SpellCheck").Cells(1, 1).Text
cmdSpellXtra.BackColor = &HFF00&
End If
End Sub
Regards,
Mike
