XL: Error Running Inserted Text File Macro from Another Macro

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 5.0, 50.a
• Microsoft Excel 98 Macintosh Edition

SYMPTOMS

If you run a Microsoft Visual Basic for Applications procedure that inserts a macro as a text file into a module, and then runs the macro in that module, you receive the following error message:

   Run-time error '1004':

   Cannot find macro 'MacroName'

CAUSE

This error appears if either of the following is true:

• You insert the macro into an existing module. (This is the case if you have already inserted a macro as a text file into the same module.)

      -or-
  

• You have the Require Variable Declaration option selected.

Note that because of this behavior, it is possible to run a procedure that inserts two macros with the same name into the same module, and then runs the macro inserted first, because the second macro is not recognized until the procedure that inserts the two macros is finished.

The error occurs in the second case because the macro is inserted above the Option Explicit statement. The Option Explicit statement must appear at the beginning of a module.

WORKAROUNDS

To avoid this error message when you run a Visual Basic procedure to insert an entire macro into a new module, and then run the macro, do the following:

• Insert the macro as a text file into a new module. Use the Add method of the Modules object to add a new module to the workbook before inserting the macro.

      -and-
  

• Clear the Require Variable Declaration check box on the Module General tab of the Options dialog box. This option adds the Option Explicit statement automatically when you create a new module.

• Combine the macros in one text file. You cannot add them as separate text files into the same module.

      -or-
  

• Insert each text file macro into a new module.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.