10 thoughts on “Checking Grammar”

1. “Unfortunately, checking the grammar via VBA doesn’t actually tell you what the error is. What it does tell you is that a particular sentence has a grammatical error.”

   Last year I automated Word’s spell checker within Excel so a client could check spelling in text entered into userform textboxes. It worked just as if the check was occurring in Word, with the familiar dialog highlighting the error and suggesting alternatives. With the appropriate code slapped on, I bet the grammar checker could work the same way.

   – Jon

2. Jon – how did you do that? I saw that word provides a method that returns an array of suggested spellings, but nothing similar for grammar. It looks like CheckSpelling needs a document or range object. Did you roll your own using GetSpellingSuggestion method or was it some other way?

3. Dick –

   It’s slowly coming back to me, at least the general approach. What I did was open a new Word instance, with the main window moved off screen. I copied the textbox contents into the empty document window and ran the spell checker on it. If it was spelled correctly, no problem. If there were any errors, the suggestion box in Word would appear (that’s why Word had to be the active application, and I moved it off screen to keep the Excel form visible). When the user finished, I recopied the text back to the textbox. I didn’t have to interact any more than that, except to move the Word application window back before quitting.

   That was many hours of frustration, and I think I could only invoice a fraction of it. I’m waiting for more apps to depreciate the rest of my effort.

   – Jon

4. That’s freakin’ brilliant. I can’t wait to try it.

5. Hi Guys,

   Here is some code to do spell checking of text within a textbox on a userform using Excels spell checker. It uses column A on worksheet Sheet2 as a temporary storage area.
   Not as pretty as the word version but it appears to work.

   Create a userform, add a textbox and a commandbutton to it.
   Paste the following code into the code module of the userform.

   Option Explicit

   Function SpellCheck(Text As String) As String
   ‘
   ‘ Spell check text
   ‘
   Dim rngSpell As Range
   Dim rngTemp As Range
   Dim lngRow As Long
   Dim lngPos As Long
   Dim lngStart As Long

   ‘ break textbox text in to lumps that do not
   ‘ cause the spell checker to not apply changes
   ‘
   Set rngSpell = Range(“sheet2!A1?)
   Do While Len(Text) > 255
   lngPos = 255
   Do While lngPos > 0
   If Mid(Text, lngPos, 1) = ” ” Then
   Exit Do
   End If
   lngPos = lngPos – 1
   Loop
   If lngPos = 0 Then lngPos = 255
   rngSpell.Offset(lngRow, 0) = Left(Text, lngPos)
   lngRow = lngRow + 1
   Text = Mid(Text, lngPos + 1)
   Loop
   rngSpell.Offset(lngRow, 0) = Text
   lngRow = lngRow + 1
   Set rngSpell = Range(“Sheet2!A1:A” & lngRow)

   If Not rngSpell.CheckSpelling(CustomDictionary:=”CUSTOM.DIC”, IgnoreUppercase:=False _
   , AlwaysSuggest:=True, SpellLang:=2057) Then
   SpellCheck = Text
   rngSpell.Clear
   Exit Function
   Else
   ‘ rebuid spell checked text
   For Each rngTemp In rngSpell
   SpellCheck = SpellCheck & rngTemp.Value
   Next
   End If
   rngSpell.Clear
   Exit Function

   End Function
   Private Sub CommandButton1_Click()
   TextBox1.Text = SpellCheck(TextBox1.Text)
   End Sub
   Private Sub UserForm_Initialize()
   CommandButton1.Caption = “Spell Check”
   TextBox1.WordWrap = True
   TextBox1.EnterKeyBehavior = True
   End Sub

6. Unfortunately, when the spellchecker dialog comes up, your form loses focus. When checking spelling against a worksheet, this means your workbook gains focus and it never seems to give focus back to the form.

7. This seems to be corrected in 2010.

   I used:

   Private Sub tbComments_Exit(ByVal Cancel As MSForms.ReturnBoolean)
       With Range(“SpellingCheck”)
           .Value = tbComments.Text
           .CheckSpelling IgnoreUppercase:=False, AlwaysSuggest:=True, SpellLang:=2057
           tbComments.Text = .Value
       End With
          
   End Sub

   Where “SpellingCheck” is a Named Formula refering to a cell I’ve reserved for the purpose on my behind the scense “Options” worksheet.

   I’ve tested this and the 255 limit seems to have been fixed, I reached a few thousand and gave up as this was (more than twice what I beleive I need.

   If anyone has a simple to implement update on the grammar issue though.

   Mawdo

8. For the benefit of anyone googling this:

   My form above is Modal, Nevyn Hira may have had an issue with a non modal form, but you should be able to set the focus back at the end of the sub.

   Mawdo

9. Hi guys….i am trying to develope a vb code which could help me identify if the sentence written in a particular cell is gramatically correct or not….it should check everything like article, preposition, nouns etc….pls help

10. Hi,

    Is there a development on checking sentences for grammar errors in the cells of an Excel spreadsheet?

    Thanks/ Rameshwar

Posting code? Use <pre> tags for VBA and <code> tags for inline.