How to use late binding for cross version compatibility in excel VBA

Question

I've got this code in excel 2013 but it does not work in excel 2007. The main difference seems to be microsoft excel 12.0 object library in 2007 vs version 15.0 in excel 2013.

How would I go about making it compatible across all versions of excel? I've looked into what's called "late binding" but I'm not sure how to modify my code in this regard.

Any help is appreciated.

Here's the code:

Option Explicit

Sub ImportHistoricalDataSheet()

    Const SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS = 13056
    Const adSaveCreateOverWrite = 2

    Dim aBody, sPath

    ' Download Historical Data xls file via XHR
    With CreateObject("MSXML2.XMLHTTP")
    'With CreateObject("MSXML2.ServerXMLHTTP")
        '.SetOption 2, SXH_SERVER_CERT_IGNORE_ALL_SERVER_ERRORS
        .Open "GET", "http://www.housepriceindex.ca/Excel2.aspx?langue=EN&mail=abc%40abc.com"
        .Send
        ' Get binary response content
        aBody = .responseBody
        ' Retrieve filename from headers and concatenate full path
        sPath = ThisWorkbook.Path & "\" & Replace(Split(Split(.GetAllResponseHeaders, "filename=", 2)(1), vbCrLf, 2)(0), "/", "-")
    End With
    ' Save binary content to the xls file
    With CreateObject("ADODB.Stream")
        .Type = 1
        .Open
        .Write aBody
        .SaveToFile sPath, adSaveCreateOverWrite
        .Close
    End With
    ' Open saved workbook
    With Workbooks.Open(sPath, , True)
        ' Get 1st worksheet values to array
        aBody = .Worksheets(1).UsedRange.Value
        .Saved = True
        .Close
    End With
    ' Delete saved workbook file
    CreateObject("Scripting.FileSystemObject").DeleteFile sPath, True
    ' Insert array to target worksheet
    ThisWorkbook.Sheets("Sheet1").Cells(1, 1).Resize(UBound(aBody, 1), UBound(aBody, 2)).Value = aBody

End Sub

Answer 1

Your code already uses late binding with all the CreateObject(... statements.

But the library versions between the two machines (one running Excel 2007, one running Excel 2013) could be returning different types. You can check this with the following code:

Option Explicit

Sub TestXMLHTTPType()

    Dim objXhr As Object

    Set objXhr = CreateObject("MSXML2.XMLHTTP")

    'check what you got
    Debug.Print TypeName(objXhr)

    Set objXhr = Nothing

End Sub

Do you get a different output on the two machines? This information will allow you to code for methods and property statements that compatible between Excel versions.

Answer 2

So, how late binding works is, you declare the type as Object when you declare it.

Dim oSomething as Object

When you instantiate the object, you use a specific interface (IDispatch) dedicated for this binding, that looks up the functions at run time. This is why its called late binding. You use CreateObject to instantiate the object.

Set oSomeThing = CreateObject("SomeObject")

Early binding looks in the type library upfront so already knows the types.

Early binding will fail a compilation if some function doesn't exist - since it knows all the functions upfront. Late binding will pass a compilation but fail when it runs.

Now,

1. I would use early binding where possible. Then compile to see if you get any errors.
2. I would then open the older versions of Excel to and compile again to see if you get any compilation errors there.
3. If you get no errors, which you shouldn't, you need to run the application in debug to find the exact line the failure occurs and take it from there.