3 Tips for Faster and Better VBA Macros in Excel - Simple to Advanced

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:

#msexcel #howto #tutorial

[KleanthisSkoulikaris]

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!

[foxbox]

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

[BenLinfordUK]

"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 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! 🙏

[simonsieber]

This has to be one of the greatest tutorials I’ve ever watched on YouTube. Thank you so much!

[wayneedmondson]

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

[rubenmunozverdu]

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.

[SimpleExcelVBA]

Cool tips for beginners! We need to remember that we can't "trust" 100% to macro recorder :)

[darrylmorgan]

Great Tutorial, Had Many A Headache And Fun With ARRAYS But So Worth It...Thank You Sir :)

[amalhafiane]

Extremely useful and well explained! Thank you

[NeangBona]

very awesome magic file vba macro I do like it so much!

[davethomas]

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.

[dfpbueno]

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

[dfpbueno]

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.

[GopalRoy-pnze]

Sir, I have a doubt, plz make a video (how to find top 3, top 10, top 5, in vlookup )