0

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?

John Pickup
  • 31
  • 2
  • May be of interest: http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.programming/2005-03/1672.html – Fionnuala Jun 22 '10 at 11:05

3 Answers3

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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • 1
    +1 but writing `If x = True` is just horribly, horribly wrong. – Konrad Rudolph Jun 22 '10 at 11:04
  • Thanks, that has revealed something although unfortunately not quite what I'm after. (Please excuse my VBA nube-ness) Enumerating the AddIns collection lists the Add-ins as shown by the "Tools|Add-Ins..." menu in Excel. This differs from the list of VBA projects you see in Excel's VB envionment. The particular xla that I'm interested in is listed as a Project here and isn't in the AddIns collection. Is there a way of enumerating these too? (it seems that the workbooks collection doesn't appear to provide this - the first thing I tried) – John Pickup Jun 22 '10 at 12:03
  • May need to mess with Macro Security settings & "Trust access to VBA project settings" but try; For Each proj In Application.VBE.VBProjects ..Debug.Print proj.Name .. Next – Alex K. Jun 22 '10 at 13:12
1

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.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
0

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

John Pickup
  • 31
  • 2