Excel: Error Checking in Macros and File Commands

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: 3.0 error


Keywords          : 
Version           : 
Platform          : 
Issue type        :

Last Reviewed: March 29, 1999