5 VBA Hacks Everyone Should Know in 2021

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.

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

Call me a dreamer, but one day I'm hoping that a worksheet will have a row zero for column headings. Preferably optional by worksheet. Maybe even negative numbered rows above that for the rubbish that idiot users put above actual data that should be in a header.

Final tip about turning error handling back on:
Can this be done within VBA?

Hi Excel Mastery :) I`m not very advance in VBA. Could you tell me how I can use your examples from Immediate window in normal worksheet ? thx
😁

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.

Thanks

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

Very insightful video 📹 Thank u for your hard work

Is it possible to multiply a row of numbers by the same number with VBA? So for instance if the constant mulplier is 7 and I enter 5 in the cell should compute 35 in the cell.

People say VBA is dead. But it will be here as long as there is Excel. Hahaha.

Btw, instead of doing loop, what's your thoughts on range.formula then insert excel formula using any worksheet.function? I used if to automate my vlookup function. Hahahaha

6. hack VBA = macro parameters
7. hack VBA = click and hide / view

Hi. In the last section you show error handling and debugging. How do you get the error handling to give the location of bad data?

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.

Thanks Paul. I just discovered you yesterday and Ive learned a lot already!
Yours is one of the very few tutorials that promotes using arrays and listobjects feature. Never seen arrays used in just a single line code before so I like your tutorials for that. Thanks again. Im beginning to update my Excel Apps on array features for faster execution.

You shouldn't be using Excel for data wrangling. Excel is an accounting tool and nothing more. Instead, you should be using R. If you are using Excel to analyze or process data, especially as a "hack", you are obsolete. All of the "hacks" you are describing in Excel using VBA can be done EXACTLY in R using 1 or 2 lines of code. Show me the "hack" on how to create a Mosaic Plot. Do you even know what that is? Probably not because Excel can't generate them. Meanwhile, they are some of the most compelling plots to use for extracting deeper insights from data.

For the first one, I know that split trick but I've resorted to RegEx for any kind of complicated string extraction. The watch stuff I knew and it's drag and drop capable which is awesome. I always use arrays when I can. I made someone's product 66 times faster just by having it do everything in arrays and putting it all in the cells after its completed. I've ran into issues with current region even though I don't recall exactly what happened. The last one I didn't know. Great tips as always!

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

The CONTENT of what you covered is Excellent, but THE SPEED in which you covered the material was UNREALISTIC for anyone to learn/understand. You both speak and move the mouse around @ 100 mph.

I liked the turn off error handling idea, but would it not be 'safer' to <Debug.print i> before the <next i>, the code will stop at i just befofre the issue row.

It's 2021, and people still enable macros.... Yikes.

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

Thanks, this is was very useful

I hate VBA. I've never understood it and I'm not even motivated to watch this video. Sorry.

Nice Video. Thank you.

array is much faster than range.

Hi Paul can you please make a video on to update pivot table when user changes particular cells. Example I want to create a p& l iin excel with pivot table. I want to update it based on the financial period i.e from and to date. Dont want to do that with slicers but want to do it with excel vba. Thank you

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

Very interesting ton link table and VBA. 🙂

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.

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

Hi Paul I have got a data in a serious of columns and row which change every time. What I want to achieve is I want to add each columns and if the result is different from zero then.
In short if you could help me with the code that can loop across the column and sum it. In an if and then
Many thanks

Harika yöntemler. Teşekkür ederim. İyi Çalışmalar..

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

For Listobjects, you don't have to reference the worksheet if you use:

Dim tbl As ListObject
Set tbl = [Table1].ListObject

Likewise, you can get the values of a column:


Dim vals As Variant
vals = [Table1].ListObject.ListColumns("Column1").DataBodyRange


Slightly more robust in case your table switches worksheets.

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.

Wow. I did a course in Udemy, slow-paced, and lost it now this is so better. Thanks a ton. Do you have a full course? Also, I hear that Microsoft is stopping VBA for good and switching to visualscript

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.

Just recently started using tables. They are amazing.

Hi Paul.. than you very much for all your lessons about vba excel. With all of those i can make my pos application much much more faster.

Hai Paul. Can you please explain how to fill a form in a website form using vba excel? Thank you

Amazing content, thank you!

Great hints! I was kinda lucky I knew them before, because our coach is awesome. But his availability is limited.
I just learned about the split from him last month and it made my jaw drop.
Speed of arrays is, I think, well known if you code a bit longer. But yeah, that was something I learned from him as well.

DataBodyRange I've learned last month by myself (digging).
I generally prefer to work on tables, because of power query and quality of life in general.

CurrentRange is useful, but I somehow end up using UsedRange, because it also resets the scroll bar.
It's useful in cases when you use same workbook during the month and with each day there's more and more data and then on the new month the sheet is barely filled. Without UsedRange the vertical scroll would be small like there was the data from whole month.
The minus, or sometimes advantage, of UsedRange is that it would grab the range of all of the data, even the data after empty column. So for example cannot be used when next to the table there are supporting cells, such as =TODAY() and =MAX(ItemsDateColumn) - to get today's date and report's date.
So both are useful in their own way.

PS Due to the fact you do very great job at explaining things I have few recommendations:
I am currently learning conversion from arrays to collections and from collections to arrays to print the results.
It's so not intuitive to me and I still mostly copy the code and rewrite it for my own purposes.
I could use some more hints. Especially adding new items to existing collections while in the loop.
Probably that's because I still don't understand "New" before variable type. Too much knowledge in short time :D
Eh... I've just checked your playlist... sorry. You have it all. So I better jump into it.

Also Enums and Type various use cases would be awesome, too.
I found that usage of Type makes code very clean in some cases - when you can group some parameters (strings, integers etc) into groups.
I cannot find many use cases for Enums though :( but it's also not intuitive for me as most things I would like to put in Enums are strings with spaces and slashes, and that doesn't work.

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

Paul, you really need to include some info on what references you are including (and those that cannot be included because of conflicts). Initially the code would not compile. Sorry if i missed it somewhere.

SIR Paul!!!

Great Video
A remark:
In the Use Range macro, you should save the last row of the range in a variable, and then put: For i = 2 to LastRow. I think it would be faster.

Greetings from Bolivia.

For the split I use

For each item in split(string, "~")
.......
Next Item