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…
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.
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.
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
Compatibility Error: 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.
0 Comments
Trackbacks/Pingbacks