Excel – Preventing Excel VBA compile errors due to users having an older version of MS Office (MS Outlook) installed

excelvba

If I have a spreadsheet where I've referenced MS Outlook 14.0 Object Library from the VBA editor, and a user who only has MS Outlook 12.0 installed, then when that user opens the spreadsheet, they get a compile error on this line:

Range("export_date") = Date - 1

If they go into Tools, References, in the references list, there is this error: 

MISSING: MS Outlook 14.0 Object Library

If they deselect that library, and instead select

MS Outlook 12.0 Object Library

…the code then properly compiles and the spreadsheet works fine for them.

I don't really understand why it fails on the Date() function, as that is VBA function, not an Outlook function. But even more important, is there a way to avoid this situation? The only thing I can think of is to not set references, and just use variables of type Object and instantiate via CreateObject("Outlook.Application"), etc, but I hate to give up strong typing, etc.

Can anyone suggest a superior way to handle this issue of backwards compatibility with older versions of MS Office?

Best Answer

tbone, what you refer to as Strong Typing is called "Early Binding".

Unfortunately one of the drawbacks with Early Binding is that if the end user doesn't have the same version as yours then you will get those errors.

If you ask me, I prefer Late Binding (where you don't create references and use CreateObject to create an instance)

An interesting read.

Topic: Using early binding and late binding in Automation

Link: http://support.microsoft.com/kb/245115

My Suggestion

Don't give up Early Binding if you like it because of intellisense. However before you distribute your application, change the code to Latebinding. There is not much of a difference in the way you code in Early Binding and Late Binding.

Here is an example

Early Binding

'~~> Set reference to Excel Object Library
Sub Sample()
    Dim oXLApp As Excel.Application
    Dim oXLBook As Excel.Workbook
    Dim oXLSheet As Excel.Worksheet

    '~~> Create a new instance of Excel
    Set oXLApp = New Excel.Application
    '~~> Add a new workbook
    Set oXLBook = oXLApp.Workbooks.Add
    Set oXLSheet = oXLBook.Worksheets(1)

    '
    '~~> Rest of the code
    '
End Sub

Late Binding

'~~> Doesn't require a reference to Excel Object Library
Sub Sample()
    Dim oXLApp As Object
    Dim oXLBook As Object
    Dim oXLSheet As Object

    '~~> Create a new instance of Excel
    Set oXLApp = CreateObject("Excel.Application")
    '~~> Add a new workbook
    Set oXLBook = oXLApp.Workbooks.Add
    Set oXLSheet = oXLBook.Worksheets(1)
    '
    '~~> Rest of the code
    '
End Sub

HTH

Sid

Related Topic