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
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.
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.
@@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.
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.
@@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.
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?
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.
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...
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.
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)
It's simple. It's clear. Perfect. Thanks a lot sir !
Hi Paul, you have taught this old dog a new trick.
Thanks a lot.
😎👍
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.
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
If I just have watched this video 2 months ago... I'd saved a lot of time
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.
Another great lesson and video! Thanks Paul! Thumbs up!!
Thanks again Wayne!
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
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?
You solved so many problems for me in 10 minutes 🎉
Glad it was useful
Oh Paul, that's a great solution!
Thanks
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.
Clever and smart! I like it very much!👍👍👍
Thanks Paul!😁💚🍀🤟😎
Brilliant
Thank you Mr. Paul for the nice video.
Why don't you create a complete VBA course on Udemy?
That would be amazing
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.
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.
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?
Let me know in the comments below, how you use binding in your code...
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.
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...
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.
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
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, RUclips 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.
Sir one problem
Excel to access late binding data save , update etc problem hai
Waiting for your response
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?