How to make your Excel VBA code run 1000 times faster
VloĆŸit
- Äas pĆidĂĄn 11. 07. 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
czcams.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
czcams.com/users/ExcelAutomationByMudit
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.
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.
One of the best and most useful Excel Videos found on the entire internet. Well Done and a big THANK YOU.
I feel happy that I use these techniques all the time when vba calculation time matters on some projects.
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!
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!
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...
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.
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).
This is absolutely amazing. I went from a run time of 6 minutes to 14 seconds! Thank you so much!
Thanks Peter!
Man, this is the BEST VIDEO ON VBA in youtube! Thanks a lot.
This is one of the most genuinely helpful contents I have ever come across.
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.
Here's a fan from Taiwan!! Thanks for this useful video, I learned a lot.
That is some very valuable techniques! Will definitely be using them to write/REwrite macros. Thanks! đ
This is exactly what I needed. Spot on. Thank you so much!
This is an awesome improvements. I did not know there were so many ways to speed up the code! Thanks for sharing these tips
Thanks for this tutorial. Is a MUST for all 'amateur' programmers like me. Excellent and Thanks once again Regards. Martin ( South Africa)
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
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.
Outstanding! I have been doing it the slow way for years.
Thanks for this - I look forward to applying advance filter on current project.
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.
AWESOME!!!
That's the point of macros - speed!
I am happy there are ppl like you
Glad to help
Thank you so much for your great videos! You are great at explaining. Im learning 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 CZcams 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.
Great tips, thank you!
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!!
Brilliant! I just learned lots and just wish I had known this a long time ago!
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đ
You are the best VBA teacher of me, many thanks
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.
I am speechless. This is wonderful stuff. Thank you for sharing. God bless!
Bravo!! great tutorial. It help me a lot to improve my data processing from multiple worksheets. Thanks a lot!
Wow... Nice... Advanced filters blew my mind! Thank you!
Best VBA tutorials Iâve seen so far!
Thanks for your comment Mei
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
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.
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.
wow !! The Awesome techniques I got today from your channel, Thank you very much for this great knowledge !!
Great stuff Paul, thanks a lot for this video.
That's awesome. Thanks for the video!
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
Your live explanation is precious for me.
This is the double slit experiment for Excel. Thank you for breaking this down.
Excellent! Thx. Extremely important concept in VBA
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
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.
Great work !!!! Thanks
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.
Great video!! I just watched this one video of yours and I subscribed. Good job!!
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.
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.
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.
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.
Great video, one of the better videos I ever watched on VBA and I have been coding / learning for few years
Thanks very much.
Really helpful. Many thanks.
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.
Eye opening stuff! Thanks Paul.
You're welcome.
Thank you for sharing this, Paul. These tips are extremely helpful.
You're welcome Eric.
Wow! Great info. Thanks for the tips.
I love this advanced filter trick! Thanks for sharing!
Glad you liked it!
EXCELLENT, WONDERFUL, THANK YOU SO MUCH FOR YOUR CONTRIBUTION OF KNOWLEDGE
Great VBA helpful video tips from you sir Paul.
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!
Clearly, the guidelines have improved the speed of my code... excellent & simple techniques... thanks
You're welcome Shohan
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
czcams.com/users/ExcelAutomationByMudit
Excellent - thank you.
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, Timely informative, and pertinent, as usual.
Thanks lyonhard1.
Perfect. Excellent. Thanks a lot to take the time to teach us how to become our codes faster. Greetings from Venezuela. New Follower.
Thanks for sharing. Excellent techniques. God bless you.
Loved this Advanced filter
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.
Wonderful! Keep up the good work!
This guy definitely knows his stuff! I am impressed.
AWESOME ! .... Super professional ........
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.
Paul, this is great! Thanks. I learned something new and useful today.
Glad to hear it. You're welcome.
Thanks for video!
Great video mate. Thanks for the heads up on arrays.
Thanks a lot Mike
Thank you I learned a lot from this video.
You are just Great Sir with immense knowledge
I was aware of some of the tricks, but not really aware of the really big ones. Thank you! Well done.
You're welcome.
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
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.
This blow my mind...thanks a lot.
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.
This is amazing.
I am so using this! Thank you
Glad you like it!
Wow, just awesome!
Great video Paul. Excellent explanation, very well presented and so clear.
Thanks. Glad you enjoyed it.
very useful, thanks a lot
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 :)
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.