Checking Grammar

Unlike Word, Excel doesn’t have the facility to check for grammar errors. Fortunately, you can automate Word and use its grammar checker to do the job. 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. It’s better than nothing, I guess.

The GrammaticalErrors property returns a collection of Word.Ranges, each representing a sentence with an error in it. Here’s an example that checks the grammar in an Excel range:

With this range

Checkgrammar1

you get this output

Checkgrammar2

Thanks to Robin for the suggestion.

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. 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

  5. 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.

  6. 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

  7. 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

  8. 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

  9. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *