milancshah
Technical User
I found the following VBA script that enables the user to select a range in Excel and identify grammar errors using the built-in MS Word Grammar function.
However, the VBA script was written for Office 2003 or prior and does not work in Office 2007. The VBA does not crash, but it simply does not work.
If someone can please review the VBA code below and provide feedback to change it so that it works with Office 2007 (Excel 2007), this would be greatly appreciated.
I have pasted the complete text from the original post to give credit to the VBA script author.
Thanks,
Milan
____
Checking Grammar
October 19, 2004, 8:34 am by Dick Kusleika
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:
Thanks to Robin for the suggestion.
___
However, the VBA script was written for Office 2003 or prior and does not work in Office 2007. The VBA does not crash, but it simply does not work.
If someone can please review the VBA code below and provide feedback to change it so that it works with Office 2007 (Excel 2007), this would be greatly appreciated.
I have pasted the complete text from the original post to give credit to the VBA script author.
Thanks,
Milan
____
Checking Grammar
October 19, 2004, 8:34 am by Dick Kusleika
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:
Code:
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 SubThanks to Robin for the suggestion.
___
