Spell Check in Excel-Auto Select First Recommendation Using VBA

[kellywalters]

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]

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]

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]

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]

Cross posted https://www.mrexcel.com/forum/excel-...mendation.html

[macropod]

kellywalters: Please read VBA Express' policy on Cross-Posting in Rule 3: http://www.vbaexpress.com/forum/faq...._new_faq_item3