CODEWITHSUNDEEP
vbareference
zuiqo
zuiqo

Reputation: 1199

Testing for DLL references in VBA

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
Next
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?

Upvotes: 3

Views: 1094

Answers (1)

Mike
Mike

Reputation: 1324

What this behavior demonstrates is that VBA compiles separate subroutines separately and at different times. I had a similar situation when I was trying to resolve references on behalf of the users (solving a versioning problem, which I got to work, but then abandoned as not worth the trouble).

When you are ready to enter a subroutine, it interprets only as much as it needs to, and you could get a compile time error then, even though to you it seems like you are at run time.

The error you are actually getting is probably a 429 Automation error or something similar. You would get that if you have a broken link (dll moved, deleted, or not registered). What I remember from my project, is that I could reliably handle it if a good reference was saved in the file, or no reference was saved, but if a bad reference was saved, the code had to be isolated similar to what you found. I think I had mine in a separate class, but the principle is the same.

I think this "interpret only as much as necessary" is considered a feature of VBA. You can run a certain section of code, even if you have compile errors elsewhere. This can be useful when you only have a partially written functions or other half-finished stuff, or if you open the file on a computer without some referenced software installed. It lets at least some of the functionality still work.

Upvotes: 4

Related Questions