Excel can spell check, but it's not as capable as the spell check in Word, nor is it automatic. Using VBA, you can automate the spell-checking process in your workbooks. In this guide, we’re going to show you how to spell check using VBA in Excel.
Spell checking in Excel
Excel's spell checking is a bit different than Word's. It does not underline the misspelled words or check grammar as you type. You need to run spell check manually unless you automate it using VBA.
There are two ways to manual spell check in Excel:
- Pressing the F7 key or
- Clicking Spelling in Review tab of the Ribbon
Excel will show the Spelling dialog if there is a misspelled value inside a cell.
You can use the buttons to ignore or correct the spelling or add the word into the dictionary to remove warnings.
Spell check using VBA in Excel
VBA has a dedicated method for spell checking: CheckSpelling
However, how the CheckSpelling method works differs according to the parent item you call it for. You can call the method either for a range object (cell, range of cells or a worksheet), or the application object.
The CheckSpelling method under a range object displays the Spelling dialog box for the spelling errors in the context range.
You can use any range object for expression. Such as the following line checks the content of the specified cells in the range object.
Use Selection object to create a reference to the select cell or range.
You can use any valid range reference to run the spell checking. You can learn more about references in VBA, check out How to refer a range or a cell in Excel VBA.
If you want to spell-check the entire sheet, including parts like header and footer, use a worksheet object instead. For example, use the following code to test the active worksheet.
To test a specific sheet, use either the name of the sheet or the index with a Worksheets or Sheets object.
The CheckSpelling method of the Application object returns a Boolean, (True/False) value instead of showing the Spelling dialog box.
The result will be a spelling error, the method returns True. Another difference from the Range alternative is the argument requirement. You must supply the text to the method as a parameter.
This capability allows you to create scenarios like highlighting misspelled cells: How to highlight misspelled cells in Excel using VBA