Urban Adventures & More

Suppose you developed a set of macros to be run on several different excel installations. Some may be different versions or, as is the case at my office, some have Bloomberg libraries and others don’t. This creates a problem because excel will get set to use certain libraries and give strange error messages when you don’t have them.

The manual solution is to go to the VB Editor (Alt-F11) and change the references to the correct ones every time you open the problem (Tools->References and change those that are labeled as MISSING).

The better solution is to have a list of alternatives libraries that work and enable and disable those modules at startup. Here is how I did it. Note that for this code to work you have to go to Tools -> Options -> Security -> Macro Security -> Trusted Publishers and check “Trust access to Visual Basic Project” on every computer that will be using the code. That is only a one-time exercise, after which they can happily use it without interference.

This code is based on this and  this. (Actually, they seem to explain it all, but I like my way just as much.)

1. Get the GUID for each library you will be using. I used the following code:
   
Sub guid()
Dim typelib As Variant

   Dim cell As Range
   For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
   Set theref = ThisWorkbook.VBProject.References.Item(i)
   ActiveCell.Value = theref.Description
   ActiveCell.Offset(0, 1).Value = theref.guid
   ActiveCell.Offset(1, 0).Activate
   Next i
   End Sub

   You should find the module names you want in that list and note down the GUIDs. You will later use the GUID to activate or deactivate trouble modules.

2. The goal now is to disable trouble modules (since they cause compile errors at startup) when you save. This can be done by adding the following code:
   
Const BBGUID = "{F2303253-4969-11D1-B305-00805F815CBF}"

   Sub remove_bb_reference()
   Dim ref As Variant
   For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
   Set ref = ThisWorkbook.VBProject.References.Item(i)
   If ref.guid = BBGUID Then
   ThisWorkbook.VBProject.References.Remove ref
   Exit For
   End If
   Next i
   End Sub

   I set a global constant BBGUID to contain the GUIDs from the first step, and now I check all the references in the workbook against that one. Once it is found, I disable it (you will have to adapt the code to do a list of trouble GUIDs).

3. You will want this code to be run when you save the workbook so the references are not there when you open it. Add this code to the “ThisWorkbook” Excel objects in VBA editor:
   
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MyModule.remove_bb_reference
End Sub

   where “MyModule” is the name of the module containing the code from the previous point.
4. Now we need code to enable the target module at startup.
   
Sub add_bb_reference()
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid BBGUID, 1, 0
Select Case Err.Number
Case Is = 32813
' ref already exists
Case Is = vbNullString
' no issue
Case Else
' error; pass
End Select
On Error GoTo 0
End Sub

   We try adding the code; if it throws an error, in this case I move on because the computer just doesn’t have the library. If you were mediating different versions of excel, the “Case Else” line would have to try loading another acceptable library.
5. Finally, have the add code run at startup. Again, in the “ThisWorkbook” object:
   
Private Sub Workbook_Open()
ExcelFunctions.add_bb_reference
End Sub