Excel: Error Checking in Macros and File Commands

 Last reviewed: July 16, 1997
Article ID: Q92557

The information in this article applies to:
  • Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
  • Microsoft Excel for OS/2, version 3.0
  • Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0

SUMMARY

In Microsoft Excel, you can use an error checking routine in a macro to determine if a condition exists and then specify what should happen. The following two macros use IS functions to determine if a certain condition exists.

Macro to Check for a File Called SHEET1.XLS

   =ERROR(FALSE)
   =IF(ISERROR(ACTIVATE("sheet1.xls")),OPEN("sheet1.xls"))
   =ERROR(TRUE)
In the event of an error, ERROR(FALSE) allows the macro to continue without displaying an error message. ERROR(TRUE) resets error checking to normal. If the document is open, Microsoft Excel will activate the document. Otherwise, the ACTIVATE statement produces an error and ISERROR returns a true condition. This causes the IF statement to run the OPEN statement to open SHEET1.XLS.

Formula to Determine if Menu Item Exists

The following formula allows Microsoft Excel to determine if a menu item exists. If the menu item does not exist, it is added to the menu.

=IF(ISNA(GET.BAR(1,OFFSET(network,0,0,1,1),0)),ADD.MENU(1,network,9))

The defined name given to the menu definition table is "network." The OFFSET function returns the top left cell of the menu definition table which is the name of the menu itself. If the menu exists, the GET.BAR function will return a number equal to the position of the menu item. If the menu does not exist, the GET.BAR function will return the #N/A value at which point the IF statement will run the ADD.MENU function.

MORE INFORMATION

For more information on ISERROR, ISNA, GET.BAR, and other functions, refer to the "Microsoft Excel Function Reference."

REFERENCES

"Microsoft Excel Function Reference," version 4.0, 10, 35, 190-191 "Microsoft Excel Function Reference," version 3.0, 68, 135-136

Additional query words: 5.00 3.00 4.00 3.0 4.0 error

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.