Excel: Macro Error Using SPELLING.CHECK() on Blank Cells

ID: Q83493

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("Range",8)
   A4: =FOR.CELL("Current",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.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 416-417

Keywords :
Version :
Platform :
Issue type :