Make Your VBA Code Run 1000 Times Faster (Part 2)

hello and welcome last year I released a very popular video called how to make your VBA code run 1000 times faster it demonstrated how to make your code run exponentially faster by implementing little-known methods in this video I'm going to show you more proven techniques that will dramatically increase the speed of your application and just like in the last video I want to start with an application that is running extremely slow and I'm gonna make it run 1,000 times faster by implementing these techniques now if you like this video then please click on the like button and to get notified about any upcoming videos click on the subscribe button below and then click on the bell icon beside it so let's get started by looking at the code that we are about to change this is the code that were going to be changing now at the moment for 50 thousand records this code takes approximately 34 minutes to run what we were trying to do was get it up to 200,000 records and at the moment that would take probably two and a half hours upwards to run now normally when we multiply the number of Records it grows exponentially the time as well so a time could get up to anything when I'm running these tests obviously I'm not gonna do it with 50,000 records as it takes too long so I want to do it with a subset of 5,000 records now how we what we want to do first is we want to time it so we use the micro timer now you can Google micro timer Charles Williams and you will find it on his site so the micro timer allows us to time to the millionth of a second we're gonna multiply by a thousand so that we were just getting a millisecond or 1000 of a second it's very easy to use we do dim time as double and then we set the current time to equal to the current time from the micro timer so this will be down to millionths of a second and then at the very end what we do is we basically subtract them so we have Micro timer at the current I now minus the time that we saved and let's multiply that by a thousand to give us the output so when we run this code what we will get is how long it takes for it to run so it gives us the time from the start to the finish now the problem that we have is that we're going to put these all to our code and each time it's given us how long it took from the start to run so what I did was I created a simple class and the class gives us the time from the last time we timed it so if we look at the main timed here you can see my timer start and I'm gonna do a print time this is going to give us back the time since the start when I do the next one for update email it will give us back to time since get range and so on so it gives us very easy calculation how long everything takes and when we run the code then we can see exactly how long each part is taking and this in turn then will help us to identify the slow areas in our application so we're going to run the code and you'll see the results will appear in the immediate window on the right-hand side so let's go ahead we press f5 we run the code and you can see that we've got some data back so so far the first five items deval and pretty quickly but the next one is taken a long time so you can see the final one right dictionary to the worksheet took a considerable amount of time to run so let's put these on our spreadsheet so we can track exactly what we're doing so we're gonna paste our results in here you can see the percentage breakdown on the right hand side and from this breakdown you can clearly see that right dictionary two worksheets is taking up by far the most time so this will be the first one that we tackle so we can see fixing this would be an easy win so let's have a look at right dictionary to the worksheet and see what's in this code so we have right dictionary to the worksheet here you can see that it's just down there a quick pause to tell you about the excel vba handbook course are you struggling to build VBA applications do you find it difficult to get good information on how to create real-world VBA code the struggle every time you try to create a VBA application no matter how simple it is well the excel vba handbook course teaches how to build real-world Excel VBA applications from scratch on the most courses you won't be overwhelmed with information and left to figure out how to put it all together instead you'll be taken step by step 210 Excel VBA applications where every concept explained once you start working through VBA applications you'll be amazed how quickly your VBA skills increase so check out the VBA handbook course at the Excel VBA handbook comm and the link can also be found in the description below the video so if we look in the right dictionary - worksheet we can see straight away what the problem is so we're writing out to the worksheet for each item in the dictionary so writing out the key I'm writing out the item for far left key so that means if there's a thousand items we're gonna be writing out two thousand times now the problem is every time we write to the worksheet are read from the worksheet there is a time cost so we've got to reduce that as much as possible so what we want to do is write it out in one goal if possible and luckily for us the dictionary has got the keys function and this gives us back an array so we can get the all the keys back and write them out directly and we can do the same thing for items as well so we get rid of our loop because obviously we don't to be running to our loop a lot of times and what we need to do now is we need to resize so sheet output cells row 1 what we want to do is resize that cell into the same size as the number of items in the array but just do a resize and we say the number of laws is dictionary count and we've got one column because we're writing it out to a cop to do exactly the same thing for our items so this will write it out to column 2 now the thing about dictionary keys in dictionary item is that they return arrays and arrays in the format of a row what we wanted as a column so we can just simply use worksheet function and transpose and that will convert it from a row to a code now of course we're doing the same thing for items and so let's do a debug compile make sure everything is okay so then we'll run the code and we'll see exactly how the speed works and you can see that is considerably faster so let's put this in our spreadsheet so we please send the results and you can see that is considerably faster in fact if we look here we can see that the total time is now three hundred and sixty three times faster and a right dictionary it was taken about four minutes before and now it's taking only 113 milliseconds so that's a considerable difference so now we can see that we collection two dictionary is the one taken the longest time so that's the one that we're going to concentrate on next but first of all what we should always do when we make changes it's just check our output and make sure that what we did work correctly before we move on so we know that it worked the same way as the original code if we go down to the end of our data will see that it has 5,000 results and that's what we were expecting so as I said now we can look at read collection to dictionary and that's this one here so we can just right click and go to the definition so if we look in the read collection to dictionary soap doesn't seem to be really doing anything too extraordinary but one thing that a lot of people don't realize is that using a for loop losing like for I equals 1 is a lot slower than using how far each loop when we come to using a collection so we should change this to a for each loop so instead of this we say for each item in the collection and then we reference the item like this now we have here the item as a variant but we still want to use ie so we just say I equals 1 and then we'll just update it every time that we added to the dictionary we'll just add 1 - hi so in other words we'll have a key 1 key 2 and so on so we go to the top of our sub so we'll open our immediate window and then we'll run the code now you can see that we got the results back so let's check these results against our previous results so now we can see that if we look at what happened there we collection - dictionary the first two times we didn't change it it's approximately somewhere between 1 8 8 and 210 milliseconds now it's down to 33 milliseconds so you can see that is between 6 and 7 times faster so this can make considerable difference because we're only dealing with 5,000 records now when we get up to dealing with 200,000 records it can grow exponentially how slow this happens so it's important that we understand these areas that we can make the key differences so we can see now that empty collection now seems to be the one that's taking the most time so into collections taking somewhere between 143 163 and it's making up 43% of the time that our application is taking to run so let's have a look at empty collection so you can see here that we're trying to empty everything in the collection now the easiest way to empty everything in the collection is just to set it to nothing so not to bother with using a for loop like this we can just do set collection equals nothing poor we can set if we want to use the collection again we could set the collection equals new collection that will create a new empty collection but if you just want to empty it out just to spare memory we can just say set collection equals knotting so that saves us having to read through all the items so let's run the code on this one see if we gained any benefit these are the results we'll paste them into our Excel spreadsheet and see what we get so we pierced in our results here and you can see that if you look in the Green Line you can see after the first one we had a 363 times improvement then we got 629 and our Optus 826 so you can see the for empty collection it made a huge difference so rather than somewhere between 143 and 163 millions were now down to 400 milliseconds so that's a significant improvement now the only we can do anymore about right dictionary to worksheet what we can do is have a look at update email and see if we can make any improvements there so if we look at update email here where we call it what we're doing is we're passing in an array we're making a change to the array and then what we're doing is we're returning the array now returning the array like this is quite slow so let's have a look at update email so you can see that we passing the array as a variant and then we return it now this is very slow because we're passing it in and what happens is when we pass a back vba has to create a second one or a second set of data and this makes a very slow indeed so what we should be doing is not passing it back so this should just be a sub and we should be passing it in by ref now in the case of a variant array like this boy ref is faster because we're just referencing the outside array and then we make a change to it in normal circumstances we pass things by a valve and the reason we pass things by Val is that if we make a change in the soap to the variable it doesn't get changed outside but we're breaking the rules slightly for this because we want it to run a bit faster now there's one other players here you can see that we're also using by eval for the array so we're going to change this to by ref and we'll do a debug compile now this tells us that we were not allowed to pass it back which is good and we'll get rid of the parentheses here we do control G and let's run the code and see what the differences so I've got the results that's copied them to our worksheet so you can see when we paste in the results that for our update email that we got a significant advantage so it went from approximately between 37 and between 47 milliseconds down to 15 milliseconds so you can see that that is quite an improvement so now we're up to 879 times faster than what our previous code was so now we're going to make one more change to our code so if you look at the dictionary line here you can see I'm using create object and what this means is that we're doing late binding so the dictionary is an external library and we can connect to it using early binding or late binding and you can see more about that on my blog post on the dictionary but what we're going to do is want to change from net binding to early binding so this means we change from object to as new dictionary now what we need to do first is we need to go to tools references and make sure that Microsoft scripting runtime is turned on now you need to turn it on for every project I just had it turned on already because I was doing some testing but you need to turn it on for any project that you want to use early binding and so we don't need the create object line and anywhere that we're using object we can replace this with dictionary so we've changed them all so let's go to the top and let's run the code and see what kind of results we get and now let's paste these into our worksheet and see if we got any kind of improvement when we paste in our results you can see that we collection two dictionary has made a significant improvement so it's gone from 38 33 so that was kind of a ballpark figure of where it was it's gone down to 4 milliseconds so this is 7 times faster now in terms of milliseconds these changes might not seem like a lot but remember we're only doing this for 5,000 records and as we increase our records up to 200,000 there'll be a big significant time increase now our overall improvement since we started has been over 1,000 times so our code is now which started running at two hundred and four thousand milliseconds it's now down to 173 milliseconds and as I said for 50,000 records it was taken as 34 minutes to run so this is a significant improvement indeed if you're having trouble with your code if it's running very slow and you want to identify the areas with the problems here are four tips that you should follow so use the micro timer as it allows you to see the speed on a smaller set of data and start with a small sample of data so if you're having problems with 50,000 records and it's taken like 30 minutes to run what you want to do is you want to start with a much smaller sample so that your code will run very quickly and you can identify the problems much quicker so what you should do is start with the bottlenecks so when you look at the different areas of your code if there's one that's really really slow that's the place that you should start you get an easy kind of change there and one thing to keep in mind is that you should close all other applications so if you run your speed test with some applications open they can significantly slow down how Excel is running and if you run a got another time with no applications open then your speed can be quite different and you may not realize that this is the problem so the best thing to do is close all other applications in today's video we started with code that was running slow and we made it run a thousand times faster now I hope you enjoyed this video and I hope you got some use from it if you liked it please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the Bell I can't be cited now please let me know if you found these tips and tricks' useful in your own code and if you would use them so you can let me know in the comments below the video hope to see you on the next video

Channel: Excel Macro Mastery

Views: 27,139

Rating: 4.9708295 out of 5

Keywords: excel, microsoft excel (software), vba, excel vba, visual basic for, visual basic for applications, run vba code in excel, 1000 times faster, excel tips and tricks, faster vba code, speed up excel

Id: RNqd89K_bbU

Length: 16min 33sec (993 seconds)

Published: Fri Jun 26 2020