XL5: Function Wizard Error When Function Contains Loop

• Microsoft Excel for Windows, versions 5.0

SYMPTOMS

In the versions of Microsoft Excel listed in the beginning of the article, if you use the Function Wizard to insert a function that contains a loop, you will receive an error message in the macro.

CAUSE

This error occurs because of the way the Function Wizard calculates the results of functions when you select them.

WORKAROUND

To prevent this error from occurring, you can do any one of the following:

• Use a Visual Basic, Applications Edition, subroutine instead of an MS Excel 4.0 macro. This error does not occur when using Visual Basic, Applications Edition, functions.

  -or-

• When the Macro Error dialog box is displayed, choose the Halt button. This will allow you to proceed normally.

  -or-

• Upgrade to Microsoft Excel version 5.0c for Windows. The error does not occur in this version of Microsoft Excel.

MORE INFORMATION

When you use the Function Wizard to insert a function into a formula in Microsoft Excel version 5.0, the Function Wizard calculates the result of the function before it actually places the function in the formula.

If the function contains a loop, and if the function is written using the Microsoft Excel 4.0 Macro language, the Function Wizard cannot properly handle the loop, and a macro error will be reported at the locations in the macro where the following commands occur:

   =FOR() command
   =NEXT() command
   =WHILE() command

Note that this error occurs ONLY when you use an MS Excel 4.0 function macro in Microsoft Excel version 5.0 for Windows.

This error does not occur when you use a function written in Visual Basic, Applications Edition, nor does it occur in Microsoft Excel version 5.0 for the Macintosh or in Microsoft Excel version 5.0c for Windows.