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

 Last reviewed: July 16, 1997
Article 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.

  1. 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)

  2. Select cell A1 on your macro sheet.

  3. 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

Additional query words: 5.00 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.