5 Killer Excel VBA Tips Everyone Should Know

[@sdawilson]

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 KZbin.

[@Excelmacromastery]

Great to hear! Thanks Simon.

[@MGE0007]

....there is nothing embarrassing about seeking knowledge.....🤔

[@kairomalachi255]

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

[@Vandalfoe]

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.

[@realhuahin]

Thank you Paul for the generosity in sharing your experience!

[@Ganesh749]

Great as always.. Paul Thank you for sharing wonderful tips.. Indeed you are VBA guru!!

[@Excelmacromastery]

Thanks Ganesh

[@OmkarUmbre]

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.

[@OzScout66]

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 :)

[@Excelmacromastery]

You're welcome😀

[@tuyoexcelypowerbi]

Very useful. Best of last VBA tutorials I have watched lately!!

[@Excelmacromastery]

Thanks - Gla d you liked it.

[@free3690]

Thank you for awesome tips. I am going to try the F3 search!

[@mcallister7593]

This whole series is just brilliant and I am very grateful I found it. Bravo and thank you.

[@sindhusudhakaran1731]

very useful and not commonly known!! Thank you

[@joaocustodio2094]

Another great video. Thanks Paul.

[@Excelmacromastery]

Thanks Joao.

[@jacksonmacd]

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.

[@edge5817]

Thanks Paul for this wonderful tips... very useful indeed!

[@Excelmacromastery]

Glad you like it Edmundo

[@sandeepkothari5000]

Paul, these are really killer tips. What a way to usher in the new year! Have a happy one.

[@Excelmacromastery]

Thanks Sandeep

[@justintime5021]

# 3 was very useful. Thanks for that! I normally always googled that and copied the syntax as well

[@Planet_Xplorer]

I use 'stop' command for debugging. Very useful

[@rajeshkumargoyal6528]

Thank you. You are real VBA guru.

[@BenjaminHouot]

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.

[@JHatLpool]

Really good. Thanks. If only I knew all of this 5 years ago !

[@tuworlds]

another great video thank you

[@wayneedmondson1065]

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!

[@Excelmacromastery]

Thanks Wayne. Glad you liked it.

[@iincitr]

Thank you Paul. Tip #5 is very useful that I didn’t know. Would you pls make a video about Excel interoop operations.

[@JohnOvens]

Hi Paul, this is a fantastic video. You keep adding helpful hints based on your extensive experience. Thanks

[@Excelmacromastery]

Thanks John. Hope you keeping well.

[@scottpinkham330]

Great tips - thank you for this

[@juliogadiolisoares3157]

🤔👏👍 very useful thank you

[@szisziszilvi]

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.

[@Excelmacromastery]

They are Conditional Compilation Arguments. See my video on Debug.Assert for more information.

[@szisziszilvi]

@@Excelmacromastery I'll check for sure!

[@MrWarlls]

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 ?

[@Excelmacromastery]

You can have multiple compilation arguments. They are seperated by the colon symbol

[@walerij]

What does the # sign mean before the IF?

[@frikduplessis3869]

Compliments for the new year Paul, great tips as always

[@Excelmacromastery]

Thanks Frik

[@vbatipsandtricks5237]

Good way to share.

[@BertandRussell]

Hi sir, I am the first one to like n comment on this video. God bless you

[@Excelmacromastery]

Thanks for your support Abid

[@mike_case]

Great work again, especially #5 :) Thank you Paul your work is really helpful. I'm waiting for next tips ;)

[@Excelmacromastery]

Thanks. Glad you like it Michal

[@nelsonrioux5555]

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

[@aNDy-qh1em]

Hello, here is some explanation stackoverflow.com/questions/6325486/if-else-end-if-what-do-the-hash-signs-mean-in-vba C'est facile

[@Excelmacromastery]

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.

[@terrylavelle1139]

@@Excelmacromastery Love your work, Paul. Is it worth expanding on these in a future video?

[@songokussj4cz]

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

[@Excelmacromastery]

That's a good way to do it.

[@obsoquasi]

thank you very much for these useful tipps!

[@Excelmacromastery]

You are welcome John

[@aiooshhaw9640]

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

[@jmstampe]

That’s good stuff especially the compiling shortcut; something I find I have to do frequently in my line of work.

[@scotolivera8207]

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.🙏

[@Excelmacromastery]

Check this video out: kzbin.info/www/bejne/n5aVdaiZnpKkgbM

[@FizzyMcPhysics]

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.

[@Excelmacromastery]

Glad you liked it Benjamin.

[@warrenhall1750]

That was great.

[@RichardCookerly]

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!

[@feynmanwasagenius3482]

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

[@Excelmacromastery]

Glad y ou like it.

[@scotolivera8207]

Yes indeed

[@markwoodward7016]

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.

[@Excelmacromastery]

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.

[@nicor1501]

Tip#5 is indeed my favorite too :-)

[@Excelmacromastery]

Thanks Nico

[@eltee704]

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

[@Excelmacromastery]

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.

[@eltee704]

@@Excelmacromastery thanks for the reply ❤️

[@aNDy-qh1em]

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.

[@JithinDevan]

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.

[@thearchibaldtuttle]

Happy 2020!

[@Excelmacromastery]

Happy New Year Archibald

[@christianmuntean]

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.

[@GanovAlex]

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

[@lyonhard1]

Cool beans Paul

[@bogdanexit1]

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.

[@Excelmacromastery]

I'm sure you meant "Can you make a video?". It's not in my schedule for the near future but who knows:-)

[@bogdanexit1]

@@Excelmacromastery You are right (for can't). Thank you :)

[@christianhapke9384]

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.

[@bogdanexit1]

@@christianhapke9384 Thank you.

[@slbrick56]

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.

[@barrygeldham390]

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

[@ricos1497]

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?

[@Excelmacromastery]

It's more for a development environment. Test should match Live as much as possible.

[@ricos1497]

@@Excelmacromastery sorry, yes, that's what I meant.

[@jimfitch]

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.

[@psychedelarte7257]

Merci

[@Excelmacromastery]

Thank you.

[@kytan13]

This might be a stupid question, but what´s the shortcut to delete an entire row?

[@Excelmacromastery]

Ctrl + Y

[@kytan13]

@@Excelmacromastery thank you very much.

[@shakhobiddinnakiev6767]

#3 is tip?))

[@dgh25]

Nice but nothing new. I would like to know how you delete an entire line of code in one click though?

[@Excelmacromastery]

Ctrl + Y

[@dgh25]

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) 👍

[@rrrprogram8667]

If u wanna be 2nd in vba then follow this channel..... (1st is always paul)

[@JstnW]

I don't wanna kill anybody, but I'll try your tips.

[@Excelmacromastery]

Please don't (lol). Thanks Justin

[@JstnW]

Excel Macro Mastery lol. Ok I was over the top. Sorry

[@robertschrock1506]

U

[@dmytroYmedia]

VBA is a zombie language, so let's just don't use it. And help it to die.

[@houstonvanhoy7767]

I read this statement yesterday: "If VBA is dead, then it is the most productive corpse in the office."