How to use Early and Late Binding the right way!
Вставка
- Опубліковано 25 лип 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: bit.ly/3etPly2
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
VBA Early vs Late Binding(and how to easily switch between them)
In this video I cover how to use binding. We look at the two types of binding with code examples of each.
Then we look at the pros of each. We so how to use compiler parameters to switch between. Finally we see an elegant solution that allows you to easily switch between each method.
#ExcelVBABinding #VBALateBinding #VBAEarlyBinding
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
Table of Contents:
00:00 - Introduction
00:28 - What is binding?
00:49 - Examples of binding
02:41 - Late Binding vs Early Binding
03:34 - When to use each type
03:57 - Binding rule of thumb
04:08 - Using a Basic If Statement
05:03 - How to switch between
06:31 - Possible solutions
07:39 - My simple method
08:29 - Class Modules - Наука та технологія
If I just have watched this video 2 months ago... I'd saved a lot of time
This was a better explanation than several webpages I reviewed. THANK YOU.
Excellent! Oh, I wish I knew this a long time ago. Proof that it’s never too later to learn. Thanks, Paul
You're welcome Jim.
Thanks for your sharing. Now I understand more deeply about early & late binding.
Clever and smart! I like it very much!👍👍👍
Thanks Paul!😁💚🍀🤟😎
Another great lesson and video! Thanks Paul! Thumbs up!!
Thanks again Wayne!
Wow , excellent , I have been waiting this video for a long time , you have changed my approach to any VBA project I will work in
And this is why I always use late binding. The problems from users having different versions of the DLLs are just not worth the pros of early binding. Also, as we get more experienced, we can live well without it.
Great explanation of the pros/cons and possible workarounds to still use early binding.
Very true - with more experience there is less of a need for early binding.
How can data save excel to access late binding please share any file for better knowledge
Oh Paul, that's a great solution!
Thanks
Brilliant
You solved so many problems for me in 10 minutes 🎉
Glad it was useful
This is absolute gold. Many thanks!
Glad you like it
@@Excelmacromastery I was switching between early and late but not using the #. That's really useful. Apparently early binding gives a considerable performance increase over late, but I've not tested that. Early is especially useful for automation using other applications, even outside MS Office, such as MapInfo, SPSS, etc. but as you rightly point out, versioning is a problem. This happens during big upgrades when people start to get a new version of MS Office. Roll-outs can be very gradual. Some would say glacial.
I've read the performance is better with early but I haven't tested it either.
I've dabbled with early and late binding in a large MS Access application, but always found it cumbersome to switch between the two methods. Your solution with conditional compilation is a game changer. Thanks very much
You're welcome Jack.
Really brilliant approach for late and early binding!
Just a comment regarding the scripting runtime library: As this library has not changed for ages I think it does not do any harm if you always use early binding in this case.
Another comment: The library you use has to be installed on the target system regardless of one uses early or late binding.
Hi, Do you know if there is a way to avoid excel showing the error message when the library is not installed?
Ha! Just last week I was trying to debug some old access code and couldn't figure our what was up with a DLL error. This video would have been so convenient.
Thank you Mr. Paul for the nice video.
Why don't you create a complete VBA course on Udemy?
That would be amazing
If we don't want to implement a way to change to late binding before providing the .xlsm to another person, what exactly is the restriction to communicate to the other person so they don't have a DLL issue?
In suppose throughout any code, we need to check if the another application is open or closed how can we do that (not using API and I am using sendkeys). I don't want to set everywhere if else statements...
Masterfull as always. I needed this video 5 years ago. I always used late binding and haven’t intellisense really slow you down. Will the code still working with the external library enums? I. E. acFormatXLS12 ? I usually have to convert them to their actual value, I. E. Instead of acFormatXLS12, I had to use 8 for example when I use late binding.
Glad it was helpful! For Late Binding you need to convert them because you don't have access to the library before runtime.
Let me know in the comments below, how you use binding in your code...
I always use late binding. No exceptions. My VBA code is always usable.
IntelliSense can be useful for development if you're not familiar with the library
What if the library you use is not installed on the target system for whatever reason?
@@guwu4091 exactly. If the dev is accessing functions from a later version and that version is not on the target system it aint going to matter which method you use. Hence the reason for standard operating environment and good dev ops practices.
Tempts me to come our of retirement and get back into the game....not really. A really useful video as per usual Cheers
Thanks Peter
Sir one problem
Excel to access late binding data save , update etc problem hai
Waiting for your response
Hey Paul, Big fan of your work. Just a small feedback, your download link is not working for me and this is not first video. it's happening on all of your videos. I am using Edge.
Thank you very much
I am having one regarding protection of code how to strongly protect vba project with out cracking the password is there any
There is third party software available or you need to use a different language like c# and vsto.
Somehow, UA-cam has screwed up video resolutions above 480p.
I don't see any problem with it?
My projects never get big enough for me to struggle with switching to late binding later on. Ofc, they are usually one and done type deals.
It's good when you can get away with it.
I think "Early" and "Late" are poor names for these two different ways of doing bindings. They should be called Compile and Runtime bindings. Just saying 😁
This is very frustrating... did anyone else get runtime error 451 ?
Sub Early()
' WORKS FINE
Dim myDictionary As Dictionary
Set myDictionary = New Dictionary
myDictionary.Add "Apple", 1
Debug.Print myDictionary.Count
Debug.Print myDictionary.Keys(0)
End Sub
Sub Late()
' RUNTIME ERROR 451
Dim myDictionary As Object
Set myDictionary = CreateObject("Scripting.Dictionary")
myDictionary.Add "Apple", 1
Debug.Print myDictionary.Count
Debug.Print myDictionary.Keys(0)
End Sub
I had to search around for the solution which is...
Debug.Print myDictionary.Keys(0) needs to be
Debug.Print myDictionary.Keys()(0) in the late bound version. (extra parenthess)
It appears that early bound code doesn't always translate into late bound code.
However, the extra parenthesis will work in the early bound version.
It's a subtle one. If you check this table you can see the differences between using early and late binding(excelmacromastery.com/vba-dictionary/#A_Quick_Guide_to_the_VBA_Dictionary)
@@Excelmacromastery Thanks for the reply, I see you did have it covered. I'm not sure what the reason for the empty parenthesis is though?