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:
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.
LikeLike
“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.
LikeLiked by 1 person