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