XL: Error Calling Macro Contained in Open Add-In

• 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 err msg

Keywords : kbprg kbdta xladdins KbVBA
Version : WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : WINDOWS winnt
Issue type :