Microsoft KB Archive/83493

{|
 * width="100%"|

Excel: Macro Error Using SPELLING.CHECK on Blank Cells

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0

-

SUMMARY
In Microsoft Excel, a reference to a blank cell in the word_text argument of SPELLING.CHECK will result in a Macro Error.

MORE INFORMATION
Microsoft Excel versions 4.0 and later provide a macro function that allows non-interactive spell checking. The function, SPELLING.CHECK, takes three arguments; a word or text argument, the name of a custom dictionary and whether or not to ignore uppercase letters. If the word text argument is a reference to a blank cell you will receive a macro error when this function is called.

To use SPELLING.CHECK without getting an error on blank cells, use a FOR.CELL loop as in the following example. Note that the skip_blanks argument to FOR.CELL is set to TRUE.

This function macro simply returns true if all the words in the given range are spelled correctly and false if any misspelled words are found.

  Enter the following macro code in a new Macro sheet.

  A1: SpellCheck A2: =RESULT(4) A3: =ARGUMENT(&quot;Range&quot;,8) A4: =FOR.CELL(&quot;Current&quot;,Range,TRUE) A5: = IF(NOT(SPELLING.CHECK(Current)),RETURN(FALSE)) A6: =NEXT A7: =RETURN(TRUE)  Select cell A1 on your macro sheet. From the Formula menu, choose Define Name. Select the Macro Function option on the Define Name dialog box and choose OK.

This function macro can now be called from a command macro to check a selected range using the call =SpellCheck(SELECTION). To check a specified range, use =SpellCheck(A1:B10). Note that if all the cells in the range are blank, SpellCheck will return TRUE not FALSE since it is skipping blank cells and finding no misspelled words.