Excel VBA UserForm - SpellChecking - Text Frames 2

[hafod]

Hi,
The following code is called up on the click event of a 'spellcheck' button and relates to one of three text frames on a Data Entry/Validation User Form. Problem is, it will correctly identify a spelling error but is incapable of highlighting actual spelling error(s) in the frame in focus, or bring up standard Windows spell check dialog for subsequent correction. Can this be done programatically? The User Form (modal)is designed to prevents users from directly accessing raw Excel (database) data / and other features.

Hopy you can help

Hafod

Private Sub cmdSpellXtra_Click()
'Check HoY Spelling
frmDataEntryFormTutor.txtXtra.SetFocus
If (Application.CheckSpelling(txtHoY.Text) = True) Then
MsgBox ("spelling OK")
Else
MsgBox ("spelling error")
Columns("J:J").Select

'Selection.CheckSpelling SpellLang:=2057
End If
End Sub

 

[hafod]

Mike,
Many thanks for your useful and helpful post. If you wish I can send/e-mail you the complete add-in with GUI' s (not for your review of course!) together with source spreadsheet. They are not unduley complex. This may help you contexualise the app. and problem and be of help to you.
Regards,
Mike (Hafod)

 

[rmikesmith]

Mike,

Yes, I'll take a look. However, Tek-Tips frowns on the posting of email addresses because it attracts spammers. Here's a hint though: I have a hotmail account and my username is my forum name.

Regards,
Mike

 

[hafod]

Mike,
Thanks for that. Having technical e-mail difficulties at work at present. May have to tx application to you this evening at home.
Best Regards,
Mike

 

[rmikesmith]

Mike,

I took a look at the files you sent. In a nutshell:

[li]Loaded your Add-In and associated files[/li]
[li]Selected Forms Tutor from the SIMs Interfaces menu[/li]
[li]Copied/Pasted the nonsense string to the txtExtra TextBox[/li]
[li]Clicked the spell check button[/li]

Result: No Type Mismatch error; no errors whatsoever. In fact, it worked as expected (spell check dialog displays, etc...).

I repeated this with the other TextBoxes. All OK.

This makes me think there is something in the way you are adding that extraneous text that is causing the problem. Can you relay, in detail, what you did that resulted in the error being generated? Is the error reproducible? Also, what happens if you just type in the random characters at the end of the text?

After this is cleared up, I'll send some recommendations for streamlining your code.

p.s. This duplicates an email message I sent; so disregard.

Regards,
Mike

 

[hafod]

Hi Mike,
Thanks for e-mail and posting. Sorry for delay in response - been away with family for this glorious weekend! Now back to reality I suppose.

The transmitted file (email) contains a text string, which, if appended to the 'Xtra curricular text' using the Form Tutor GUI and its associated data 'FormTutoro3' does produce a consistent 'Type Mismatch' error with me.

As can be inferred from the string it was simply a straight Edit/Copy of the first record data for this field with random ioio... etc characters added. Occasionally the addition of mere random chars eg ioioio ioi ioi.. etc alone will produce the error, but ocassionally with a second 'random tranche' of meaningless characters, usually i, o and spacebar.

Hope this helps Mike and thanks for kind offer to comment on my code.
Hope yove had an equally fine (and blistering) weekend.
Regards,
Mike

 

[rmikesmith]

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:

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.

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:

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

 

[hafod]

Hi Mike,
Many thanks for your very thorough evaluation of the text management problem and associated symptoms to which you appear to have a viable and tested solution. In retrospect, I shold have studied VBA help on function more fully. Having said that, I dont think I could have developed a remedy to overcome it to the extent of yours.

In the context of my application, the overspill relative to 255 is unlikely to be significant (most teacher comments are marginally above 255 in the extreme), but does fall within the limitation of the native 'checkspelling' function and therefore requires address if the Add-In is to be released. I will include your function in the application tomorrow (Wed) in work - (non teaching time permitted!) and post the outcome later.
Again many thanks Mike for your help,
Mike H.

 

[hafod]

Hi MIke
I have included your preferred function and function call in the User Form GUI code and works reliably and without any perceptible delay. The text entry will be limited anyway to c350 characters. I am not quite sure what you mean by 'streamlined' code because your code works well and looks very efficient? HAving seen GUI do you feel that I should make any other pertinent changes to enhance functionality? I would welcome your comments.

Again, many thanks for your effort and continuing contribution to overconming problems with this VBA project.
Mike

 

[rmikesmith]

Mike,

Glad to hear it's working for you. My reference to streamlined code was in connection with some of your existing code. I'll make some recommendations with code samples and send them to you via email, as they are outside the scope of this thread.


Best Regards,
Mike