Which Excel VBA Method is the FASTEST for summing data?

hey there in this video we're going to take four methods for summing data and we're gonna see which one of them is the fastest so the first thing we need to understand is what exactly is summing data so if you look at the data on the screen here you can see fruit and sales and you can see that as individual records for each one of them so what we want to do is we want to group all the sales of each one so for example Apple we want to get the total sales for banana we want to get the total sales and for peach we want to get the total sales and we're going to try with four different methods in Excel VBA so the four minutes are the pivot table the sum if a do and the dictionary array so let's look at the first one so the first one we're going to look at is the pivot table we take our data like this we select insert and then we say pivot table and we're just gonna put it on this worksheet I'm gonna call it here and then we just add our role and we add our values and so this is what a pivot table is so we're going to call this pivot table in Excel VBA now before we start with the ESPE tests let's have a look at our data so I have randomly generated data here and this data has 200,000 records and it's got a thousand different users so a thousand different full names and we want to group by the full names and we're gonna group orders and microrna group amount so let's go ahead and look at the code so you can see that I have a group and some soap here and what the group and some soap does is we basically select our matters then we clear the data then we go down further we get the range and then once we have that we basically start the timer and then we use the method we picked above to select the soap that we're gonna run this time and then after the soap is wrong we basically print out the time that it took now let's pick the one we're going to use this time and it's gonna be pivot so this is the cord for a pivot table and let's just go down and we can have a look and see so we can get rid of this we're not going to group by week which is going to group it very simply and we don't need any column fields either so we're just going to keep this very very simple and so that's basically the code that we're using for the pivot table so let's go back up to our group and sub and let's bring up our immediate window so we can see the results and let's run the code and see what happens so the core took about 2.7 seconds which is pretty quick all things considered so let's enter this in our results table so the reader our results table works is the fastest one or the quickest one ends up being a hundred percent and we work out how fast all the other ones are in comparison so the next one we want to look at is the sum if so what exactly is the somme F so some F is bassy like the worksheet function you can see here so let me just put an apple here so so myth is this worksheet function and we basically give it a range and the range we're giving is this and then we select the criteria which is Apple we want to sum everything based on the current fruit so in this case it's going to be apple and we want to sum the sales so everything in the sales column we click OK and you see we get 190 so that's what so myth is now so myth is a little more complicated to use so let's go ahead and look at the code for so myth so the problem with zombies it sounds like a good idea in theory but the problem is that we don't know in advance what the different categories or her money that we have so what we have to do is we have to read through the list the first time and fill a dictionary with all the different full name so basically we're getting all the unique values we're getting a list without duplicates and then once we have that we run to the dictionary like here and you can see we run the worksheet function on the columns we need to do and then once the dictionary is updated we basically just write out the dictionary to the worksheet so it's a bit heavy-handed as you can see but it still worth trying this just to compare it to the others so again let's go and run the code for this and we'll change this to so myth and let's bring up our immediate window so you can see that it took a hundred and seventeen seconds for the Somnath to run so obviously I stopped the video while this was happening now the problem with Somnath is that it has to keep going back to the range and when something keeps going to the worksheet it tends to be very very slow but still it's always worth testing just to see exactly what it does so let's draw the value in here to the summit and you can see that the way that I've done it on my chart is that some if is 2% as fast as the pivot table in other words the pivot table method is 50 times faster so let's have a look at some more of the competitors here and let's see how does ATO stack up against some F and the pivot table so let's change our guy here to a deal and we go to definition and you see when we look at the aerial same thing here so what I do actually is is ActiveX data objects so we use this one we're connecting two different data bases and in this case we can treat an Excel worksheet like it's a database as long as it has records it has its data in record form so we basically just open up the connection and then we use our query string like this and then we just create the record set open the record set with the query and the connection and then write the values to the worksheet using copy from record set so let's go back to our tests and let's go to our group and so omit it now we always click in here just to make sure it's at the end of the immediate window and we're going to run this and see how long it takes to run it has taken a total of six seconds so let's copy that into our sheet and we can see that it's slower than the pivot table so you can see that it's about 43% the speed of the pivot table now one big advantage of a deal is that a deal can work on closed workbooks so if you're doing something like this and you're open lots of workbooks then areas were useful now the last one that we're going to look at is the dictionary array so the dictionary array is kind of the traditional way that we do that so how does that compare to the ones we have already and can it beat the top speed of the pivot table so let's go and look let's we'll actually set it here first and let's go and see what's involved in the array dictionary and so this is the code for the array dictionary so basically what we're doing is we create a dictionary and a dictionary is a structure that has a key in a value so each key is unique like a real world dictionary and the idea is that the value is like can be anything in our case the value is going to be a class module because we want to store more than one value so we have a dictionary and we basically go down and we say it does this item exists if it doesn't we add the item to the dictionary given it a unique key which is the name of the person and otherwise we basically take the one from the dictionary that already exists and we update the values so we basically just add to the current value and then at the end we'll have the total amount and now whom were finished with that we basically just go down to our dictionary and we basically write it out to the worksheet and as I said we use the array with this so rather than reading from the worksheet what I actually do at the top is we put the value in an array and then we read to the array and then at the very end we write it back to the array because that saves us let me just get rid of this because that saves us a lot of time ok so now we've seen what to use array dictionary is let's run it and see how it actually compares to the others so you can see that the value we got is basically one second now you can see this value is faster than all the other ones so the fastest method that we've used is using the array and the dictionary so let's paste it into our table here and just see what the final result looks like so you can see that the dictionary way is a lot faster than any of the other methods the pivot table is about one third as fast and IDEO is about 15% so we'll say dictionary raised about probably seven times faster so having looked at the different methods you can see that the dictionary array is clearly the fastest one however in some cases maybe you prefer the pivot table because of the formatting it provides and in other cases if you don't open the workbook then a deal might be the solution that you're looking for I hope you enjoyed this video and I hope this gives you some idea of the best method to use when you want to some data if you enjoyed this video then click on the subscribe button to get notified of any upcoming videos and click on the link in the description below to get the free VBA cheat sheet on arrays and dictionaries see you on the next video

Channel: Excel Macro Mastery

Views: 21,575

Rating: 4.9698796 out of 5

Keywords: excel vba, vba, microsoft excel, summing data in excel, excel, visual basic for applications, Pivot Table, SumIf Worksheet Function, ActiveX Data Object(ADO), Dictionary with the array, excel vba ado, vba pivot table, sumifs in vba

Id: KnesmRAWPNM

Length: 10min 34sec (634 seconds)

Published: Tue Sep 03 2019