Reference to add-in required in spreadsheet if the add-in uses a DLL?

[Terry Haywood]

I have an add-in that uses a DLL for some of its
calculations. The add-in has references to the DLL (VB
editor, tools, references) and it is loaded in XL (tools,
add-ins, add-in is checked). My spreadsheet can use
functions in the add-in that don't require the DLL but
gets a #NAME error on add-in functions that use the DLL
_unless I also put a reference to the add-in in the
spreadsheet_.

Scenario:
MyAddIn.xla (has reference to DLL defined)
- Function_that_uses_DLL
- Function_that_does_not_use_DLL

MyAddIn is loaded in XL

My_XL_App.xls without a reference to MyAddIn
- call to Function_that_uses_DLL gets #NAME error
- call to Function_that_does_not_use_DLL works

Add reference to MyAddIn to MyXLS
- both add-in functions work

Question: Do I have to put a reference to the add-in in my
XLS code to be able to use add-in functions that need the
DLL? What I expected (and want) to happen is:
- I call a function from a spreadsheet by for example
putting "=Function_that_uses_DLL" in a cell
- Excel finds the function in MyAddIn and uses the
reference _defined in the add-in_ to locate the DLL

I don't want to have to put a reference to the add-in in
the spreadsheet for a couple reasons:
- Users creating new applications using add-in functions
have to know they need to use
the code editor to create a reference (they have to know
how the add-in works)
- I use a naming scheme for add-ins such that the file
name changes on new versions. Because the
add-in file name changes I have to change references in
all spreadsheets that use the add-in.
This is particularly problematic because users access
spreadsheets from a shared drive and do
not necessarily have the same AddIn version installed.

Terry

 

[Ronald Dodge]

For anything that you have references to, it will need to be references as
there is no exception about this to my knowledge. However, there is one
work around to this that would not require the user to know how to operation
the Add-Ins dialog box. You can setup a macro that will execute upon either
the file or program to open. If it's just for the file, then you can either
create an "Autpen" macro in a standard module or use the "Open" event on
the Workbook Object, which will create a "Workbook_Open" macro.

Use the Installed property on the Add-In object to see if it's installed
like the following:

AddIns("analysis toolpak").Installed = True

If it returns an error, you need to add the addin to the list with the Add
method of the Addin Object and set the installed property like the
following:

AddIns.Add("generic.xll").Installed = True

Now thirdly and last, but not least, just cause an add=in may be listed as
installed, it doesn't necessarily mean it will work 100% of the time, and
that's cause it's just a reference to it, so to be sure that the functions
will work, you need to be sure that the workbook is openned as add-in files
are normally openned in hidden state.

For addtional info on this, take a look at the AddIn Object help file in the
VBA help.