Highlight Misspelled Words In Excel With A Simple VBA Macro - Macro Mondays

Hello and welcome to your weekly instalment of #macromonday. A great way to learn how to write Excel VBA macros. Today we will let’s look at mistakes, or being more specific misspelled words. You may or may not know that Excel has a spell checker that you can easily access and use. Yes, that’s correct, even Excel will check your spelling. If you want to manually spell check your Excel document then follow the steps below.

Open the spreadsheet that you want to spell check
Hit the keyboard shortcut F7 or use the Review Tab | Proofing Group | Spelling.
Whatever cell your cursor is in, Excel will start ca spell check from that cell, but once it reaches the end of its spell check it will prompt to check all cells that were before the one you started the spell check off at.
Excel will confirm when the spell check is complete.

But this is #macromonday isn’t it, surely I have a macro for you?. Of course, I do. This little bit of code will spell check your Excel worksheet and highlight any incorrect spellings in any colour you choose. Handy that isn’t it?. Let’s crack on and get coding,

What Does The Macro Do?

This little macro will highlight an incorrect (or what it considers incorrect) spellings of text in your excel worksheet. We use the Application.SpellChecking method, which checks the spelling of individuals words.

How Does The Macro Work?

The syntax is straightforward:=

expression. CheckSpelling( _Word_ , _CustomDictionary_ , _IgnoreUppercase_ )

The only parameter that is required or not optional is the first one – Word, the rest are optional.

Name	Required/Optional	Data Type	Description
Word	Required	String	(used only with Application object). The word you want to check.
CustomDictionary	Optional	Variant	A string that indicates the file name of the custom dictionary to be examined if the word isn’t found in the main dictionary. If this argument is omitted, the currently specified dictionary is used.
IgnoreUppercase	Optional	Variant	True to have Microsoft Excel ignore words that are all uppercase. False to have Microsoft Excel check words that are all uppercase. If this argument is omitted, the current setting will be used.

The result is a True or False one. That is is the word is found in one of the dictionaries then it is TRUE, if not FALSE. We then fill colour the cells if the word is not found in the dictionary. (spelt incorrectly).

Let’s Get Coding

Step 1. Open the Visual Basic Editor. You can do this by either selecting the Developer Tab | Code | Visual Basic. Alternatively, you can hit ALT+F11 as a shortcut to open the Visual Basic Editor.

Step 2. We need to insert a new module either into the current workbook or in your Personal Macro Workbook (PMW). If you want to make the code available to all Excel workbooks then insert a new module into your PMW. If you want to read more about the PMW then feel free to read my previous blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Step 3. We need to declare a variable.

Dim wd as Range

This simply creates a memory container for this value.

Step 4. We use the For Each…Next Looping method this loops through all of the used cells in the Active Sheet in the current workbook.

Step 5. If a word is not in the dictionary it is highlighted in RED.

Step 6. The process is repeated until all ranges have been checked.

That’s it. The Macro is complete. If you save this macro in your Personal Macro Workbook then you can call or use it on any of your Excel workbooks. Do leave a comment below if use the spell check option in Excel or even if you do not.