1

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.

innominate227
  • 11,109
  • 1
  • 17
  • 20
  • 3
    You should look in to late binding, that way you can do a bit of error checking and running only the proper code: http://support.microsoft.com/kb/245115 – sous2817 Aug 09 '13 at 17:20
  • http://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically-vba-excel – Mr.Monshaw Aug 09 '13 at 18:19

1 Answers1

2

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.

V.B.
  • 6,236
  • 1
  • 33
  • 56
  • This worked. The hard part was figuring out the name I needed to pass. For the other the string you have to pass to get BLP_DATA_CTRLLib.BlpData is GetObject("Bloomberg.Data") – innominate227 Aug 29 '13 at 14:00
  • Yeah, to know the name is sometimes the hardest thing. Cool that this has worked! – V.B. Aug 29 '13 at 17:45