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

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 somethin开发者_如何学运维g 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?

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

You don't need to use VBE.VBProjects or Application.AddIns: an XLA is a hidden element in the Workbooks collection so if you know the name of the XLA add-in you can directly check in the Workbooks collection to see if it is open:

Public Function IsLoaded(ByVal AddInName As String) As Boolean
    On Error Resume Next
    Dim xla As Object
    Set xla = Application.Workbooks(AddInName)
    IsLoaded = Not xla Is Nothing
    On Error GoTo 0
End Function

This way you do not need the user system to trust access to the VBE Extensibility object model.

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