As a VBA Developer, once you start getting savvy with Excel and start doing some fairly complex coding, you will find yourself in a position, where you would have to communicate with applications like Word, PowerPoint (Client Applications), or some other software component, and control them through Excel (Host Application).
This interaction happens between applications using the Microsoft's Component Object Model (COM).
To be able to control or communicate with Client Applications, you will first need to be able to see the Client Application’s Object Model. The Object Model, basically has all the Classes of the Object (Methods, Properties & Events) which we are trying to communicate with.
To see Excel’s Objects Browser, just to get a feel of it, go to the Visual Basic Editor, and Press the F2 Key.
The Object Browser enables you to see a list of all the different objects with their methods, properties, events and constants.
What is Binding?
When you refer to an Object in your code with an intention to access its Methods, Properties & Events, the process is called Binding.
There are 2 main types Binding which you will have to choose from, both having Advantages and Disadvantages over one another -
Let us take a look at them one at a time.
Early Binding –
Early Binding is when you manually set a reference to the required Object Library. This process of binding is done during Design Time (When the code is being designed), and hence the word “Early”.
To set a reference, on the Visual Basic Editor, go to the Tools Menu and then References. The window should look something like this, with some references already selected (Default References) and some available for selection.
Microsoft claims that Early Bound codes are atleast twice as fast as late bound codes. This speed comes, as Excel knows exactly what it is dealing with before the code actually starts executing, and hence it accumulates all the resources needed to run the code in the most efficient manner. That being said, to my experience, when the complexity of your code is pretty average, and when in conjunction with good hardware, the time difference in execution of an Early Bound Code Vs a Late Bound Code is pretty minimal.
The one biggest advantage of an Early Bound code, is the help from Excel through the beautiful intellisense. Intellisense is the contextual drop down which you get while typing code with the related Methods, Properties, Enums etc. of the object being used.
This is of course available, only when the code is Early Bound, as it knows all of the Methods, Properties etc. of the Object beforehand, along with where to locate those functions in memory, and hence can be made available to you at Design Time.
To Early Bind a code, you set a reference to the required library, explicitly declare the object variable with a specific object type, and create a new instance using the New Keyword.
You can also do it like this using a single line instead of two, and omitting the SET Keyword.
This technique is called Auto Instancing. Even though it looks like a smart move, It does have some drawbacks (I won’t get into it right now), but If you would take my word, go for the explicit type of Instancing which I have shown above (With the SET Keyword).
One disadvantage of Early Binding is Portability, probably the biggest one of using Early Binding. Assume you have written a pretty neat code which you are really really proud of, and you hand over the workbook to your boss or distribute it amongst your colleagues hoping for some real appreciation, but all you see is a frown on their faces, when they excitingly run the code, which instead of returning some meaningful data, returns an error “Can’t find project or library”. Pretty embarrassing huh?
This happens so many times, especially when the workbook is being used on different versions of Excel. The Reference which you had set while distributing the workbook, might not be available on your colleagues computer as he might be using an older version of excel, which very easily happens, and that’s exactly when that Error occurs.
We have spoken enough about Early Binding. Let’s now take a look at Late Binding.
Late Binding –
Based on what we have seen so far, it may sound as if Early Binding is a better choice in terms of everything, which is almost true, but there is 1 trump card which Late Binding holds, its ability to execute the code without explicitly referencing the type of Object Library which it will use. This my friend, is the biggest advantage of all.
While distributing your workbooks to a bunch of people, you won’t have to sweat, hoping your code won’t spit out one of those ugly errors.
While using Late Binding, you will have to declare your Variables with a Generic Object Data Type, and instantiate it using the CreateObject Function. Late binding always compiles the code during Run Time and hence the word “Late”.
Another little drawback while using Late Binding is that, you will have to use Constant values instead of Enumerations.
Instead of this
You will have to use this
To find out the constant value of any Enumeration, you can go to the Object Browser by pressing F2 on the Visual Basic Editor, and then by selecting the appropriate library, class and the required member of the class. At the bottom of the image, you will be able to see the constant value.
So which one should you choose?
There is no right or wrong, it’s what suits you best, and what doesn’t. What I normally do is start with Early Binding, make use of the Intellisense while writing the code, and once I am done, I change it to a Late Bound Code, by declaring the variable as a generic Object Type, Instantiating it with the CreateObject Function and changing all the enumerations to its constant values. That’s how I get best of both worlds.
Thanks for reading. :)
Office, Excel, Microsoft, Microsoft 2010, Excel 2013