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

here i'm going to show you three ways to dramatically speed up your macros and i'm going to show you them through the context of a real world example where we go through 30 000 cells in this table and change the values in each cell and we're going to be using a timer to compare the difference between all three of the methods and to see just how much time we can save which is going to be a lot before we start check the video description and click the link to teach excel so you can download the files for the tutorial and follow along and make sure to subscribe and accept notifications so you can see all the new tutorials all right here is our sample data set it is just a straight up text for 10 000 rows and a few columns not too many but there are no formulas there are no links there's nothing too crazy now let's go to the vba window and get started alt f11 and if you downloaded this workbook open up module 1 and go to the very first macro and here is the downloadable file by the way with all of the comments in it and i wanted you to see this before we start coding so you can see how beneficial it is to download the workbook i modeled this off of the workbooks i have in my full vba course where i structure them so that they are a reference the reference guide mentality means there are almost no bare lines of code and everything is thoroughly explained so let's go over the very first example which is don't select cells and i'm going to clear out most of this and leave the timer in there and we're going to build this from scratch and this one as well but don't worry i'm going to walk you through everything and this is the timer by the way this is what starts the timer and this is what ends the timer and outputs it in the immediate window which we will go to when we run the macro so what we're going to do is to put all of our code within the timer so stop selecting cells it's the number one thing people do that slows down their macros tremendously and we are going to create a little loop and i'm going to create a variable for the loop dem cell as range for each cell in range and what we want to do is to go through c d and e in this table so c two to e ten thousand and one so c two to e ten thousand and one and then next cell and when you are in here what a lot of people do when they start out programming is they make their cell reference which we can now do using the cell a variable and then they go like this select and then they go on the next line and they do selection dot whatever you want to do here we are just going to put some text in front of the current selection value so selection dot value equals and the text that we want to put in front of it concatenated with its current value selection.value so it doesn't exactly matter what you do the point is never ever ever do this right here where you select the cell first and then do something with that selection the macro recorder makes you think that you should be doing this but you should not and why is that well let's hit ctrl g or go to a view and immediate window to get the little immediate window guy and make them a little smaller make this a little smaller and let's take a look at our data set right now so we are going to go through thirty thousand cells and put some text in front of it and the timer so how long it takes is going to output a value down here so let's click in here and hit play or f5 or just run and run and let's see how long this takes notice that the cursor is doing something we can see some activity and now the excel window is freaking out a little bit and i can't really do anything in it so let's see how long this takes i have a feeling i'm going to be fast forwarding quite a bit oh my gosh we are finally back 187 seconds is how long that took so just over three minutes you can see selecting cells is a horrible idea so how do we fix it it's actually very very simple and don't get confused that i have cell here because this is just a cell reference if you had it outside of a loop you would probably reference the cell something like this but once you have a working cell reference that points where you want it to point all you have to do is to take the line where you had a selection comment that out and go to the next line paste it in and take your range reference and replace selection with your range reference then comment out cell select and now with that small small change let us re-run the macro hit play and let's see how long i didn't even finish speaking 1.09 seconds that's how long it took to run again so we went from 187 seconds to 1.09 seconds just with that little change of our code and that's how important that is now let's add one more line to get the additional text off of the cells cell dot value equals and we're going to use some functions that are actually the same as the worksheet functions right and then what do you want to do i want to get some text from here and how much of it do i want to get well i need a length so i use a len cell dot value but i do not want the first two letters so minus two close up right go to the next line comment that out rerun this and you'll notice we get the o and the dash off for the first one and run it again and we should get the second one off there we go and it still only took 1.07 seconds and the timer will almost never be the same every single time that you run the macro even if it's the same macro in the same data and that's just the way it is now this is what i think is the most important tip now let's go to the next one where we are going to deal with some of the application settings and what i'm going to do is just copy this macro everything is working and then we are going to paste it in the next one all right so here we are going to tell the macro hey i want you to stop updating the screen while you are working and hey i want you to stop calculating formulas while you are working and that's going to save us quite a lot of time so let's say that we couldn't change our cell selections like this we had to have selection somewhere or we don't have time to go through all of our code or we just don't understand the code so that's where this is really going to help us so we can go towards the top and put application dot screen updating equals false and application dot not calculate but calculation equals excel calculation manual and then grab that and go to the bottom i'll put it before the timer and change false to true and delete that and hit control space to get the little intellisense menu back and choose excel calculation automatic now let's go back to where we had 187 seconds okay so this and this and we will comment these guys out and this time let's see how long this takes us get the immediate window back ctrl g and bring that over here click in here hit play and i wonder how long it's going to take 3.19 seconds so yes you want to stop selecting cells and you want to stop working on selections for the most part but if you can't or you don't have the time for it just go ahead and add these two lines and at least you won't go crazy waiting for your macros to finish running for the most part and if we then commented out the selection lines and brought back these guys we should get a pretty much similar time to what we had previously so run this to remove the text and we get 1.05 and now it's time for the final method and this one is going to speed things up dramatically but it's also the most difficult to use so go with tip 1 and tip two so that you can make sure that your code is working quickly with little effort but now if you want to make sure it works lightning fast go to the last macro and here we're going to be dealing with arrays arrays are really quite difficult but now let's see how fast this is because why are we going to do something so complex if it's not that fast so let me go down here you can already see it looks a little bit crazy and let's go ahead and add the text on get the immediate window back and let's go down a little bit and see how long it takes us so the last one was just over one second run this 0.08 seconds how amazing is that with this one you can leave work early all right so let us get the text off again and that should be about the same time yes now let's close this and take a look at what you need to change if you don't really want to learn that much about it alright so copy this macro and paste it where you want to use it go down here change this to the range that you want to go through then go down here change this to the upper left corner of the range where you want to paste the values that you have changed in our case this right here c2 is the first range right here c2 and when you are in these loops if you know which column you want to work on so we are dealing with c d and e and let's go up if i know that i want to deal with the manufacturer column then that is the third column in our range reference you can go inside the first loop right here and change the second part of this to the column that you want to reference we want to reference the third column in this range so column e so we put a 3 right here and this will get you the value from the cell now if you want to go through every single cell in this range up here then you use this loop right here and you go inside of it and you reference the cell like this so you do not hard code any values for rows or columns and this will go through every single cell in the entire range reference and that's all you have to change everything else is for the most part going to stay the same and you just work on your values in here however you want to work on them but now let's take a closer look at how this guy works all right so let's start up here and this is going to be kind of like a quasi explanation because like i said there's so much to do with arrays i just can't cover it all right here but in my full vba course i have an entire section dedicated to arrays and many useful examples throughout the course so definitely check that out if you want to learn more about arrays this is a dynamic array we know it's an array because it has the open and closing parentheses after it these are some counter variables we're going to use to go through the array by the way if you have variables declared multiple variables on the same line you have to put a type for each one or that one will be declared as a variant if you don't understand that don't worry about it it's not too important just to clear the variables that you're going to use to go through the array now here we have some interesting variables which don't matter too much in this tutorial but in the premium course they play a very important role because we create arrays from arrays and change their size and do all sorts of interesting things but this is going to store how many rows and how many columns are in the array so that at the end we can properly size the range reference where we put the array back in the worksheet now here of course we turn off screen updating and calculations we have our timer and here we start getting interesting we take our range reference we access the values from it and we put them all inside this array so now this is an array of values not range references not range objects just values from the cell and then what do we do we loop through all those values and the first one is the for loop out here this takes us through the rows in the array so for i equals l bound my range array that stands for lower bound and then two u bound which stands for upper bound my range array that just says hey i want you to go through every element in the array okay so now we're going through every element and the first series of elements that we go through if you go like this technically it's called the first dimension that we're going through we are going through rows and as i previously mentioned if you want to work on a value here you reference the array and then the i the counter variable that you used right here and declared at the top of the macro you put that in there for the row value and that allows us to access the next row every time this loop runs but here we hard code the column reference because we know that we want to deal with column e and our columns only span three so this is one two and three so we hard code that in there and we are then dealing with the value in column e now if you do this you may not need to go through every single cell in the row and then you would not need this next loop however if you want to go through every single cell in every row in every column then you need this loop right here and this loop is structured almost exactly the same as this one up here except for in the l bound and u bound we have a comma two which says hey i want you to work on the second dimension of the array and the second dimension is where our columns are stored so you can think of this right here as an address for the values in the array and here we are looping through the second dimension the second part of the address so when we want to reference a value in the array we type the name of the array and then we use the counter variable from the outer loop to reference the row and then the counter variable from the inner loop to reference the column and that's how we get the values all of the values from the range inside the inner loop and then all of this is the same as we had in the previous macro so we put an o and a dash and then we just remove it down here and all that we did is change how we reference the value so that's basically all there is to it but of course you can do many many many other things in here including creating new arrays from this array and when you do that that's when you have to then once again figure out what is the size of your array and do you need to shrink it back down because we're dealing with a raise for speed so one thing you do not ever want to do it's beyond this tutorial but i'm going to mention it now is you don't want to resize an array every time this loop runs so if you are going to put this value into a new array you don't want to resize that new array every time we get to here you want to make it so big out here that it can hold as many values as there are in this array and then once you get to the end of the loop then you resize it back down now that's one of the more powerful things you can do with arrays but if you do do that and you do resize it down here you will then no longer know how many rows and how many columns are inside of it or you may depending on how you do things but that's why i copy and pasted this from my premium vba course this little snippet right here because we do so many interesting powerful things i put a little dynamic way to get the correct number of rows and columns in there and this is exactly how it's commented in the course the whole tutorial is used as a reference for arrays that explains exactly how everything works and this right here is just the technique for getting the number of rows which accesses the first dimension and then this one the number of columns which accesses the second dimension that is a little bit confusing if you don't know what's going on with arrays so i beg your pardon if i've lost you at this point but they really are so powerful if you don't know how to use arrays take some time to learn them please please please they will speed everything up and make your life so much better after they've given you a few headaches but once you get beyond that it's very good so then all we do once we have the rows and the columns we now know the size for the array because in order to put an array into the worksheet and not lose any data not have that data truncated we have to exactly give the size of the array so we use a cell reference from the upper left which is c2 so we go here and then we do resize to say hey be three columns and hey go ten thousand down or ten thousand and one and then we get a reference like this and we simply paste in the array directly so here's range c2 then we resize it to the correct number of rows and columns access the value property and pop the range in there that is so much work but once you have a template even just a template like this you can copy and paste it into your projects and you will have to change almost nothing initially and then you can add all of your other non-array related code wherever you need to and by the way if you do happen to take my premium vba course and you want to see where we use arrays to the max then check out the file import advanced analysis tutorial and that's where you see just how amazing a raise can be and by that point in the course anyway you know arrays backwards forwards up and down but anyway let's finish out the last few lines of this so all we do turn screen updating back on calculation back on and the timer i'll put that to the immediate window and you're done okay so it is a lot of stuff and i could have just told you these three techniques for speeding things up very quickly at the beginning right turn off screen updating and calculation stop selecting cells and use arrays but i think it's really important to see examples like this and how they can be used in the worksheet so i just want to end by taking you back to the immediate window and showing you where we started and where we have ended up so we started with a very basic macro writing them how it's very easy to do when you start out especially if you're using the macro recorder to help teach you how to do things and you've selected a lot of cells we started out with 187 seconds that's how long the first one took and it totally froze up excel and was just not a good experience and by the end we did that exact same procedure 0.08 seconds arrays arrays arrays learn how to use arrays go home from work early i hope you liked the tutorial if it was helpful don't forget to give it a thumbs up and make sure to subscribe and accept notifications so you can see all the new tutorials

Channel: TeachExcel

Views: 5,180

Rating: 4.8759689 out of 5

Keywords: make macro faster, make excel faster, make vba faster, macro execute faster, arrays in excel, increase speed macro excel, teach excel, excel tutorial, ms excel

Id: b04lEbisWyQ

Length: 20min 39sec (1239 seconds)

Published: Tue Mar 02 2021