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:
Sub GrammarCheck(xlRange As Excel.Range)
Dim wdApp As Object
Dim wdDoc As Object
Dim wdRng As Object
Dim wdRngIndex As Object
Dim sMsg As String
Dim bOldOption As Boolean
Dim rCell As Excel.Range
Dim sTextToCheck As String
‘Create new Word instance
Set wdApp = CreateObject(”Word.Application”)
‘Store the old CheckGrammer option and make sure it’s
‘currently set to True
bOldOption = wdApp.Options.CheckGrammarAsYouType
wdApp.Options.CheckGrammarAsYouType = True
‘Create a new document and define a range
Set wdDoc = wdApp.Documents.Add
Set wdRng = wdDoc.Range
‘Loop through the cells and build a string to stick into Word.
For Each rCell In xlRange.Cells
sTextToCheck = sTextToCheck & ” ” & rCell.Text
Next rCell
‘Put the text into Word
wdRng.Text = sTextToCheck
‘Count the grammatical errors
If wdRng.GrammaticalErrors.Count = 0 Then
sMsg = “No grammatical errors”
Else
‘Initialize the prompt for the message box
sMsg = “The following sentences have grammatical errors:” _
& vbNewLine & vbNewLine
‘Loop through the Word ranges that contain errors
For Each wdRngIndex In wdRng.GrammaticalErrors
sMsg = sMsg & Trim(wdRngIndex.Text) & vbNewLine
Next wdRngIndex
End If
‘Show result
MsgBox sMsg, vbOKOnly, “Grammar Check”
‘Reset options and close everything
wdApp.Options.CheckGrammarAsYouType = bOldOption
wdDoc.Close False
wdApp.Quit
Set wdApp = Nothing
End Sub
Sub TestGrammarSub()
GrammarCheck Sheet1.Range(”A1:A5″)
End Sub
With this range
you get this output
Thanks to Robin for the suggestion.
Jon Peltier:
“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
20 October 2004, 12:14 amDick:
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?
20 October 2004, 8:27 amJon Peltier:
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
20 October 2004, 7:11 pmDick:
That’s freakin’ brilliant. I can’t wait to try it.
20 October 2004, 8:35 pmAndy Pope:
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
21 October 2004, 3:56 amPrivate 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