Watch these 28 minutes if you want to become an Advanced VBA user...

in this video I'm going to show you how to master using arrays Collections and dictionaries in VBA if you can grasp what I've covered in this video then you are well on your way to becoming an advanced VBA user now make sure to download the code from the link in the description below so that you can try out the examples for yourself we're going to look at using arrays first so in Excel we often have data like this and we often read it using VBA now the typical way that we read this data is by using a range so let's have a look at the code for this so the way we read this data is by declaring a range variable like this and then we set the range variable to the range that we want to read the data from so we're going to use current region here and what current region does is it gives us back all the adjacent data so now once we've got the range we read through it using a for Loop we say for I equals 1 2 and that's range rows.count so the number of rows that we have and then we close our for Loop here and now to access any item on a particular rule what we use is range and we use cells I use the current row and then we use the number of whatever field we want so we're going to take column one and we're going to take column two now when we run this code what we get is the day and the sales person so you can see we got the results of column one and column two the problem with using this method is that using range is extremely slow for reading data so what we do instead is use an array so we declare the array as a variant and then we assign the array to our range and not only just the range we say to the value of the range so we're using value 2 because it's the fastest way of accessing the values and now VBA will automatically create an array from all the values in a Range so we can read through it like this we use l boundary one which is the first item in our array and the u-bound array 1 which is the last item now these just deal with rows if we use number two it deals with columns so to access something we simply use the array and we're basically using the row and the column so I is the row one and two or the column if we run the code again what you'll see is that we'll get exactly the same results but our code will run a lot faster because we're using arrays and this can make a significant difference if you're using a large amount of data just like we can read from the range to an array we can also read from the array back to the range and this is how we do it we simply assign it to the array now the difference here is that when we assign the range we've got to resize the range so it matches the size of our array so we use the resize function to do this and then we use u-bound array1 which gives us the number of rows a new bound array 2 which gives us the number of columns so in terms of array this is basically the First Dimension and the second dimension and when we run the code you can see that it rolled out all the data to the column starting at column f being told how to use the array is one thing but to really understand it it's a good idea if we look at a real world example so let's look at a simple example where we take this data here and filter it by the salesperson Jenny and the results will look like this so let's go ahead and start writing the code the first thing we do is list out all the tasks so we can list them out as comments as you see here so the first one we want to do is get the range and place it in an array then we're going to store the row and column size in a variable and that's just to make the code more readable and then we're going to create the output array after that we're going to read through the data and we're going to filter the data so how we filter the data is by checking if the salesperson is Jenny and if the salesperson is Jenny we're going to copy that role to the output array and then finally what we're going to do is write out the array to the worksheet so now that we have a list of our tasks we can approach them one at a time the first thing that we'll do is we'll declare our array as a variant and then we'll assign it to a range so in other words this will copy all the data from the range to our array and it will automatically Dimension as in set the size of the array to the size that we want now the next thing we're going to do is we're going to store the row and column in a variable and the reason we're doing this is it just makes the code a bit more readable so rather than having new bounds everywhere we can just say row count equals u-bound array 1. and column count is u-bound array two so u-bound basically gives the number of items in a particular dimension in an array now we're going to have an output array and that's the array we're going to be writing out at a range so every time we find a row we're going to put it in this array and we set this to the size of the current array because we know that it's not going to be bigger than this and in the end we're only going to write out the records that we find we're not going to write out the entire array so we use the for Loop to read choose records so one to row count and then we'll terminate our for Loop down here with the next I so then we declare our variables we want to have dim I as long and that's for our for Loop and we want to have the sales person as well so the sales person again we're just using this to make the code more readable so salesperson will equal array I2 which is the second column of the current row now using this as I said mix to call more readable but it also makes the code run a bit faster if you're dealing with a huge amount of data and then we say if sales person equals Jenny so we're filtering here we want to add this row to the new array now we need to track the rule that we're going to write out to the output array so we use the variable current row this starts at zero and every time we find a record we add one to it so the first time it'll be one the second time two and so on so we declare this as a loan and we explicitly set it to zero now it's automatically Zero by default but it's always good to explicitly State what we're doing in our code and then to copy the row we use a for Loop and the for Loop reads across two our columns and we start by saying output array so its output array current row so that's the variable we just had and it's the current column so column J and then with array it's the current row that we're reading through which is I and then the column which is J and that will copy the row to the output array we're just to clear J as our variable up here then we terminate our read statement now to see if our code is working so far we run it and we stop it at the breakpoint at the end and then we click on output array and press shift F9 and click add and this will add a watch to our watch window now we can double click on the watch window to bring it up here and then what we'll do is we'll look through each of the items in the array so the first item you can see is the row one with Jenny the second is number two and the third one is number five you can see the fourth one is empty so it's brought us back three rows and put them in our output array and this is exactly what we're expecting before we write out the data to the spreadsheet we should write code to clear any pre-existing data so we clear the spreadsheet by using current region and that gives us all the adjacent data but we do offset one which essentially moves the range down one row it moves it off the header and then we clear the contents so let's try it here by putting in some temporary data and we run the code and you'll see that'll give us back the empty spaces as we expected so it removed all the existing data now we're going to write it out by doing sheet data range and we start on F2 so we're going to write a range from F2 and we resize the range to the size we want so we use current rule to write out the number of rows and this means it's only writing out the number of rows that we've added to our output array so we're not writing out the entire array and the number of columns is the number of columns in the output array so that's u-bound output array too and we simply assign this then to our output array so we use value 2 because value 2 is the quickest way of writing out the data so we run the code and you can see that we got the results we've got all the rows with Jenny written out to the range starting at f2 this is the code we use to filter data using an array now don't worry if the code is a bit complicated because when we use an array we pretty much use similar code to this all the time and you can also download this code from the description below the video and try it out for yourself now that we've seen how arrays work in VBA let's compare them to dictionaries and Collections and see how they differ and already is simply a row of data items in memory when we're dealing with worksheets we use a two-dimensional array which is essentially a grid of data items similar to a worksheet range their dictionary is a bit different it also stores data items however each item also comes with a unique key the purpose of the key is to allow the user to quickly access a data item for example in a real world dictionary we have a list of words which are keys and each word has a definition which is the value the main difference between using the dictionary and an array is that we typically use an array when we want to read through each item in our data and we use a dictionary when we want to retrieve individual values a collection is similar to the dictionary but has a few differences with the mains ones being as follows the keys in the dictionary are optional you cannot change a value if the basic type is like a string or a long one useful thing you can do with a collection is to insert an item in a specific position which is useful if you want to emulate a stack or a queue now the easiest way to understand these structures is to see them in action and we're going to have a look at some real world examples so you can see exactly which is the best one for each situation in this example we want to get to unique names from a list of 200 000. now in this example there's 97 unique names and we want to extract them from the 200 000 data items so we're going to do it first in an array and then we'll do it with a collection and a dictionary and we'll compare the code for all three and we'll see which method is the fastest now we're going to start with this code because we've already used this in our first example what we do with the array is we get the range we create the output array we read through the data and then we write out the data now the difference here is which data that we're going to be extracting so in this case we want to check what the last name is so first of all we say last name equals array i1 we put it in a variable because it's more readable and it's more efficient if we're referencing this multiple times and we're going to create a function exists in Array and what we do is we pass that function the output array we want to see if the idiom exists in this output array we want to give it the last name which is the item we're checking for and we want to give it current rule because we're basically telling it only check to this rule otherwise it doesn't know where to stop and it'll read through the entire array which is very inefficient so if it exists in the array we simply put the value in our output array at a current role position so we just say that equals last name and we before that we move current row to the next row so current row like in our previous example starts at zero when we find the first item we set it to one the second item two and so on now this is our exists in Array function this will return true or false based on if the item exists or if the item doesn't exist in our output array so in other words if we've seen it already so we read through the array we start at one and we read until the current row so if you've only got one item in the array it'll just read to position one and it'll basically check everything in the array to see if the item exists so we're just again doing a simple comparison we're saying if that in the array equals the value then what we want to do is return true so that's basically saying it exists in the array otherwise it will return false now a Boolean function will always return faults by default so we don't actually have to set exists in Array to anything it'll be false unless we set it to true now let's just change our resize here current rule one and now we're going to write out the data so we're going to write out the data and this is going to be written out to A2 you see when we run the code that we got the output to A2 if we scroll to the end you can see it goes down to 98 so it wrote us out to 97 unique values and that's how we extract unique values from a list using an array if we look at the time and for the array you can see that as we add more unique values that the performance degrades considerably and this is because when we have the array it means we've got more and more lookups to do and it takes longer and longer 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 is it a 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 unlike most courses you won't be over 100 information and left to figure out how to put it all together instead you'll be taken step by step to 10 Excel VBA applications with every concept explained once you start working two VBA applications you'll be amazed how quickly your VBA skills increase so check out the VBA handbook course at the excelvba handbook.com and the link can also be found in the description below the video we can make a slight adjustment to this code and it will work a bit faster for this particular application so instead of the clearing a two-dimensional array we declare a one-dimensional array so for this I was doing output in two-dimensional array with one column and now I'm just going to make it a one-dimensional array and you'll see that we have everything set to one now what this means is that if it's a one-dimensional array it's usually in rule format so you can see here that when we write it out it just writes out the first item multiple times so what we actually have to do is we have to transpose the array so we use worksheet function transpose to do this now keep in mind that the maximum values you can have in the array when you're trying to transpose is 64 000. now in our case there's no problem but it's just important to understand if you're going to be working with transpose when we run the chord you can see that we got the same results as in a previous method now it's important to understand this method because sum functions in VBA like like join will return an array and so you it's important to know how to write the array to the worksheet before we get started with the collection example let's look at the basics of a collection so we declare a collection like this and what you'll notice is that we never have to specify the size VBA takes care of this for us so you simply just add the item that you want and VBA will automatically resize it and do whatever housekeeping is needed to be done under the hood now to retrieve the item we simply specified a position and I've specified one and two here so when we run this code you'll see that it retrieved apple and orange you can see them in the immediate window now if we want to retrieve all the items in the collection we can run through the collection using a for Loop so we can use the standard for eye Loop like this or we can use the for each Loop now for each Loop is much quicker but the Ferrari Loop means that we have a counter so we can access items if we need to so it depends on specific circumstances and we run the code here you can see printed out the entire contents of our collection which is simply apple and orange now what most people don't realize is that when we add an item to a collection we can also add a key it's optional but we can use it if we want so in this case I'm adding a key which is the product ID and I'm giving this one product id001 and the second zero zero two and now to retrieve the item we can use these keys so you can see in the second ad that I've added idem and key to the parameter names just to make it clear now when you're using a dictionary there are actually parameters are in reverse so here we are using the keys and when we run the code the debug print will print out apple and orange so it used the key now the disadvantage with the collection is that we can check if the key exists and the second item is that we can't run through a list of the keys and that's why the dictionary is generally better so let's go ahead and get started using the collection in our first example so we've seen how to use the array to get the unique names from a list now we're going to use the collection and you can see most of the code is the very same we're going to get the range into an array and so on so we declare our collection first of all and the next thing that we do with inner loop we're going to add the item to our collection but we're going to add the name as an item and as key and the idea is if we try and add a duplicate key it will give us an error and it won't work now we don't want the code to stop in an error so what we do is on error resume next and this isn't really ideal because we're basically saying if there's an error keep going whereas there could be an error on a different line but we don't have an exist function in collection so this is what we have to do now we could when we're writing out a collection we could read through the collection and write it out through the worksheet but that's quite slow to do so what we do instead is we write it to an array and then we'll write the whole array out to the range now you might say why don't we just use an array in the first place just to do everything like we did before but it's actually quicker to use the collection as we'll see because the method of adding the collection and not having to cycle through the array is actually much quicker we read through the collection and then we just assign whatever the current value in the column to our output array and then what we'll do is we write the output array to the worksheet just like we've done in our previous examples when we run the code you can see that it's written out all the values write down 298. and so the collection works really well and now if we look at the speed you can see what the speed was like for the collection and you can see that is significantly faster than when we were using a race now let's look at how it approach the same problem using a dictionary let's have a quick look at the basics of a dictionary so we know what we're dealing with now we go to tools references and we click on Microsoft script in runtime and the reason for this is because dictionary is an external Library it's not actually part of the VBA but we still have full access to it now instead of using collection obviously we're going to use dictionary and I'm going to replace the variable C o l l with d i c t so it's obvious in our code that we're dealing with a dictionary now the chord we're left with is the same code that we use for a collection earlier and when we run the code you'll see there's no errors but it doesn't print out Apple the reason for this is that with the collection the item comes first and the key comes second but with the dictionary it's in reverse order so we'll reverse these and we run the code again and this time when we run the code you'll see that it retrieved the items as we expected from their keys so we got apple and orange now when we use in the dictionary one other difference is that the key is mandatory we have to have a key so if we don't provide two parameters say for example we take out apple and we run the code we'll get the error argument not optional so we always have to have a key and an item with a dictionary now another thing that we have in the dictionary that we don't have in the collection is we have the exist function and in this code that I'm writing here you can see that we can use exist to check if a key already exists in our dictionary now when we run this chord it writes out the product key if the product key exists so I'm putting in an invalid product key and you'll see it gave me the message box the product 009 does not exist if we want to redo all the items in a collection for example all the keys we can do so like this we use the for each key in the dictionary using dict will give us back dictionary Keys the collection of all the items and then we can print out the key and then use that key to give us the item when we run this code you'll see that it prints out all the keys and their Associated items so this is very useful when we're using the dictionary so now we're going to try this example of getting back unique values using the dictionary instead of using the collection or the arrays so first of all we declare our dictionary and we make sure obviously that we have the reference set to scripting dictionary and what we're going to do is we check if the last name exists and if this is false so if it doesn't exist what we want to do is we want to add it to our dictionary and we're just going to add an item of zero because all we're going to be using is the keys we don't care about the items for this particular example so if it doesn't exist we simply add it otherwise we simply ignore it now when we're writing out a dictionary we can actually write out the dictionary keys and the dictionary items straight to the worksheet because they're actually arrays but we need to transpose because as we saw already one dimensional arrays needs to be transposed because a one-dimensional array is a row so the dictionary case is an array and we can write that straight out to our range so we were under chord and you'll see it get us back to results so what you might notice is that I took very little code with the dictionary compared to using the collection already arrays and not only that but if we look at the speed you can see that the dictionary is much faster than using the collections and the array so the dictionary is definitely the best for this one now we're going to look at our third example and in this example what we're going to do is we're going to sum the values of each of the sales people so we're going to get all the totals so this is similar to how we create a pivot table now how are we going to do this and which is the best method to use well we've seen already that arrays is quite slow and we're trying to retrieve a value and we can use collections for this because collections don't allow us to change the value so we can change a value of a basic type in a collection so this means that the dictionary is our best option so let's look at the code and see how we'll use the dictionary to complete this task so this is the code we have so pretty similar to what we've seen before we put all the data from a range into an array and then we read true that array now you can see that we've got salesperson in column two and sales amount in column four so let's declare our dictionary like this and as always when we use a dictionary make sure that we have scripting runtime turned on in our references now what we say is if the salesperson exists in this dictionary what we want to do is we want to add to their value so if exists equals true we take the value at that key so give us back the value for that sales person and then add the sales amount to it and then we place it all back in the value at the key now if the person doesn't exist so the sales person isn't in the dictionary what we do is we just add it with the current sales amount now I'm going to refine this a bit shortly but let's go with this for the moment and then to to write out the items here what we basically do is we use the dictionary keys and the dictionary items now one thing that we must remember when we use these two arrays so additionally Keys is an array and dictionary items is an array and one thing that we must keep in mind is that the one dimensional arrays so as we've seen that means that they write to the role in a worksheet so if we want them to write to the column we've got to use worksheet function transpose that is a limit to this it only goes to 64 000 so keep that in mind if you're using worksheet function transpose so when we run the code what you'll see is that we got the results that we had expected so this is how we sum the values using the dictionary now I'm going to refine the code to make it even better so we actually don't need to check if the person exists in this dictionary and the reason for this is that if you look here this one line will actually do it all for us so if the person doesn't exist in the dictionary this line automatically puts it in the dictionary for us so this is really good because we don't need all the extra code to check so we run the chord again and you'll see that what we got was the results that we expected and with just one line of code to add the item to the dictionary whether it's there or not so you can see how useful this is this example is very similar to the last one but this time what we want to do is we want to sum both the volume and the amount columns now this is a bit trickier because the dictionary can only store one item per key so we want to sum two items so that makes it that little bit more difficult so how do we approach doing this well we approach doing this by using a dictionary what we do is the dictionary stores the ID or the position in the output array so let's look at an example of what I mean when we read through the first record here which is Jenny what we do is we put Jenny as the key but we put the value as our ID and so we're putting one and then in position one we're going to put Jenny and then what we do is we add the volume and the amount for Jenny now we come to the next record Bill we checked this bill exists in the dictionary and if it doesn't we enter them and we give them the next position which is two and then we enter that position in the array so we can always reference bill in the array by looking at its value and then John we do the same doesn't exist we add them to our array and then we add values to his current value and the current value obviously is zero the first time now this time we're meeting bill again and we've checked if Bill exists and he does so we use the value 2 to go to the position in the array and then we simply update his values in the array now the beauty of this is that at the end we've got an array and all we have to do is write out our array just like we did before and we can write it straight out to the worksheet let's start writing the code now the first thing we do is we declare our dictionary so using new here we create our new dictionary so we can start adding to it now we're going to create a variable and this variable tracks the new row ID now the first ID will be one the second will be two and so on and this is simply where we put them in the array and we'll use output row ID when we're accessing our row ID from the dictionary so it's basically the current output row ID now what we do first of all here is we say if the item already exists in the dictionary so we check if our salesperson exists in the dictionary now if they don't exist in the dictionary what we want to do is first of all we want to get a new row ID so that means we just simply add one to the new row ID so it starts at zero so now the new row ID is one we add the sales person as the key and then we add the role as the item so the row is the value and then what we do is we add the details to our output array so at the new row ID position we're going to add the salesperson's name and this goes into column one and column two and three will hold the further values which will be the volume and the amount so now we're down here we say output or row ID give us the output row ID and this gives us the value on a given sales person so the idea here is that if it already exists output row ID will give us to salesperson and if we've just add it it will give us the value at a salesperson as well so output array now equals what's currently in that position but we add what's in the array so we're basically adding what's in the current volume and what's in the current amount to that position in the array so this is similar to what we did in the last one it's just slightly different how how we position it with the dictionary and with the array and that's all there is to the code so for someone that seems quite complicated actually implementing the quote is very very simple so let's run the code and look at the results and you can see that we got the results that we expected we got the totals for the volume and the total for the amount after seeing these examples it's a good idea to really get to know the dictionary and if you check out my playlist here it covers everything that you need to know

Channel: Excel Macro Mastery

Views: 49,732

Keywords: #VBACollection #VBADictionary #VBAArray #VBAAdvanced

Id: cUB-MYTlDUU

Length: 29min 0sec (1740 seconds)

Published: Wed Dec 14 2022