CODEWITHSUNDEEP
excelvba
innominate227
innominate227

Reputation: 11703

VBA Missing Reference Causes Others to not load

I Have 2 VBA Macros in an Excel. Macro 1 copies some values from another Excel file. Macro 2 requests some values from Bloomberg. Macro 1 requires a reference to "Microsoft Excel 12.0 Object Library", and Macro 2 requires a reference to the "Bloomberg Data Type Library" to be added.

I would like it so that users can run Macro 1 even when on a machine without the Bloomberg Excel API installed. However when I try to run Macro 1 I get to this line:

Set XL = CreateObject("Excel.Application")

below and get the following error:

 "Cant Find Project or Library"

It seems like since it can not find the "Bloomberg Data Type Library" reference it is not loading the "Microsoft Excel 12.0 Object Library" either.

Everything works as expected on a machine with the Bloomberg Excel API installed.

Upvotes: 1

Views: 2282

Answers (1)

V.B.
V.B.

Reputation: 6382

If you want the same file to work on both machines, first remove the reference to BBG in VBA Editor -> Tools -> References. Then in your code do something like that:

On Error Resume Next ' turn off errors
Set XL = CreateObject("Excel.Application")

Set BBG = CreateObject("BBGName")

If Not IsEmpty(BBG) Then
    ' Do work with BBG

End If
On Error GoTo 0 ' turn on errors

That will allow you to use BBG macro only when BBG library is present, without throwing an error if it is not.

Upvotes: 2

Related Questions