XL: Error Running Inserted Text File Macro from Another Macro

ID: Q117363

The information in this article applies to:

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

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 message occurs if either of the following conditions 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.

The error message occurs because when you run a procedure that inserts a macro as a text file into a module that has already been compiled, the existing module is not decompiled to recognize the new macro until the procedure that inserts the macro is finished.

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.

WORKAROUND

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, follow these steps:

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 Editor tab (or Module General tab) of the Options dialog box. This option adds the Option Explicit statement automatically when you create a new module.

If you are inserting more than one macro into the module, do either of the following:

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.

Additional query words: 5.00 5.00a 5.00c 7.00 err msg

Keywords          : kberrmsg kbdta KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,7.0a,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: December 8, 1998