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

arunsjain

Board Regular
Joined
Apr 29, 2016
Messages
130
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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:

Code:
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.
 
Upvote 0
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
 
Upvote 0
Try this version, it should handle both of your issues.

Code:
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
 
Upvote 0
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
 
Upvote 0
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:

Code:
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!
 
Upvote 0
Thank you so much Eric. Yes it is much faster.

You are simply great :-)

Kind Regards
Arun
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top