5 VBA Hacks Everyone Should Know in 2021

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

5 VBA Hacks Everyone Should Know in 2021

Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + C 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
Repeat the last search: 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:19 - Manipulating Text Hack
02:32 - Debug Properties Hack
04:30 - Get Range Hack
07:58 - Read Data Faster Hack
09:43 - Find Error Fast Hack

[Excelmacromastery]

Let me know in the comments if you plan to use these hacks in your own code. Make sure to download the source code from the link in the description.

[MudandoARotina]

Hey paul, i got a promotion by studiyng with your videos!! Thank you!!

[ricos]

I love the split function in VBA, I use it all the time, especially for parsing range addresses. A tip for others is that you can add a second set of brackets to return the split number, e.g. Split (s, ":")(1) to return the second item.

[mateuszbajko]

Great work as always! I must admit I didn't know about checking properites in the watch window, so big plus to you! I have been using these tips for some time yet, but I saw them for the first time on your channel long ago :) I agree they were gamechangers.

[serdip]

Great video, as always! Thanks very much for posting.

When using LBound(arr) and UBound(arr) in the For loop header, does VBA reevaluate those functions at each pass through the loop or does it optimize performance by evaluating them only once? Generally, when I am looping through an array and I know the lower and upper bounds, I store those in variables such as inLBound and intUBound and use those variables in the construction of the For loop. Thank youl

[mike_case]

Thank you Paul. Once again great video!!! :)

[seamushand]

I like how you use the watch window. I never thought of adding properties to it like the address query you added. Thanks

[Erebus]

ty.
these are always super helpfull when comming to a new syntax etc ^^

[dermotbalson]

Thank you for another valuable video.

I remember reading (rightly or wrongly) that Excel and VBA are in different memory processes, which creates a performance hit each time you go between them, so I always minimise traffic between VBA and the worksheet, reading all the sheet data that I need into arrays at the start, doing all the work in memory, then writing results back in as few statements as possible, ideally one figure or table.

The other performance hack I think everyone should know is dictionaries.

[granand]

So if I have used the current region, an array can I compare each element from sheet 1 to mapping column name ( Order of columns can be different) from sheet 2 to highlight mismatches both ways. I am using power query using multiple join methods as separate queries.

[choijaeho]

Wow, awesome video!!! Thank you so much for sharing these cool tips!!!

[tranyarelisher]

That error handling hack would have saved me some grief in the past for sure.

[ThorstenStrauch]

Whow! Once again: I learned a lot! The only thing I already knew about was the timing advantage of arrays over ranges. And I knew about list objects. But all other topics: well worth the watch! :-)

[JT-kyov]

What's that clsTimer you are using? Is it that one using the QueryPerformanceCounter and double integer values?

[wayneedmondson]

Hi Paul. As always, great tips! Thanks for sharing :)) Thumbs up!!

[Tattysnuc]

faster than a speeding bullet! Thank goodness for the pause & rewind buttons...

[MrSweck]

A lot of your videos have changed my coding to the better... both to the eye and to the runtime stability. I've been doing this for almost 20, years but somewhere the learning curve phased out and I started to "do with what I had", which meant long detours instead of direct functions, ex nested loops and Mid to find strings instead of simple Instr (and really dangerous Resume Nexts just to get to End)... not to mention 'time'. Your quote "What people often do when they start VBA because they don't know any better", from your 1000x faster video, made me laugh... because I knew you were talking about me. I haven't "just started", but there's certainly a lot I should know better after 15+ years. Your videos have given me much and I'm sure that your coming ones will.

[krn]

Thanks Paul. Love the watch window edit. I had no idea you could add .address to something while in the watch window.

[howdykeiths]

Thank you very much for your videos and instructions and tips. Not sure why, but the shortcuts to select regions, Ctrl * gives me a "Cannot show outline symbols" message. and the Ctrl Shift * gives me a "Printer Installer Client Console".

[kenc]

I didn't know about the watch and the error-break hacks. These are really helpful in debugging, which often takes longer than the coding itself. I hope you have a video or article that focuses on debugging tricks (not the textbook methods) to quickly nail down problems.