List of Enumerated Constants for Late Bound VBA Automation

To automate one Office program from another, one of the things you must do is avoid the use of the built-in constants for the target program (if you are using late binding).

Each Office program that supports VBA has a set of built-in constants, with numeric values that represent some state or property. These constants are usually prefixed with the initials of the source program. i.e. 'xl' for Excel, 'ol' for Outlook, 'wd' for Word, and so on.

For example, one of the constants in Outlook is olMailItem, which represents an email message. The numeric value for this constant is 0 (zero). To use this constant, you would write something like the following in your code.

Dim App As Outlook.Application
Dim Msg As Outlook.MailItem
Set App = Outlook.Application
Set Msg = App.CreateItem(olMailItem)

If you use olMailItem in your automation code, and the code is not early bound, you will receive a compile error. Replace the constant with the number zero, however, and your late-bound code will work.

Of course, there is a way around this. Just declare the constants locally, and you can use them regardless of whether your code is early or late bound! Inspired by Late Bound Enumerations, I've created lists of the enumerated constants for Access, Word, PowerPoint and Outlook. If you need the list for Excel, visit the blog post I just mentioned.

I really like this approach. It allows you to change your code from early to late bound, and vice versa, without having to edit your code and toggle between your target program's built-in constants and their equivalent values. For example, you wouldn't need to change all references to olMailItem to 0 (the constant value) when changing your code from early to late-bound. I know you can just do a search and replace, but this makes it one less thing to remember. And writing early bound code, then converting it to late bound before distribution, is a smart idea.

Download the code

Download .bas files
Download .txt files

Site last updated: August 20, 2014

excel school learn dashboards