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.

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

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.

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.

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

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

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

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

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.

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

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

Very insightful video 📹 Thank u for your hard work

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

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

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.

Hi Paul. Your teaching is nice. I learn some new things on watching each of your videos.

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

Ive learned so much with your vids.... Thanks!

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

Nice Video. Thank you.

The Split() approach is good. Extremely useful for doing what the name suggests, not just for debugging but also for merging and separating multiple data in a return value string, when you cannot be bothered doing something nicer. The Parent thing is also very nice to keep in mind. It could easily happen that all you have is a worksheet refeference, but actually you need to save the workbook. You could overhaul stuff and make that reference available or just go with Worksheet.Parent.Save. Also very nifty hack inside the hack to adjust the expression in the watch window. CurrentRegion is dangerous unless you know that your data will not contain empty rows or columns. Do use it if you can be certain of that but otherwise my feeling is that it has the potential to be one of those PITA sources of errors that is just not particularly obvious unless you're actively looking for it. Finding end points the traditional way seems safer to me. Using Excel tables is nice, if that is how the data is formatted, but keep in mind that Excel table columns cannot have the same name. And I have seen many, many data ranges in Excel that would then suddenly get renamed if you just force it to be an Excel table, so I prefer simply writing a bit more code over using Excel table specific code. The next hack, the array thing, is in my view not optimally described. The hack is not simply that data is read faster from arrays than from ranges or cells in the workbook. This part is trivial and fairly obvious. Reading from an array does not require any interfacing between VBA and the Excel spreadsheet layer. Reading from specific cells obviously must require that. The real hack here is in loading the entire data range into an array in a single read, which is such a monumental time saver when working data. You can do it with the CurrentRegion or just by using Range(cells(1,1), cells(eRow, eCol)), and now any single cell access will happen at array speed instead of spreadsheet speed. Lastly, the "halt on all errors" approach to stopping error handling from running away from the error. That is a good tool to remember, but of course the example code used was a bit on the rubbish side. One should aspire to write code that doesn't crash and burn just just because a data range had a string where a numerical value was expected, and if crashing is the right thing to do then a proper crash message should be composed. In a perfect world, there would even be enough time for that. One last thing to comment, please do not use these very relaxed and casual variable names that Paul is using. Just calling an array "arr" and declaring it as standard variant is like naming your kid "person". It is not a big deal in small examples but if you have 30+ variables with all kinds of junk in them then having half-decent naming of variables becomes nice. It is a bit more typing, but if you ever run into a legacy project with a few thousand lines of code then you'll hero-worship the predecessor that took that took the time to do that. Conversely, imagine having that legacy project but everything is a mix of a, b, c, aa, ab, ba, and so forth? And everything is just variant all over the place.

Thanks for everything.

Great tips, super useful. I will use this for sure !

تمثل اللغة تحدي جدي بالنسبة لي لكي اتابع فيديوهاتك ، فانا ابذل جهدا مضاعفا لكي احصل على الفائدة القصوى ، و مع ذلك استمر في متابعة منشوراتك لاني اعلم انك الافضل . شكرا بول .

Thank you for this great video. Well explained and useful

Thanks, this is was very useful

Very nice video sir ! Thank you

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.

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.

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

Excellent tips!

Nice video. I regularly follow your video. Your teaching is so excellent. Please make a video- Excel data send through what's app. Data may be jpg, pdf, text accordingly.

Amazing content, thank you!

Very interesting ton link table and VBA. 🙂

As always, amazingly well explained! Edit: I'll use your Code but I need to understand the Magie, accordingly I'll write it by myself! Thanks alt sir!😉🤟😎

I do a lot of work with HMI displays that utilize DHTML and scripting, the split function is invaluable for building strings that can be passed to the execute command or eval function.

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

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.

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

Thanks

Great video. We want more hacks! :D

Awesome sir, thanks for knowledge update

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

I already knew these.

Genious! That is so helpful

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

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.

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

Thank you for another fantastic video, I didn't know you could do that with the watch window! With the code Sheet1.range"A1:C1").value = split(s,":") (11:19) does this need the array to work or is the array element of this redundant?

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 😁

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.

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

SIR Paul!!!

Hi sir,, how to implement mouse click and drag during running simulation using vba?

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?

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.

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

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

Congratulations and many thanks. I am looking forward for your new video.

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

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

Hi! Error 50290 worked around? I can recreate the error by opening a drop down list and while it is open, click on a macro that takes me to a different sheet like shData.Select shData.Range("A1").Select Works unless dropdown list in cell is open. 50290 Error occurs. Any workarounds?

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.

array is much faster than range.

Your videos are always great, however you should consider ditching this Windows 98 mouse you use and take a more silent one. Yout keys are silent but the mouse click is pretty annoying...

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.

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

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.

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