First, apologies for the generic title - if anyone can suggest a better one, I'd be happy to change it, but right now, I have no clue where to start.

I have a workbook utilizing a DLL to access a data provider (Bloomberg), and the requirements to get it to work correctly are quite tricky. Furthermore, deployment is a nightmare, since users might need to reference the DLL themselves. Naturally, I first check wether the library is referenced, before testing the library itself.

Here's my code (which is working as intended) :

Public Sub TestBloomberg()
Dim ref As Object
Dim fRef As Boolean
fRef = False
For Each ref In ThisWorkbook.VBProject.References
    If ref.GUID = "{4AC751C2-BB10-4702-BB05-791D93BB461C}" Then
        If Not ref.IsBroken Then
            fRef = True
        End If
    End If
If fRef Then
    ' In separate Sub to get around User-defined type error
    Call TestBloombergConnection        
ElseIf Not fRef Then
    ' warn user about missing reference
End If
End Sub

As you can see, if the reference to the DLL is set, I proceed checking if the library works as intended (this has a lot of external factors at play, such as wether the server-application is running, the user is logged in, etc.) You can think of this as a simple ON-ERROR-GOTO-wrapped call to the dll.

I am forced to move the actual test of the functionality to another sub, as called from the second if-block. If I have no (or a broken) reference to the dll, even though the library will not be called itself, I will get a User-defined Error. If I move the exact same code to another sub, it will work perfectly.

Finally, my question: What happens when I run my VBA code, why do I get a (i think) runtime error during compile time? How can my code be so dependend on external factors, that it can't even get to the point of failing?

Related posts

Recent Viewed