PDA

View Full Version : [SOLVED] Spell Check in Excel-Auto Select First Recommendation Using VBA



kellywalters
05-10-2019, 12:25 PM
I am looking for some VBA code to be used in Excel VBA that will perform spell check on a selected range on a single Excel worksheet. If a word is found to be misspelled I would like that misspelled word to automatically be changed to the first suggestion that would normally appear in the dialog box. I do recognize the possible data implications but I will be keeping record of the ORIGINAL word for this purpose.

My range could contain up to 30,000 rows of data so the time saving component of the spell check without user involvement is key!

For example, my data set might include "Printter" instead of "Printer". I am looking for a macro that would identify that "Printter" is spelled incorrectly and then update it to "Printer" without the user having to view ALL misspelled words and then having to press "Change" from the dialog box that appears.

I have seen some similar macros but they have all been only in Word. This macro must function in Excel.

Any help is MUCH appreciated!

georgiboy
05-13-2019, 03:05 AM
Hi Kelly,

Welcome to the forum.

The below is not a perfect or finished example of this but thought i would share to maybe help with some direction. The below needs a reference to the MS Word Object Library.


Sub CheckSpellWord()
Dim tmpVar As Variant
Dim sugg As String
Dim var As Variant
Dim tmpStr As String
Dim wd As Word.Application

If wd Is Nothing Then
Set wd = New Word.Application
wd.Documents.Add
End If

var = Range("A2:A100").Value

On Error Resume Next
For x = 1 To UBound(var)
tmpStr = var(x, 1)
tmpVar = Split(tmpStr, " ")
For Each r In tmpVar
sugg = wd.GetSpellingSuggestions(r)(1)
If sugg <> "" Then
var(x, 1) = Replace(var(x, 1), r, sugg)
'Debug.Print "Replaced: " & r & " - With: " & sugg ' logs the changed words
End If
sugg = ""
Next r
Next x

'Range("A2:A100") = var ' will replace the original data
Range("B2:B100") = var

wd.Quit
End Sub

Hope this helps

kellywalters
05-13-2019, 04:46 AM
Thank you so very much
georgiboy! Just to confirm that I am following the code correctly...

The macro takes the data in cells A2:A100, reviews them via spell check in Word, and then pastes the corrected version in cells B2:B100?


Hi Kelly,

Welcome to the forum.

The below is not a perfect or finished example of this but thought i would share to maybe help with some direction. The below needs a reference to the MS Word Object Library.


Sub CheckSpellWord()
Dim tmpVar As Variant
Dim sugg As String
Dim var As Variant
Dim tmpStr As String
Dim wd As Word.Application

If wd Is Nothing Then
Set wd = New Word.Application
wd.Documents.Add
End If

var = Range("A2:A100").Value

On Error Resume Next
For x = 1 To UBound(var)
tmpStr = var(x, 1)
tmpVar = Split(tmpStr, " ")
For Each r In tmpVar
sugg = wd.GetSpellingSuggestions(r)(1)
If sugg <> "" Then
var(x, 1) = Replace(var(x, 1), r, sugg)
'Debug.Print "Replaced: " & r & " - With: " & sugg ' logs the changed words
End If
sugg = ""
Next r
Next x

'Range("A2:A100") = var ' will replace the original data
Range("B2:B100") = var

wd.Quit
End Sub

Hope this helps

georgiboy
05-13-2019, 06:09 AM
That's right,

It's not pasting the result though it's just placing an array on the page.

Thought this might get you going in the right direction.

Fluff
05-15-2019, 06:14 AM
Cross posted https://www.mrexcel.com/forum/excel-questions/1097249-spell-check-excel-auto-select-first-recommendation.html

macropod
05-15-2019, 04:21 PM
kellywalters: Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3