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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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.
“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
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?
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
That’s freakin’ brilliant. I can’t wait to try it.
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
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.
This seems to be corrected in 2010.
I used:
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
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
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
Hi,
Is there a development on checking sentences for grammar errors in the cells of an Excel spreadsheet?
Thanks/ Rameshwar