It's embarrassing. I've been coding in VBA for 20+ years, mostly in Access. I learn new things every time I watch one of Paul's videos. Thoughtful, structured, articulate and clearly demonstrated... the best on UA-cam.
You all probably dont care at all but does anybody know of a tool to get back into an instagram account? I was stupid lost my account password. I would love any assistance you can give me
Great video as always. One thing for viewers of tip #5 to note is that MyMsgBox, as you've posted it, has a hardcoded message. However, it was written to accept any string for message text, by replacing the "... Option B..." hardcoded text with the variable 'prompt', which I see is the first (and only required) parameter to MyMsgBox.
I am working on VBA coding for more than 11 years. But when I watch your videos, I think there are lots of things which still I need to learn and use them. Great videos. Thanks.
I've always been aware of conditional compilation, but never pulled the trigger to try using it. Tip#5 gives me inspiration. Thanks. I really appreciate your clear and concise teaching methods. Btw, perhaps a separate video all about conditional compilation could be interesting.
Mate, I've learnt more xl vba tips and tricks from you in the past month, than I have in the past 10 years. You are truly a "VBA Master" my friend - Thanks & Cheers from your friends Down Under in Oz :)
Hi Paul.. great tips.. thanks for starting off 2020 with this useful video. Looking forward to more VBA fun with you and Excel Macro Mastery in 2020. Happy New Year.. and Thumbs up!
Thank you Paul for this video... Could you make a video on how to export/save embedded objects from an Excel file? I've tried changing the Excel extension to .zip but it did not work.
As usual great content and very useful, if there is anyone can explain and give a practical example of how classes work in real life and in which circumstances could be useful that person would be you . So i ll really appreciate if you could take time and make a video about this topic.🙏
Hi Paul, I'm really loving your videos. Does the Excel VBA Handbook Course cover everything you present? I would love to have all this information in one place.
Hi Mark. The purpose of the Excel VBA Handbook course is to teache how to build Excel VBA applications from scratch. So it does include many tips but it doesn't include everything on this channel.
Amazing video as always! It’s pretty incredible that I still learn something new with each video even though I’ve been working with VBA for 8 years. Keep up the great work!
Hi Pual , thanks for your videos its really helpful for us, I wanna to ask you about some problems that i faced when i used VBA, how I can contact with you > thanx
Hi Paul, i have got a big app >15MB with dozens of modules, forms, class modules. I have applied all the options you proposed to reduce it. Still it is too big. Is there any way to upload modules dynamically? - this definitely should help. Thanks.
Wow! Some wild stuff buried in the settings. I'll have to remember to press Ctrl F3, instead of Ctrl F Haha. I've recently devised a different way to handle breaking and msgbox suppression using a DebugMode Global variable which I can toggle on and off with a button in the worksheet, or by setting it in the Immediate window. For me, this has the advantage of being built into the code that I import, so I don't have to set up the work workbook the way you did.
For those Conditional Compilation Arguments, I'm doing EarlyBinding/LateBunding checks for the case end user does not have a reference to for example Microsoft.Scripting for Dictionary. Example: Production Arguments: none. Debugging arguments: 'EarlyBinding=1' #If EarlyBinding Then Dim myDict as Scripting.Dictionary Set myDict = New Scripting.Dictionary #Else Dim myDict as Object Set MyDict = CreateObject("Scripting.Dictionary") #End If Now when EarlyBinding=1, I can have myDict. hinting. But when I delete EarlyBinding, it goes to LateBinding which works the same (little bit slower) but User doesn't have to have those references problems
I need a answer to this. Is it basically possible to somehow connect excel to a gameserver. I mean so the data exchange would be really fast. If this is possible then online Excel games should not be a problem anymore.
These #if statements are used by the preprocessor to check if code is to be used or not. Vba checks the code before it runs. If the condition is false then the code is ignored. Even if the code has an error it won't matter because the code is ignored.
thanks a lot, your channel is on top of my videos, F3 and goto new Paul's video :-) You're using MZ-tool, that's great one, for me i can't imagine now how to work without it, very comfortable and useful add-on
Hmm the last one is interesting, what I normally do is import a module called testmsg and call that while I am testing the code. The I remove the module at the end and remove or silence the calls to the module. Interesting different method
thanks Paul, do you host your repository anywhere? i'd love to be able to go through the modules for best practices and have some easily set up macros that i may not have thought of
Hi Lee, I don't host at this time but I do have a 50+ Excel VBA templates. I normally give these away as a special bonus with the Excel VBA Handbook course during live webinars. There will be a live one in the next week or two.
Just a quick comment ... during the explanation of Tip 4 (turning off error handling) the final display shows that the error stops within a loop that starts with the index value i=2. The error catches when i=3. The narrator indicates that the error occurred on the 3rd iteration thru the loop. But that is not correct. Since the loop began with i=2 and the error occurred when i=3, this is the 2nd iteration of the loop. A very small error in a great instructional video.
Hi, i see there are #s in the code at some points, like somewhere at 9:48 before some commands in this video. What are they? I've never seen them and google won't find it for me.
Hello Paul. You can't make a video in the future to share a opinion for the fastest way tu sumifS not sumif? . I have to sumifs by 5 condition. From sheets that have 200000 - 500000 rows And my last time the code takes 2 hour to run. I search over the internet and the maxim I found is Sumif not sumifS Thanks.
Read the sheet data into an array. Loop threw array and if all x conditions are fulfilled, you add the value you want to sum into a dictionary. This should take a couple of seconds.
The last one I didn't know about. I'm trying desperately to think of a situation where I'd use it. I suppose it could be used when moving a workbook from live to test environment or something?
Another possible use of Tip 5 could be this: I write procedures & functions with intent to display message boxes in some operational settings, but not others. For example, a procedure executes commands & displays a MsgBox that announces completion to the user. In a different application, that procedure is one of several called by a higher level procedure, so I don’t display the MsgBox at the end of each called procedure (because that interrupts execution until the user dismisses the MsgBox), but I display the MsgBox when the higher level procedure completes. The way I have handled that is to write the lower/called procedure with a parameter that controls display of the completion MsgBox. When executing as standalone, the argument fed to the parameter displays the MsgBox. When called from a higher procedure, the calling procedure supplies the argument that suppresses the MsgBox. It works, but has always felt a bit clunky & requires careful attention to detail. Perhaps Tip 5 would be an easier/better way to control this.
It's embarrassing. I've been coding in VBA for 20+ years, mostly in Access. I learn new things every time I watch one of Paul's videos. Thoughtful, structured, articulate and clearly demonstrated... the best on UA-cam.
Great to hear! Thanks Simon.
....there is nothing embarrassing about seeking knowledge.....🤔
You all probably dont care at all but does anybody know of a tool to get back into an instagram account?
I was stupid lost my account password. I would love any assistance you can give me
Thank you Paul for the generosity in sharing your experience!
This whole series is just brilliant and I am very grateful I found it. Bravo and thank you.
Great video as always. One thing for viewers of tip #5 to note is that MyMsgBox, as you've posted it, has a hardcoded message. However, it was written to accept any string for message text, by replacing the "... Option B..." hardcoded text with the variable 'prompt', which I see is the first (and only required) parameter to MyMsgBox.
That’s good stuff especially the compiling shortcut; something I find I have to do frequently in my line of work.
I am working on VBA coding for more than 11 years. But when I watch your videos, I think there are lots of things which still I need to learn and use them. Great videos. Thanks.
I've always been aware of conditional compilation, but never pulled the trigger to try using it. Tip#5 gives me inspiration. Thanks. I really appreciate your clear and concise teaching methods.
Btw, perhaps a separate video all about conditional compilation could be interesting.
Thanks Paul for this wonderful tips... very useful indeed!
Glad you like it Edmundo
Great as always.. Paul
Thank you for sharing wonderful tips.. Indeed you are VBA guru!!
Thanks Ganesh
Thank you for awesome tips. I am going to try the F3 search!
Mate, I've learnt more xl vba tips and tricks from you in the past month, than I have in the past 10 years. You are truly a "VBA Master" my friend - Thanks & Cheers from your friends Down Under in Oz :)
You're welcome😀
Really good. Thanks. If only I knew all of this 5 years ago !
Hi Paul, this is a fantastic video. You keep adding helpful hints based on your extensive experience. Thanks
Thanks John. Hope you keeping well.
# 3 was very useful. Thanks for that! I normally always googled that and copied the syntax as well
Great tips - thank you for this
very useful and not commonly known!! Thank you
Compliments for the new year Paul, great tips as always
Thanks Frik
Paul, these are really killer tips. What a way to usher in the new year! Have a happy one.
Thanks Sandeep
Hi, Good tips. thanks for sharing.
I use Ctrl+Tab to switch between workbook.
You could also use Shift to loop in the reverse order.
Thank you. You are real VBA guru.
Great work again, especially #5 :) Thank you Paul your work is really helpful. I'm waiting for next tips ;)
Thanks. Glad you like it Michal
Thank you Paul. Tip #5 is very useful that I didn’t know. Would you pls make a video about Excel interoop operations.
Another great video. Thanks Paul.
Thanks Joao.
another great video thank you
Hi Paul.. great tips.. thanks for starting off 2020 with this useful video. Looking forward to more VBA fun with you and Excel Macro Mastery in 2020. Happy New Year.. and Thumbs up!
Thanks Wayne. Glad you liked it.
thank you very much for these useful tipps!
You are welcome John
That was great.
Very useful. Best of last VBA tutorials I have watched lately!!
Thanks - Gla d you liked it.
🤔👏👍 very useful thank you
Good way to share.
I use 'stop' command for debugging. Very useful
Tip#5 is indeed my favorite too :-)
Thanks Nico
Thank you Paul for this video...
Could you make a video on how to export/save embedded objects from an Excel file? I've tried changing the Excel extension to .zip but it did not work.
As usual great content and very useful, if there is anyone can explain and give a practical example of how classes work in real life and in which circumstances could be useful that person would be you . So i ll really appreciate if you could take time and make a video about this topic.🙏
Check this video out: ua-cam.com/video/ie2Duci-qKQ/v-deo.html
Hi Paul, I'm really loving your videos. Does the Excel VBA Handbook Course cover everything you present? I would love to have all this information in one place.
Hi Mark.
The purpose of the Excel VBA Handbook course is to teache how to build Excel VBA applications from scratch. So it does include many tips but it doesn't include everything on this channel.
Hi sir,
I am the first one to like n comment on this video.
God bless you
Thanks for your support Abid
Amazing video as always! It’s pretty incredible that I still learn something new with each video even though I’ve been working with VBA for 8 years. Keep up the great work!
What does the # sign mean before the IF?
Happy 2020!
Happy New Year Archibald
Hi Pual , thanks for your videos its really helpful for us, I wanna to ask you about some problems that i faced when i used VBA, how I can contact with you > thanx
Merci
Thank you.
Cool beans Paul
Hi Paul, i have got a big app >15MB with dozens of modules, forms, class modules. I have applied all the options you proposed to reduce it. Still it is too big. Is there any way to upload modules dynamically? - this definitely should help. Thanks.
Wow! Some wild stuff buried in the settings. I'll have to remember to press Ctrl F3, instead of Ctrl F Haha.
I've recently devised a different way to handle breaking and msgbox suppression using a DebugMode Global variable which I can toggle on and off with a button in the worksheet, or by setting it in the Immediate window. For me, this has the advantage of being built into the code that I import, so I don't have to set up the work workbook the way you did.
Glad you liked it Benjamin.
For those Conditional Compilation Arguments, I'm doing EarlyBinding/LateBunding checks for the case end user does not have a reference to for example Microsoft.Scripting for Dictionary. Example: Production Arguments: none. Debugging arguments: 'EarlyBinding=1'
#If EarlyBinding Then
Dim myDict as Scripting.Dictionary
Set myDict = New Scripting.Dictionary
#Else
Dim myDict as Object
Set MyDict = CreateObject("Scripting.Dictionary")
#End If
Now when EarlyBinding=1, I can have myDict. hinting. But when I delete EarlyBinding, it goes to LateBinding which works the same (little bit slower) but User doesn't have to have those references problems
That's a good way to do it.
I need a answer to this. Is it basically possible to somehow connect excel to a gameserver. I mean so the data exchange would be really fast. If this is possible then online Excel games should not be a problem anymore.
Thank you for the tips and for letting me discover "Events". By the way I don't know why you have put an "#" before IF and END IF
Hello, here is some explanation stackoverflow.com/questions/6325486/if-else-end-if-what-do-the-hash-signs-mean-in-vba
C'est facile
These #if statements are used by the preprocessor to check if code is to be used or not. Vba checks the code before it runs. If the condition is false then the code is ignored. Even if the code has an error it won't matter because the code is ignored.
@@Excelmacromastery Love your work, Paul. Is it worth expanding on these in a future video?
I didn't know about searching with F3 and a quick test shows that I can use it in reverse to search backwards using Shift-F3
thanks a lot, your channel is on top of my videos, F3 and goto new Paul's video :-) You're using MZ-tool, that's great one, for me i can't imagine now how to work without it, very comfortable and useful add-on
Hmm the last one is interesting, what I normally do is import a module called testmsg and call that while I am testing the code. The I remove the module at the end and remove or silence the calls to the module. Interesting different method
Glad y ou like it.
Yes indeed
I didn't know the tip 5. I used a global constant to do the same think. But your method is clearly better. Is it possible to set several parameters ?
You can have multiple compilation arguments. They are seperated by the colon symbol
thanks Paul, do you host your repository anywhere? i'd love to be able to go through the modules for best practices and have some easily set up macros that i may not have thought of
Hi Lee, I don't host at this time but I do have a 50+ Excel VBA templates. I normally give these away as a special bonus with the Excel VBA Handbook course during live webinars. There will be a live one in the next week or two.
@@Excelmacromastery thanks for the reply ❤️
Just a quick comment ... during the explanation of Tip 4 (turning off error handling) the final display shows that the error stops within a loop that starts with the index value i=2. The error catches when i=3. The narrator indicates that the error occurred on the 3rd iteration thru the loop. But that is not correct. Since the loop began with i=2 and the error occurred when i=3, this is the 2nd iteration of the loop. A very small error in a great instructional video.
#3 is tip?))
Hi, i see there are #s in the code at some points, like somewhere at 9:48 before some commands in this video. What are they? I've never seen them and google won't find it for me.
They are Conditional Compilation Arguments. See my video on Debug.Assert for more information.
@@Excelmacromastery I'll check for sure!
Hello Paul. You can't make a video in the future to share a opinion for the fastest way tu sumifS not sumif? . I have to sumifs by 5 condition. From sheets that have 200000 - 500000 rows And my last time the code takes 2 hour to run. I search over the internet and the maxim I found is Sumif not sumifS Thanks.
I'm sure you meant "Can you make a video?". It's not in my schedule for the near future but who knows:-)
@@Excelmacromastery You are right (for can't). Thank you :)
Read the sheet data into an array. Loop threw array and if all x conditions are fulfilled, you add the value you want to sum into a dictionary. This should take a couple of seconds.
@@christianhapke9384 Thank you.
I don't wanna kill anybody, but I'll try your tips.
Please don't (lol). Thanks Justin
Excel Macro Mastery lol. Ok I was over the top. Sorry
If u wanna be 2nd in vba then follow this channel..... (1st is always paul)
The last one I didn't know about. I'm trying desperately to think of a situation where I'd use it. I suppose it could be used when moving a workbook from live to test environment or something?
It's more for a development environment. Test should match Live as much as possible.
@@Excelmacromastery sorry, yes, that's what I meant.
Another possible use of Tip 5 could be this: I write procedures & functions with intent to display message boxes in some operational settings, but not others. For example, a procedure executes commands & displays a MsgBox that announces completion to the user. In a different application, that procedure is one of several called by a higher level procedure, so I don’t display the MsgBox at the end of each called procedure (because that interrupts execution until the user dismisses the MsgBox), but I display the MsgBox when the higher level procedure completes. The way I have handled that is to write the lower/called procedure with a parameter that controls display of the completion MsgBox. When executing as standalone, the argument fed to the parameter displays the MsgBox. When called from a higher procedure, the calling procedure supplies the argument that suppresses the MsgBox. It works, but has always felt a bit clunky & requires careful attention to detail. Perhaps Tip 5 would be an easier/better way to control this.
U
This might be a stupid question, but what´s the shortcut to delete an entire row?
Ctrl + Y
@@Excelmacromastery thank you very much.
Nice but nothing new. I would like to know how you delete an entire line of code in one click though?
Ctrl + Y
Thx! (And OMG! Thats why my code disappears when I use CTRL + Z and CTRL + Y for undo and undo undo - like in Office apps) 👍
VBA is a zombie language, so let's just don't use it. And help it to die.
I read this statement yesterday: "If VBA is dead, then it is the most productive corpse in the office."