How to make your Excel VBA code run 1000 times faster
Вставка
- Опубліковано 27 лип 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
- FREE CHEAT SHEET: Get the free cheat sheet on VBA arrays here: bit.ly/2MXsnz9
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
In this video I am going to take an Excel Macro and make it run 1000x faster.
The code I'm using is a standard code you will see in the real-world. This code reads through 100,000 records and copies the rows which meet given criteria.
Starting with very basic VBA code I will keep improving the code until it runs 1000+ times faster than the original.
If you are creating Excel VBA code for real-world applications then you should take advantage of these proven techniques.
Excel VBA Copy - The Complete Guide to Copying Data: (excelmacromastery.com/excel-v...)
Shortcut Keys:
Alt + F11: Switch between Excel and the VBA Editor.
Ctrl + R: View the Project Properties Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent)
Shift + Tab: To move lines of code to the left(Outdent).
Table of Contents:
00:00 - Slide 1
Paul, you are on fire of late! Some EXTREMELY useful techniques explained very well. I have always been able to tell the level of a developer by what stage they are at in these types of examples. I will be recommending this video to lots of people.
Hi Wyatt,
That's actually a great point you make. It's like the evolution of a VBA developer. The begin using Select and activate and evolve through each stage from there.
@@Excelmacromastery hi, actualy I am looking your other video that explains it .. thanks
Please provide your inputs
Check this too
ua-cam.com/users/ExcelAutomationByMudit
that pretty funny, i'm learning vba for the last 3 month, and this is precisely the steps i was taking it. Although i've stopped at the array level, i know that i'm miles away, too crude in the knowledge about the rest.
This advanced filter, to me , looks like that i have found a treasure chest hidden somewhere.
Saw this video and the next day reworked some macros I wrote for work. Previously if there were more than 20 files you might as well wander down the hall, pour a cup of coffee and when you got back it would be finishing, maybe. In less than a hour of recoding I had it down to 9 seconds! Today I got it under 2 seconds for 70 files.
I thank God because people like you exist.
I've been a very advanced VBA programmer for a lot of years, but still learned something new. Thanks.
That's great to hear Tom.
Check this too
ua-cam.com/users/ExcelAutomationByMudit
This is absolutely amazing. I went from a run time of 6 minutes to 14 seconds! Thank you so much!
Thanks Peter!
Paul, you are one of the best overall coders I have found. You think out all the critical issues around a successful project - design, operational speed efficiency, code maintenance, user experience and project construction. It would be a joy to have you on or leading any team I have in the future.
One of the best and most useful Excel Videos found on the entire internet. Well Done and a big THANK YOU.
I really found these tips super easy to use and they allowed me to improve my spreadsheets performance 380x. Reduced run speed from 45 mins to 8 sec! Woohoo. The best one by far was reading the worksheet data into an array and processing it in memory rather than accessing the sheet. These instructions were so clear and easy to follow. Amazing. I even implemented the module timing in my code to target savings.
AWESOME!!!
That's the point of macros - speed!
I am happy there are ppl like you
Glad to help
I really wish I had watched this two years ago!! It would have saved me a lot of time learning some of these techniques the hard way!!
I wish I had watched myself 10 years ago😛
Thanks a lot mate! I watched this video and two days later I got to apply those tips to a code that was taking around 5s to run. After making the changes it runs instantaneously which is how it was supposed to be. Keep up the good work!
As someone who loves Macroing :) everything I do in excel and who is totally self taught with the massive help of google, that was inspiring. One of the most helpful things I have seen.
Thank you!!
Fantastic overview and breakout of the code! I never realized the cell to cell was so slow and traditional coding was so much faster (Arrays). That element collector trick is also very amazing!
Hi Paul,
Your videos and the way in which you explain them is to the highest level! Thanks for making our lives easier in the deep sea of code which we all love (some may hate I am sure haha).
I feel happy that I use these techniques all the time when vba calculation time matters on some projects.
That is some very valuable techniques! Will definitely be using them to write/REwrite macros. Thanks! 😊
Thanks Paul. This is the best video I've ever see in learning VBA for mastery. Thanks, i will watch all your 13 videos.
Thanks for your support Blake.
Paul this is probably best Excel video I have ever watched - what you have shown is just amazing and something that will benefit me massively - thanks so much for sharing and keep up the great work!!
Thanks Brad. Glad you have got so much use from this video.
Paul, you've blown me away! I'll be using these techniques to vastly improve the speed of my current Excel projects. Some of my projects take 10 to 15 minutes. This is bonkers! I've been writing the most inefficient code possible.
Thanks Ian. Don't worry we've all been there. The Copy and Paste method is so common place in VBA web examples that when I discovered assign I was sure it couldn't possibly be better than Copy and Paste.
Man, this is the BEST VIDEO ON VBA in youtube! Thanks a lot.
As the only accountant at my last two jobs that knew VBA, it's extremely refreshing to know others are using VBA at this level.
I've always referenced your site, I did not know you had a UA-cam channel, I've been on here just going through videos for (Repeating them of course) the last two days.
Thank you so much for sharing this knowledge.
You're welcome. Glad you like them.
Thanks Paul, Just completed coding to process 2.5 lakhs of records, its taking quite a lot of time, had to split it into 5 sets and run the code.
This video provides, clear picture where i can improve my code and speed up the process.
Thank you very much...
Thank you sooo much. You have taken my code and shortened it's run time by quite a bit :)
I still need to learn and understand the Advanced Filter a little better, but you have helped improve my workload significantly.
I truly appreciate it :)
Thanks Zach. Glad to help.
Outstanding! I have been doing it the slow way for years.
Paul, you are always great teacher, i spent lots of times for copy and pasta data with criteria before but now this is amazing. Awesome. Thank you man.
Here's a fan from Taiwan!! Thanks for this useful video, I learned a lot.
This is exactly what I needed. Spot on. Thank you so much!
Much appreciated. My excel calculation time has dropped from 20 mins to 5mins. It saved lot of time of my life. Thank you so much.
Glad to help Deepak
Hi everyone!
This video has some great practical techniques to help you speed up your code.
Please add any comments below.
Thank you.
thank you, Mr Paul! I'm going to implement it in my team. I wish you all the best in your career!
Hello Sir.. I have below code in my file.. it takes about 10 seconds to run.. how can we get it work faster..!?
Sub do_it()
For r = 7 To 18
Range("K" & r & ":L" & r).Value = Range("I" & r & ":J" & r).Value
If Cells(r, "G") "" Then Cells(r, "K") = Cells(r, "G")
If Cells(r, "H") "" Then Cells(r, "L") = Cells(r, "H")
Next r
End Sub
Hi, I have two questions: 1) I have an Excel simulation where I have 15'000 clients and want to run 10'000 scenario. At the end I'll produce four matrices (15'000 times 10'000 times 4). It takes about 36 minutes for 1000 scenarios and I already worked with arrays (as good as I could with my limited knowledge). Do you think it is possible to store up to 0.6 billion data records in Excel or do you have a tip what to do because Excel almost breaks down ;-). Should I "outsource" data into CSV-spreadsheets instead? 2) Do you know a good random number generator in VBA. I tried to use the "built in" random number generator but somehow it didn't work with my array code.
Read that Excel 2016 32 bits has a file size problem running VBA when it is greater than 50 Mb. True? If so, why? 50 Mb is not that big.
Very nice video...
Please provide your inputs
ua-cam.com/users/ExcelAutomationByMudit
Wow, one of the best VBA videos I've ever seen. Very helpful and everything explained very straight forward! I like that I wouldn't necessarily use everything, but the step by step way you explained each thing you did makes it easy to apply where it makes the most sense. Well done!
Thanks Matthew
This is amazing. I got suggested your video by youtube. Most VBA videos are too simple for me.
I had no idea that turning off calculations would give such a drastic improvement. It cut my execution time from 161 seconds to 1.2 seconds
That's great to hear:-)
Hi. i'm in the middle of creating an output sheet with a bunch of copy/paste from different sheets... Most useful youtube recommandation EVER! Thanks for the tips, it'll be very usefull
This is one of the most genuinely helpful contents I have ever come across.
As usual, thanks Paul. While I use the web a lot for examples, you are the only one I keep track of. Love your stuff.
Appreciate the feedback Alphonso.
Probably one of the best VBA help videos I have seen, You explained each part very clearly without over speaking, Thank you.
Things I learned from the video (MicroTimer, Resize, populating an Array straight from a range). I use assignment often, but looking forward to using it with Resize now.
Favorite part of the Video is jumping to just using Advanced Filter - Sometimes we over code for things that there is already a built-in function for. Thanks again!!!
Thanks Mark. That is high praise indeed. I'm glad you got so much from the video.
Thank you for the great video. I will now search for videos about advanced filters. With this technique I could solve the runtime problem with my program.
This is an awesome improvements. I did not know there were so many ways to speed up the code! Thanks for sharing these tips
I think this may have been THE best VBA advice I have ever seen. Working in a large company everyday, sometimes handling hundredthousands of lines of data, I can't wait to implement these advice in my future work. And maybe even rework some of my existing code.
Glad you like it.
A major work !
Very helpful and easy to integrate in all VBA code.
Thank you so much
No problem Jerome.
I program in VBA for over 10 years and I have learned again some things. Thank for this video very clear… even for a french with poor english
Glad you like it Sacha.
Best VBA tutorials I’ve seen so far!
Thanks for your comment Mei
This is my first time seeing one of your videos and I'm impressed! Your explanation made complete sense, at least while I was watching and listening to you. I'm going to have to watch it a couple more times to get it to stay in my head, LOL! But your directions are clear, pretty easy to understand and the visuals going right along with your narration is VERY helpful to me! Thanks!
Thanks for the feedback Jenny. I'm glad you got so much value from the video.
Wow, I literally ran my VBA exactly how you said beginners do but that's only because that's how the record macro feature told me to. Very helpful!
Thanks Nick. The macro recorder code is very different than real-world code. It leads to a lot of confusion.
I write lots of VBA code and I learned new tricks. I never used the advanced filter before. Thanks for creating this overview.
Glad it was helpful!
OMG this is an awesome video. All the stuff I do at my work will be so much faster.
Thanks John.
Instead of setting calculation to automatic at the end, it’s best practice to capture the current calc mode at the beginning. And set it back to that at the end. Manual calc may have already been set in Excel, a macro that turns it back on can be an annoyance.
Agree with brendan
Brilliant! I just learned lots and just wish I had known this a long time ago!
love from first video.. This is impressive and extremely useful.
You are outstanding in both, knowledge and ease of explaining complicated stuff.
Thank you for sharing. Would love to see more of practical videos like this from you.
You're Wecome Jawali.
Thank you so much for your great videos! You are great at explaining. Im learning a lot
Hi Paul, this is absolutely fantastic - I have watched it several times and made copious notes. I hope to be able to implement it in my personal stock recorder program
John
Hi John,
Great to hear from you as always. Thanks for the feedback. I will be releasing plenty of new videos in the coming weeks so key an eye out for them.
Regards
Paul
Thanks for this tutorial. Is a MUST for all 'amateur' programmers like me. Excellent and Thanks once again Regards. Martin ( South Africa)
Bravo!! great tutorial. It help me a lot to improve my data processing from multiple worksheets. Thanks a lot!
application.screenupdating = false
is a must for every VBA user.
That's true.
@@Excelmacromastery Only caveat is that whilst developing code, if it crashes and doesn't get to turn screen updating back on, you can find yourself stuck and potentially have to kill the program and lose any unsaved code. Sometimes it's prudent to only include the on/off code after you have solidified your code. My 2 cents worth. :-)
@@andrewlockwood6102 In this case, you can create a macro in your personal.xlsb that turns all of it back on. You then designate that macro as a custom button in your quick access toolbar. Something goes wrong, runtime error or whatever, click the button, everything is turned back on.
@@andrewlockwood6102 it only makes you unable to interact with the normal excel UI but not with the VBEditor Window. So just quickly write a new macro with "application.screenupdating = true" and everything is working fine again
Best practice is always to implement error handling and Set back to defaults there
Thank you for sharing this, Paul. These tips are extremely helpful.
You're welcome Eric.
Wow... Nice... Advanced filters blew my mind! Thank you!
Eye opening stuff! Thanks Paul.
You're welcome.
Great stuff! I use some of those techniques myself, the way you explained everything was definitely on point. My only critique would be to have the code comparisons side by side instead of flat out rewriting so viewers can see the difference if they pause the video. Might be helpful to some.
Thanks Winter. Glad you enjoyed it.
Thank you, I tried the first two steps, and increase 10 fold of the speed of my worksheet. Thank you for sharing!
That's great to hear Kai. Thanks for the feedback.
A big thanks from Germany for this very useful und powerful way to code, people like you make this world better!
Thanks Philipp
Thanks Paul, I like your clear step by step style. This bite sized video will have a significant impact on my approach to some tasks and see I will have to rewrite a batch of my existing routines! Using the advanced filter looks like a much more efficient,simpler and cleaner approach than pivot tables/ordinary filters to do the heavy lifting.
Great you found it so useful Chris.
Advanced filter are great for copying and filtering data in record format. They are not only fast but require less code and it's simple to select or reorder columns.
The one advantage that pivot tables have is that they group data whereas the advanced filter simply filters it.
Changing from reading directly from the sheet to loading the range into an array sped the macro up from 1 min 15 sec to only 1 second. Couldn't almost believe it! Amazing! :D
Nice work!
Brilliant, well explained. I've not used vba for years and I was helping someone out with something urgent for their work. I got what they needed sorted out but my process was running so slow. They are giving a demonstration today but it's been annoying me that it's taking around 2.30mins to run through about a thousand records. Now I see its because I am making all these select cut paste onto different worksheets. I will use some of the techniques you so brilliantly explained. So I had to subscribe. Thanks again.
Thanks for the feedback Tony.
Paul, thanks a lot for share this knowledge with us. This is really useful for the daily job.
Go ahead with your valuable work spreading all your knowledge. 👏👍
Thanks Luis.
The advantage of using arrays is that beyond being the second fastest, one could write text files (csv) out of the data collected fom the source sheet, parse to databases, etc . Moreover, one could cancatenate values from different columns, perform math operations between column/rows values, etc, all from the dataset in memory. In a nutshell, one could make a lot of things out of the array data in memory as opposed of using the Advanced Filter method which is fast but you don't have the control of the aquired data from the source sheet. Adv. Filter is fast but only transfers data from one sheet to another as it is, then you'd have to process the dataset again if you wanted to do more things with it. I personally have used the array method for the reasons stated above, unless I just want to filter data.
Great video, one of the better videos I ever watched on VBA and I have been coding / learning for few years
Thanks very much.
I am speechless. This is wonderful stuff. Thank you for sharing. God bless!
Paul, this is great! Thanks. I learned something new and useful today.
Glad to hear it. You're welcome.
This is the double slit experiment for Excel. Thank you for breaking this down.
Thank's a million, now I'will review all vba macro code cause now i'm using excel 2016, which is slowing down my macros.
You are the best VBA teacher of me, many thanks
I was aware of some of the tricks, but not really aware of the really big ones. Thank you! Well done.
You're welcome.
Holy shit! That is a significant time saving. Why didn't I watch this video years ago?
Great video!! I just watched this one video of yours and I subscribed. Good job!!
Thanks for this - I look forward to applying advance filter on current project.
This is incredible awesome. Another trick I got, was about use the part of enables and disables in a unique sub and just call then. Thanks for all
Glad to help Guilherme.
Thanks Paul, Timely informative, and pertinent, as usual.
Thanks lyonhard1.
Hats off to you sir. Big help. Im running a small engineering calculation. but still there are hundreds of sheets need to be duplicated. these tips improve it by a lot.
This is amazing. I wish I had found your site some time ago. I do have a couple of applications that I want to update. I am anxious to see how fast I can make them run. Thanks for this video.
Hi Paul.. this video was super interesting and useful.. really like how you presented it logically in sequence with Advanced Filter being the speed king. Thanks for the insights and valuable tips and tricks to speed code execution. Looking forward to more. Thumbs up!
"Speed King" - that's a great title.
Excellent! Thx. Extremely important concept in VBA
Wonderful method comparison! Thank you, Paul! I use advanced filter a lot in my code but never measured the time difference when compared with the other methods.
Thanks Celia. Glad you like it.
Clearly, the guidelines have improved the speed of my code... excellent & simple techniques... thanks
You're welcome Shohan
Didn't comment first time watching this, advanced filter method is my "go-to" for any code that needs a copy paste. On average, has mitigated 96% waste generation (time) based on original process. Thanks for sharing :)
This guy definitely knows his stuff! I am impressed.
wow !! The Awesome techniques I got today from your channel, Thank you very much for this great knowledge !!
A note on Paul's final comment about sampling these methods on a small set of data first:
Some of these methods will only give you gains as you scale up. For example, in a table with 1800 rows, adding the TurnOffStuff and TurnOnStuff functions added 15ms to my run time.
Results from a very small sample size *may not be indicative* of performance effects at a larger scale.
EXCELLENT, WONDERFUL, THANK YOU SO MUCH FOR YOUR CONTRIBUTION OF KNOWLEDGE
nothing new for me, but i like how consistently you explain)
good job, definitely.
Thanks Dimon.
Great video Paul. Excellent explanation, very well presented and so clear.
Thanks. Glad you enjoyed it.
I knew some of them, but not advancedfilter 😲, simply amazing, thx for sharing this!
Glad to share Ronald.
Very valuable video...I too recommend..to my friends...
Every one face these issues of slow running of macro..
Thanks for your time..
Thanks for sharing Gopi.
I have a similar task that took less time with screenupdating off.
Now if I use an array!!
Thank you!
You're welcome James.
Perfect. Excellent. Thanks a lot to take the time to teach us how to become our codes faster. Greetings from Venezuela. New Follower.
Thank you! This was very helpful. You got yourself another subscriber! 👍
Glad to have you on board.
Great stuff Paul, thanks a lot for this video.
Your live explanation is precious for me.
Excellent video, I'll be bringing this up in the huddle tomorrow
Glad you like it Patrick.
I have never tried to put the data in an array and then process it. Very clever.
People should also know how much faster Excel 2010 was at running VBA code than 13,16 or 365. If you haven’t upgraded stay away.
And if you have a lot of code use a VBA decompiler from time to time. Errors will build up in the P-code and you can clear them out. I don’t know why but it can also speed the macros up. Because magic but I swear it’s true.
Great video this should be a “watch first” for learning VBA!
and what about excel 2021?
Thank you for posting! Already have ideas on where/how I can use this.
Glad to help Stephen.
I love this advanced filter trick! Thanks for sharing!
Glad you liked it!