Calling Add-in functions from VBA

When an Excel add-in is active any User Defined Functions (UDFs) in the add-in are available to any other open worksheet.  It might be expected that the same would apply to VBA routines defined in other files, but if you try calling an add-in function from VBA (other than from the add-in itself), you will find the function is not recognised.

This is an easy problem to fix.  The add-in needs to be referenced in the Visual Basic Editor.  Select Tools-References and either select the add-in from the list of available references, or if it is not on the list, use the browse button and select the add-in file.

Note that:

  • The add-in must be referenced for each file in which you wish to refer to it from VBA.
  • The add-in is referenced using the VBA Project name, not the file name.  It follows that the project name must be changed from the default (VBAProject), before selecting the reference.

An example of referencing the XNumbers add-in is shown in the screen shot below:

Referencing and add-in

This entry was posted in Excel, UDFs, VBA and tagged , , , , . Bookmark the permalink.

2 Responses to Calling Add-in functions from VBA

  1. David Heiser says:

    Found this out back in 2004.

    You should also take a look at XNUMBERS for highly accurate multiple digit (up to 500) computations in vba and in Excel.


  2. David McIntosh says:

    “The add-in is referenced using the VBA Project name, not the file name.” This is not entirely correct. It is presented to the user via project name, and the user specifies the reference via project name, but then Excel turns around and remembers the reference via project name AND filename, making filename-versioning impossible.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s