3 Tips for Faster and Better VBA Macros in Excel - Simple to Advanced
Vložit
- čas přidán 2. 08. 2024
- VBA Course: www.teachexcel.com/vba-course...
Excel File: www.teachexcel.com/excel-tuto...
Make a 3 minute macro execute in less than 1 second!
Learn to make faster, better, and more robust macros with these 3 techniques. And yes, we will be using arrays for the advanced example.
In this Excel VBA tutorial, I walk you through the usage and implementation of these 3 teachniques to teach you how to use them, and not just show you 3 bullet points.
The examples for this tutorial are based on changing values in 30,000 cells and we really will take a macro that performs this task in 3 minutes ( 187 seconds to be precise) and chops that down to less than 1 second ( 0.08 seconds to be precise).
Excel Courses:
- VBA/Macro Course: www.teachexcel.com/vba-course...
- Building Professional Forms: www.teachexcel.com/premium-co...
TeachExcel.com
#msexcel #howto #tutorial
This channel deserves 135 MILLION subs for every office Excel user in the world that should be paying attention to your clips and getting all this awesome knowledge!
"Arrays, arrays, arrays."
____________
Really nicely executed tutorial/demonstration video.
I knew about almost all of these techniques but watched all the way through nonetheless as you can never say never (and I loved the pace).
Low and behold... When you mentioned about transferring data to another array as part of some filtering/analysis/restructuring process, I can think of a time when I might have ReDim Preserved during the loop, (which I already know is not the most efficient), but I hadn't thought of writing to a pre-defined, same sized array in the solitary pass of the initial array and then ReDim Preserving to trim the final array down at the end, when the desired final bounds become known (which I think you're referring to as a valid speedient option where the case is warranted), so I'll have to test this out for myself. I would have probably wrongly assumed that because it's still ReDim preserving, that it's not good practice, but in valid cases/the right context, it may be entirely still the best/fastest option.
Thank you! 🙏
I was a little sad to not learn anything new... but also very happy! It's nice to confirm we're in a good place, on the same page
I would also HIGHLY recommend adding application.EnableEvents = False to the list!
Great time saver to turn off Excel's ability to detect cell/sheet changes and other events!
I totally should have added that one, you're right!
If you already knew the arrays then you are definitely very far ahead in terms of making speedy macros! :) Another thing, that I didn't think would fit here since most people don't seem to program PivotTables/Slicers so often, is to set ManualUpdate to true before you change settings on PivotTables, especially with slicers! I might put that it one of my Excel Quickie videos soon because it works well to loop through the SlicerCaches to switch them off in many instances.
@@TeachExcel it's just rough dealing with the strict "text goes in rows, values go in columns, and you can ONLY sort from left to right" sort of feel that pivot tables offer...
I wind up doing innefficient Index Small/Aggregate formulas instead for dynamic updating lists...
I Like having my own criteria and not having to refresh anything! Plus DAX is too much for me to bother with...
I got a good one for you, add a Slicer and a Timeline to a PivotTable and change one and watch the other one not update until you refresh the PivotTable... I think there needs to be a PivotTable support group somewhere hahaha (I am all-in on helper columns by the way, and if your formulas are bogging things down, don't forget about copy/paste special values - I'm adding that to an import macro that I'm finishing up now to solve a similar issue and also the Timeline vs. Slicer issue I mentioned.)
Awesome! Arrays are the way to go.. well worth the effort to learn how to use them! Thanks for the dramatic and convincing examples :)) Thumbs up!!
Thanks Wayne! You are very welcome! :) I have to say that I did not expect the Cells.Select part to take that insanely long just because I haven't used it since I first learned to avoid it ages ago. I hesitate to think how much time and productivity are lost just due to this single mistake :/
This has to be one of the greatest tutorials I’ve ever watched on CZcams. Thank you so much!
Hi TeachExcel, my first comment!
Just wanted to comment a thing on arrayRows and arrayCols. Since myRangeArray "comes" from a range, LBound will always be 1, for both dimensions.
This does not depend on your option base, you cannot make myRangeArray's first index to be 0.
So, when you define arrayRows as UBound(myRangeArray,1) - LBound(myRangeArray,1) + 1 it is always going to be UBound(myRangeArray1,)-1+1,
that is, arrayRows = Ubound(myRangeArray,1). And same goes for columns: arrayCols = Ubound(myRangeArray,2).
No need to care for Lbound.
Hi Ruben! Thank you very much for your comment! When you get the array from a range then yes, but I mentioned in the video, I believe, that you can create a new array inside the loop, based of off the initial array, and the arrayCols/Rows was placed there to prepare for that situation. I know and expect people to take my code and play with it and I figured this would help avoid surprises for them if they create new arrays before placing them in the worksheet. I didn't create the new array in this tutorial, though, because I didn't want to get any more complex with arrays since they can already be scary for many people.
I really appreciate your comment and taking the time to explain it as well instead of something kurt and unhelpful! Thanks! :)
Cool tips for beginners! We need to remember that we can't "trust" 100% to macro recorder :)
Extremely useful and well explained! Thank you
I'm glad you think so Amal :)
Great Tutorial,Had Many A Headache And Fun With ARRAYS But So Worth It...Thank You Sir :)
You are most welcome Darryl! I'm glad you like it) Arrays really do seem annoying, especially when you have to do something like convert a String array into a Variant array, and its not difficult, just annoying, but then you see how much time is saved and its almost crazy.
very awesome magic file vba macro I do like it so much!
I'm really glad to hear it Neang! Thanks for your nice comment! :))))
Very timely tutorial Don, as I was working to speed up a new macro just this week. It may just be my coding but I ran into a couple of issues and wanted to ask your opinion. Regarding the Select statement, I did find there are some instructions that fail if not preceded by Select. An example would be copy / paste values for a column with an auto-filled formula. If I do not select the range after the autofill the copy / paste throws an error. When using the manual calculation Application I had problems with the results of an auto-filled vlookup column after doing a copy/ paste values. The lookup returned incorrect data and the copy / paste nailed it in place. I could have turned automatic calculations back on at the end and then done all the copy / paste values I think, but that would have been a lot of bouncing around between sheets and columns. Are you aware of a way to get around these problems? Thank you.
Dave, I'm very glad you found this tutorial then :) I can see a few potential speed-related issues in your coding, just from your comment, and they aren't so difficult to fix usually, but, I think you should post your sample code in a question on the forum and explain exactly what you are trying to do. I feel confident that you only require a few changes to make the code a lot more efficient, and it should be easy to spot when looking at the code. For instance, you don't need to bounce or move to other sheets to work on them or even other workbooks, you just need to have a reference to them.
(You are the perfect candidate for the vba course I'm making, and I am working as hard as I can in my free time to get it ready - the topics you ask about are thoroughly covered there.)
Anyway, feel free to post on the forum and make sure to include the code in question :)
Forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment
Sir, I have a doubt, plz make a video (how to find top 3, top 10 , top 5, in vlookup )
I think I might have already done that - search my videos! Otherwise, just use this for the lookup value: =LARGE(table_lookup_column,1) and change the first value to the column where lookup values are in the table and 1 becomes 2 or 3 or 4 or 5 to return other top values.
But please ask questions like this in the forum on TeachExcel.com - it is very hard for me to notice comments on videos once they are more than a week or so old and its is MUCH easier to help in the forum where you can upload sample files.
Tanks sir
Noice
Everything is going well until vba takes a value like 12/7/2024 (July 12, 2024) and arbitrarily converts it to December 7, 2024.
That is, vba works with a mm/dd/yyyy format but is not flexible to receive a dd/mm/yyyy format.
Todo va muy bien hasta que vba toma un valor como 12/7/2024 (12 de julio de 2024) y arbitrariamente lo convierte a 7 de diciembre de 2024.
es decir, vba trabaja un formato mm/dd/yyyy pero no es flexible para recibir un formato dd/mm/yyyy
sabes que, con todo esto concluyo que no vale la pena optimizar el codigo vba, de info que contenga fechas. es mejor copiar y pegar en hojas de calculo con codigo vba asi sea mas demorado, pero garantizo, sin tanto artilugio que estoy copiando y pegando datos identicos sin alguna modificacion no controlada.
You know what, with all this I conclude that it is not worth optimizing the VBA code, of information that contains dates. It is better to copy and paste into spreadsheets with VBA code even if it takes longer, but I guarantee, without so much trickery that I am copying and pasting identical data without any uncontrolled modification.