XL: Error Calling Macro Contained in Open Add-In

ID: Q115208

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows

SYMPTOMS

When you call a macro that is contained in an add-in file, either by using the Call statement or by using the macro name in a Visual Basic procedure in Microsoft Excel, you receive one of the following error messages, even if the add-in that contains the macro is open at the time:

   Sub or Function not defined

   -or-

   Compile error:
   Sub or Function not defined

CAUSE

This behavior occurs when the add-in that contains the macro that you are calling is not selected in the References dialog box. When you open an add- in file, the add-in is added to the References dialog box, but is not selected. This is by design of Microsoft Excel.

WORKAROUNDS

To avoid receiving an error message when you call a macro that is contained in an add-in file from a Visual Basic procedure, do either of the following:

Open the add-in file (either before you run the macro, or as a command in the macro), and then use the Run method of the Application object to run the macro as in the following example:
```
      Application.Run("ADDIN.XLA!Macro_Name")
```
-or-
Reference the add-in from the procedure that is calling the add-in macro with the following steps. (Note that when you use this procedure, the path to the add-in file is hard-coded in the module, so that in order to run the procedure, the add-in file must be located in the same directory as when you referenced it.)
1. Activate the module that contains the procedure that is running the
```
      add-in macro.
```
2. On the Tools menu, click References.
3. If the add-in that contains the macro you want to run is listed in
```
      the Available References list box, select (check) its checkbox. Click
      OK, and skip step 4.
```
```
      If the add-in that contains the macro you want to run is NOT listed
      under Available References, click Browse.
```
4. From the File Name list, select the add-in you want to reference and
```
      click OK. Click OK again.
```

MORE INFORMATION

When you use the References dialog box to directly reference another workbook, add-in, or object library file, you can run a macro contained in the referenced file using the Call statement and the macro name, or only the macro name.

For more information about running a macro that is contained in an add-in, query on the following words in the Microsoft Knowledge Base:

   tools and references and addin and run

REFERENCES

For a definition of a Referenced Project, click the Search button in Help and type:

   referenced project

Additional query words: XL97 97 7.00 5.00 err msg

Keywords          : kbprg kbdta xladdins KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97
Platform          : WINDOWS

Last Reviewed: January 2, 1999