VBA: detecting if a library is available at run-time

Question

I have an xla file that refers to some other Excel add-ins that may or may not be present on the target machine. Currently Excel fails on loading the xla with a "Compile error: Can't find project or library".

Code is something like:

  Dim result as Integer
  result = SomeAddIn.SomeFunction(x)

Prefixing this with an "On Error Goto AddInMissing" doesn't help as this is seen as a compile error.

So, is there any way of late-binding/referencing an add-in by name in VBA so that I can fail gracefully (disable certain features) if a certain add-in is not present?

Answer 1

You can examine the Addins Collection;

if AddIns("The addins title").Installed = True then ...

or you can examine file names

For Each Item In AddIns
   Debug.? Item.Path, Item.Name
Next

Answer 2

Thanks everyone. Iterating through Application.VBE.VBProjects allows me to detect if the xla is present. The next step was to wrap all calls to the functions defined in the target xla in a wrapper module. This was necessary as I'm trying to avoid getting compile errors. I only call these wrapper functions if the xla is available and fortunately VBA doesn't compile modules until they are needed (this basically meant creating two wrappers - the outer wrapper checking if the xla was available and the inner one calling the target xla).

A more sophisticated solution would probably look at both AddIns and VBE.VBEProjects

Answer 3

You don't need to use VBE.VBProjects or Addins: an XLA is a hidden member of the Workbooks collection so if you know the name of the XLA you can directly check in the Workbooks collection to see if it is open. This way you do not need the user system to have Trust Access to VBE