Create a list of all spell error in a workbook with worksheet name and cell reference?

[arunsjain]

Hi,

is there any way to create a list of all spell errors in a workbook? Add new worksheet with a name "Spell Error" which includes following:

a) Column A, Spell Error
b) Column B, Worksheet name where error cell is located
c) Column C, Cell reference which got that error
d) Column D, spelling suggestion, if any (optional).

Thank you very much for your help..

Kind Regards
Arun

 

[Eric W]

Sure.

1) Open a copy of your workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, select Insert > Module
4) Paste the following code into the sheet that opens:

Sub CheckSpell()
Dim ctr As Long, wsp As Worksheet, ws As Variant, c As Variant

    ctr = 0
    Set wsp = Worksheets.Add
    wsp.Name = "Spell Error"
    For Each ws In Worksheets
        For Each c In ws.UsedRange
            If Application.CheckSpelling(c.Value) = False Then
                ctr = ctr + 1
                wsp.Cells(ctr, "A") = c.Value
                wsp.Cells(ctr, "B") = ws.Name
                wsp.Cells(ctr, "C") = c.Address
            End If
        Next c
    Next ws
    
    If ctr = 0 Then wsp.Range("A1") = "No errors found"
End Sub

5) Press Alt-Q to close the VBA editor
6) From Excel, press Alt-F8 to open the macro selector
7) Select CheckSpell and click Run

This checks each cell. If the cell says "Cats are stoopid" then you'll only get get the cell reference of the cell. If you want, I could check the spelling of individual words in the cell and highlight misspelled words. Also, depending on what formulas you have in your workbook, you might get some odd results.

Let us know if this works for you.

 

[arunsjain]

Great Eric.

It works fine.

It also include "Spell Error" worksheet's spelling error in the list. How to exclude that? If we run second time it gives run time error "That name is already taken. Try a different one". Is it possible to delete the old worksheet of "Spell Error" and create new?

Highly appreciate your help Eric.

Kind Regards
Arun

 

[Eric W]

Try this version, it should handle both of your issues.

Sub CheckSpell()
Dim ctr As Long, wsp As Worksheet, ws As Variant, c As Variant

    ctr = 0
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Spell Error").Delete
    Set wsp = Worksheets.Add
    wsp.Name = "Spell Error"
    For Each ws In Worksheets
        If ws.Name <> "Spell Error" Then
            For Each c In ws.UsedRange
                If Application.CheckSpelling(c.Value) = False Then
                    ctr = ctr + 1
                    wsp.Cells(ctr, "A") = c.Value
                    wsp.Cells(ctr, "B") = ws.Name
                    wsp.Cells(ctr, "C") = c.Address
                End If
            Next c
        End If
    Next ws
    
    If ctr = 0 Then wsp.Range("A1") = "No errors found"
End Sub

 

[arunsjain]

Perfect Eric.. You are great !!!!!!!!!!!!!!

 

[Eric W]

Happy to help!

 

[arunsjain]

Hi Eric,

Above code work smoothly. Is there any way to make it fast? Because in big file with multiple worksheets it takes time. could you please include Array kind of thing to make it fast?

Highly appreciate your help Eric.

Kind Regards
Arun

 

[Eric W]

The short answer is no, I can't make it much faster. The problem is with the "Application.Checkspelling" line. That's a built-in function to Excel, and it's slow. It has to read the internal dictionaries to decide if a word or phrase is properly spelled or not. Based on your actual data, it might be possible to do some preprocessing to avoid calling it for certain cells, but I don't know what your data is like.

This is about the best I can do to speed it up:

Sub CheckSpell()
Dim ctr As Long, wsp As Worksheet, ws As Variant, r As Long, c As Long, vals As Variant

    Application.ScreenUpdating = False

    ctr = 0
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Spell Error").Delete
    Set wsp = Worksheets.Add
    wsp.Name = "Spell Error"
    On Error GoTo NoVals:
    
    For Each ws In Worksheets
        If ws.Name <> "Spell Error" Then
            vals = ws.UsedRange.Value
            For r = 1 To UBound(vals)
                For c = 1 To UBound(vals, 2)
                    If Len(vals(r, c)) > 0 Then
                        If Application.CheckSpelling(vals(r, c)) = False Then
                            ctr = ctr + 1
                            wsp.Cells(ctr, "A") = vals(r, c)
                            wsp.Cells(ctr, "B") = ws.Name
                            wsp.Cells(ctr, "C") = Cells(r + ws.UsedRange.Row - 1, c + ws.UsedRange.Column - 1).Address(0, 0)
                        End If
                    End If
                Next c
            Next r
        End If
NextSheet:
    Next ws
    
    If ctr = 0 Then wsp.Range("A1") = "No errors found"
    
    Application.ScreenUpdating = True
    Exit Sub
    
NoVals:
    Resume NextSheet:
    
End Sub

It reads the data from each sheet in one shot, instead of a cell at a time. It also does not call CheckSpelling if the cell is empty. Depending on your workbook, you might see some improvement.

Good luck!

 

[arunsjain]

Thank you so much Eric. Yes it is much faster.

You are simply great

Kind Regards
Arun