Can I use late binding to check the existence of a library before using it via early binding?

Question

I like to use early binding in my VBA projects, since I like the auto-complete of method names, etc. during development. I also like the confidence of knowing that the compiler will warn me if I've mis-spelled a method name.

However, to use early binding I need to add a reference to the relevant library (for example, the "Microsoft Scripting Runtime"). That's fine for "standard" libraries like that, but sometimes I want to use a library that may or may not be present on the user's machine.

Ideally, I'd like to display a useful message if the library is not present (such as "xyz is not installed on this computer, and so this feature cannot be used"). If I was using only late binding, then I could do this:

Dim o As Object
Set o = CreateObject("foo", "bar")

If o Is Nothing Then
    MsgBox "nope"
End If

But, if I've added a reference to the library in order to use early binding, then if the library is not present I get a compile error when my VBA project is loaded. Thus, none of the code runs (including the code to detect the non-existence of the library).

Is there any way around this catch-22?

Nepotism

I use early binding to take advantage of Intellisense, then convert to late binding to avoid version-specific code. – Isodimorphism

@JP: See my respose to mwolfe02's answer - I don't think this is a viable solution for me, unless you have another way to achieve it. – Nepotism

I put all my declarations together at the top of each procedure, and use CreateObject which works with late or early binding. It's a simple matter, then, to simply change all early bound declarations to late bound 'As Object'. But conditional compilation would also work. – Isodimorphism

Answer 1

Not really.

However, one way I've dealt with this in development is to have two separate declaration lines. I comment one or the other depending on whether I am doing dev work or releasing to production. You can leave everything else alone (including the CreateObject line) and then you just need to remember to switch the commented line and add/remove the reference itself.

For example:

Dim o As foo.bar   'Comment out for production'
'Dim o As Object    ''Comment out for dev work'
Set o = CreateObject("foo", "bar")

If o Is Nothing Then
    MsgBox "nope"
End If

Angkor

That's sort-of OK, except that my projects are quite big, so there would be literally hundreds of declarations. Every Excel.Range, every Word.Paragraph, etc. would have to have this dual declaration. – Nepotism

I suppose I could use a compiler constant and bracket it with #If LATE_BOUND Then ... which would make the switch between the two modes easier. But it would really clutter up the code, since every declaration would now take 5 lines. – Nepotism

... and it would mean having a different declaration for all my methods and functions, too. Yikes! – Nepotism

Answer 2

You could create a class module as a proxy for an object library --- wrap all the methods, properties, and constants needed by your other procedures.

All those procedures would use the proxy class the same way, so you wouldn't need to revise those procedures to switch between early and late binding. And Intellisense would show you everything you expose with the proxy class.

The class would be a single point of control to switch between early and late binding. You mentioned Excel as one example:

#Const DevStatus = "PROD"
#If DevStatus = "DEV" Then
    Private objApp As Excel.Application
    Private objBook As Excel.Workbook
    Private objSheet As Excel.Worksheet
#Else 'assume PROD
    Private objApp As Object
    Private objBook As Object
    Private objSheet As Object
#End If

If there is a possibility Excel may not be installed on any users' machines, you can check its availability during class initialize.

Dim blnExcelAvailable As Boolean

Private Sub Class_Initialize()
    blnExcelAvailable = IsExcelAvailable()
End Sub

Private Function IsExcelAvailable() As Boolean
    Dim blnReturn As Boolean
    Dim objTest As Object

On Error GoTo ErrorHandler

    Set objTest = CreateObject("Excel.Application")
    blnReturn = True

ExitHere:
    On Error GoTo 0
    Set objTest = Nothing
    IsExcelAvailable = blnReturn
    Exit Function

ErrorHandler:
    blnReturn = False
    GoTo ExitHere
End Function

Then your procedures which use the proxy class could check a property to see whether Excel is available.

Public Property Get ExcelAvailable() As Boolean
    ExcelAvailable = blnExcelAvailable
End Property

I think this approach is possible, and it satisfies your requirements AFAICT. However, I'm unsure whether it's reasonable. Back to the example of Excel, you could do something like this for a manageable subset of its object model. But if you need all or most of its methods, properties, and constants, the proxy class would be a huge undertaking.

Personally I wouldn't use this approach. It's less work for me to manage early/late binding as mwolfe02 and JP. described. However my impression is this is more burdensome in your situation, so perhaps you're willing to invest more effort than I am in something like this.

Zaremski

Nice idea. You're 100% correct that repeating all the objects, methods and (especially) constants would be an enormous task for something like Excel. However, it was silly of me to use Excel as an example, since I can assume the existence of Office - after all, this is VBA, and if the code is running at all, then the host application must be present. Your technique would be much more feasible for the sort of small libraries that I'm actually concerned with. Thanks! – Nepotism

+1 I really like this idea but it seems it would only work with predefined "Office" apps – Gadroon

@mehow I meant one VBA class to wrap one COM application, so the application would be defined in that class. (Is that what you meant by pre-defined?) And a different wrapper class for a different application. But the concept is not restricted to Office applications. Other COM-enabled applications should also work. – Zaremski

@Zaremski yes that's correct. (sorry, it's my probably my English). I have reread your answer now and yes I take back my last statement about the restriction. – Gadroon