Early Binding v/s Late Binding in Excel VBA Programming

.

Early and Late Binding is a common phenomenon across computer programming languages. In simple terms, Binding means – how and when methods or properties of an Object are compiled and checked.

In the case of Excel VBA [COM – Component Object Model] – this occurs when you are trying to automate something which is not part of default object library of Excel (in this case).

For example:

If you are trying to access Outlook, Word, Internet Explorer, RegEx etc. using Excel VBA – you need to link those Object Libraries in your Excel to be able to use all the functions, methods, properties, etc. from that Object Library. It is similar to This Binding can be done in two ways in Excel VBA:-
1.Early Binding or Static Binding
2.Late Binding Or Dynamic Binding

What is an early binding in Excel VBA

As the name suggests, in early binding you add relevant reference before your program compiles. Early binding is done by adding the reference in Excel VBE screen itself. By doing this, all the methods, functions etc. are loaded.

Steps to add Reference in Excel VBA Screen

Step 1. Go to VB Editor Screen (Alt+F11)
Step 2. Tools –> References…

Add-Reference-Step-1

Add-Reference-Step-1


Step 3. You can see list of so many references – These are are libraries
Step 4. Select all your libraries which you want to use it in your program – For Example – PowerPoint

Step 5. Click OK

After clicking OK this object library is added in your VBE Project.
To check that, press F2 and on the Object Library Window of Excel VBA, you can see that PowerPoint Object Library is added there as shown in below picture.

Object Libraries in Excel VBA

Object Libraries in Excel VBA

This is where you can see all the classes, methods, properties etc. related to the Reference you added in Excel VBA.

That’s all. Now you can declare variables of all the types defined in that Object Library, use their corresponding methods, properties etc. You can see in the below image.

EarlyBinding-Intellisense

EarlyBinding-Intellisense

This is why it is called early binding. Object, methods, properties etc. are checked during compilation and not at run time.

Early binding is defined something like following:


Dim newPowerPoint As PowerPoint.Application
Set newPowerPoint = New PowerPoint.Application

OR

Dim newPowerPoint As New PowerPoint.Application

Advantages of Early Binding

Following are the main advantage about Early binding:
Better Performance: Early binding is considerably faster than late binding. Reason for better performance is that program is already compiled before running it.
VBE Intellisense: One of the major advantage of Early Binding is that VBE intellisense start displaying all the object, methods, properties etc. after pressing dot (.) like any default Object. Refer below image

EarlyBinding-Intellisense

EarlyBinding-Intellisense


This is version compatibility prone method of binding. If the version of the application is different in the computer where you are running the VBA application, then you will get a compilation error.

For example:

if the Object Library which you have referenced in your Excel File is XYZ-V-1.0 and shared it with your colleague who has next version of this object library V-2.0. In this case, program will not even compile because if you have reference V-1.0 which this file is missing in his/her computer.

More Object References – Bigger file size The more number of reference you add in your Excel VBA application, bigger the file size becomes and it takes longer time to compile.

This was all about Early binding.

What is Late binding in Excel VBA

Opposite to the early binding, in late binding, Objects are checked and compiled at run time. You do not have to reference the Object Library before you run the program. In late binding Objects are created run time and then method or property related statements are compiled and then executed.
If your typed method or property does not exist, then you would not get any error until you run the program unlike early binding.

This is how late binding created in VBA. First you need to define a variable of Object type and then using CreateObject(“Object Library Name”) method of VBA, you can create and instance of that Object.

Since Object Libraries are not referenced before, you can not use New keyword to instantiate the Object.


Sub Create_PowerPoint_Object
'Define one variable of Object Type to hold the Application Object
Dim objNewPowerPoint as Object
'Create an Object for PowerPoint Application
Set objNewPowerPoint = CreateObject("PowerPoint.Application")
End Sub

Advantages of Late Binding

Version Independent: Since you are not referencing a version specific Object Library and referring instance of that Object in your program, it will not through any error if shared to another computer with a different version.

Note: If the Method, properties or objects are changed in different version then it may fail even by using this late binding.

Faster compilation Since there is no Object Library files attached with the VBA project, file size remains as is and compilation becomes faster.

Faster compilation

Disadvantages of Late Binding

No Intellisense In this method you do not see intellisense any more. To use any of the method, property etc, you need to know the exact name of it otherwise you will see error at run time.
Compilation Error at run time All compilation error occurs at run time. You can not uncover such issue during compilation.
No Access to Object Model in Object Library In VBA project, you do not see Object Model in Object Library in excel VBA like Early Binding.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Regular Expression and its usage in Excel VBA - Welcome to LearnExcelMacro.com - […] use this built-in feature, you can use early binding you need to add reference in your VBA project –…
  2. Regular Expression and its usage in Excel VBA - […] use this built-in feature, you can use early binding you need to add reference in your VBA project –…

Submit a Comment

Your email address will not be published. Required fields are marked *

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

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest