Excel 2007 vba addin required to be referenced in projects that consume the methods?

Question

I have an Excel 2007 addin (a workbook with macros saved as an addin) that seems to require other books to reference before they can call its methods.

Is this normal? I thought public addin code could be called from anywhere?

Is there a way to have a method available to all workbooks that are opened without creating an explicit reference?

I'm also putting ribbon customizations in the addin and want to make sure these will also work.

Answer 1

As far as I know you cannot directly call procedures or functions from other workbooks in VBA unless you use one of the following methods:

• Add a reference to the addin which contains the procedure you wish to call
• Or, call the procedure using Application.Run() supplying the filename of the addin and procedure you wish to call whilst the addin is loaded, e.g. application.run("MYADDIN.XLA!MySubToRun"). It's important to note that you don't need the full path, only the filename.

You can however utilise functions in the addin in worksheet cell formulas in other workbooks whilst the addin is loaded.