Spell Checking TextBox on UserForm

don22 · Feb 18th 2021

Searching I found code to spell check a text box on a userform. I created the following function.

Code

Public Function SpellCheck(ByRef SC_TextBox As MSForms.TextBox) As Boolean

    Dim varWords As Variant
    Dim ndx As Long
    Dim lngLen As Long
    
    varWords = Split(SDebug.Print Timer
    For ndx = LBound(varWords) To UBound(varWords)
        If Not Application.CheckSpelling(Word:=varWords(ndx), IgnoreUppercase:=True) Then
            With SC_TextBox
                .SelStart = lngLen
                .SelLength = Len(varWords(ndx))
                .SetFocus
            End With
            SpellCheck = False
            Exit Function
        Else
            lngLen = lngLen + Len(varWords(ndx)) + 1
        End If
    Next ndx
    
    SpellCheck = True
    
End Function

Display More

It works fine except that it is very slow. With everything spelled correctly a text box with 34 words takes 27 seconds to run. One with 11 words takes 10 seconds. It is the Application.CheckSpelling that is taking a long time. Anyone have any suggestions on speeding this up?

Thanks

Don

**Carim** · Feb 18th 2021

Hello,

With the information you have provided ... you should double-check your Variant varWords ...

Hope this will help

don22 · Feb 18th 2021

Typo in the code section in the original post. Corrected.

Code

Public Function SpellCheck(ByRef SC_TextBox As MSForms.TextBox) As Boolean

 Dim varWords As Variant
 Dim ndx As Long
 Dim lngLen As Long
 
 varWords = Split(SC_TextBox.Text, " ")

 For ndx = LBound(varWords) To UBound(varWords)
 If Not Application.CheckSpelling(Word:=varWords(ndx), IgnoreUppercase:=True) Then
 With SC_TextBox
 .SelStart = lngLen
 .SelLength = Len(varWords(ndx))
 .SetFocus
 End With
 SpellCheck = False
 Exit Function
 Else
 lngLen = lngLen + Len(varWords(ndx)) + 1
 End If
 Next ndx
 
 SpellCheck = True
 
End Function

Display More

**royUK** · Feb 18th 2021

Try this

don22 · Feb 18th 2021

Roy, this is the same code as mine. Same results.

**royUK** · Feb 19th 2021

My code checked 150 words in 0.158682 seconds

don22 · Feb 19th 2021

I running this at work on a 12 year old PC running Windows 7 and a not up to date office version. I’ll have to try it at home which has current hardware and software to see what the timings are.

Thanks for the assistance.

**royUK** · Feb 19th 2021

The old PC with Windows 7 is more than likely causing it to run slowly

Spell Checking TextBox on UserForm

Participate now!

Share

Tags