Early binding in VBA

Early binding (dim x as Areferenced.ObjectType) is generally considered better than late binding (dim x as object; x= CreateObject(“AnUnreferenced.ObjectType”)

Here are the main reasons why:

  • you get intellisense (usually)
  • you get much compile time checking
  • its faster than late binding

(do you have any others?)

There is one down side which people often skip over:
if the correct version of the referenced component isn’t registered on the client pc, VBA will fail to compile let alone run, with the classic “Cannot find project or library” error.
How many times have you had to talk a user through opening the VBIDE and checking Refererences for #Missing ones?
This is the main reason we are advised to develop on the oldest version we intend to support. Most components seem to upgrade to a newer version fine, very few seem to down grade to work with an older version.

I wrote a reference checker workbook to help diagnose these problems.
On the dev machine you point it at the workbook/add-in that is not working right for the client.
It registers all the references that that wb/add-in uses. Save it and send to the users having problems.
They open it click the button save it and send it back. Now it shows details of the deve refs and the users refs and highlights any missing ones.
Its free and its here, feedback very welcome.

My next post will look at bit more closely at the performance issues.



One Response to “Early binding in VBA”

  1. Marcus Says:

    Hi Simon,

    There was a similar discussion on Joel on Software (JoS) some time ago.

    I tend to use early binding for the reasons you stated. However, one contributor stated that he always used late binding. However he starts using early binding to get the intelligence & compilation benefits. But prior to distribution changes a global variable to an Object type and references the early bound reference.

    His contention was that:
    – the performance hit was primarily on load-up, and
    – on modern PC’s, this hit should hardly be noticeable.

    Having developed solutions which worked across multiple editions of MOS I can appreciate the approach. Although I’d probably wrap the reference up in a class rather than having a global variable.

    I like the idea of the reference checker. Bovey et al also talk about a Loader in Pro Excel Dev. I also like the idea of anything that will minimise support calls and make the experience as transparent for users as possible, even if it does mean a little more overhead for me.

    Cheers – Marcus

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: