18

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?

tbone
  • 5,715
  • 20
  • 87
  • 134

2 Answers2

27

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

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • 1
    Damn, your answer is what I was afraid was going to be the case. "...before you distribute your application, change the code to Latebinding." - much easier said than done!!!! :) Using an old type library would be preferable if a person did a lot of this, but for my case I'll just use Early Binding I guess. – tbone Feb 29 '12 at 00:53
  • 1
    @tbone: trust me. It is very easy to convert an Early Bind to a late bind :) – Siddharth Rout Feb 29 '12 at 06:44
  • 2
    @tbone while you cou\ld try to add references to different outlook versions during runtime, I would go with Sid's advice on this. +1 – brettdj Feb 29 '12 at 07:56
  • 2
    > "It is very easy to convert an Early Bind to a late bind" Agreed. Not so easy to convert 20 of them, especially if you have the same problem, but with multiple different object types (Outlook, Word, etc). Or is there a technique I am missing? – tbone Feb 29 '12 at 22:35
0

Use conditional compilation! ex:

#if Updating then
     ...late binding
#Else
     ...early binding
#End If
João Batista
  • 1
  • 1