Highest-grossing films of 2012 in fact I've done that already in the VBA editor I've declared a simple variable that can hold a string and assigned a single value to that variable but what if I wanted to go a bit further and hold the names of not just the top film let's say the top three or five or even more films that's where arrays come into play the first example we're going to show you in this video is how to declare a fixed size or static array that can hold the names of the top three films in this list.
Declaring an array is a lot like declaring a variable so start with dim and then think of a sensible name for your array I'm going to call mine top three films and then unlike declaring a variable open instead of round brackets inside the brackets you have to state how many elements or compartments you when you're ready to have and because I know I'm going to store the names of the top three films I know that my array needs to have three elements so I can state that by saying the number two and the reason it's the number two is because by default in VBA arrays are.
Base from zero so the first element of the array will be number zero the second element will be number one and the third element will be number two so I can close a parenthesis then and then say what they to type on store so I'm going to as string just like with a variable and that's the basic way to declare a simple fixed size or static array now if you're not happy about the fact that arrays our base from zero you can change this at the module level by writing an option statement so if I prefer my arrays to be base from 1 instead of 0 I can write option base 1 at the top of.
The module that means that any arrays declared in this entire module have a base of 1 so that means that at this point my top three films array only contains two elements numbered 1 and 2 so I need to modify this to be top three films 3 as string the only acceptable values for optimum base by the way are 0 and 1 so I can either base from 0 or I can base from 1 any other number at all will generate a syntax error so it's at 1 or 0 that is your only choices.
Fact it allows you to do weird with things like start your array element from any number so for instance I could go from 13 to 23 if I really really wanted to that will declare an array with eleven elements I've never seen any reason to do that in the real world all my arrays either start from 0 or 1 depending on the situation so I'm going to set mine to one two three and that's the most reliable way to do it so now that we've declared our array we can begin to populate it and that's again a lot like populating a basic variable.
Except that with an array you have to say which element you're populating as you do so so let's begin by saying top three films and then I want to populate the first element of the array so in a set of brackets I put in the number one and make that equal to and was a range b3 that contains the name of the top film in my list then the other two elements I can populate quickly and easily by copying and pasting this line and change the element that I'm populating from 1 to 2 and 3 and then say b4 and b5 for the cells and that.
Will populate the individual elements of the array for the top three films in this list reading values from an array is just as simple as populating it but again you have to use the element number that you're trying to read so let's have a new worksheet into the web book worksheets dot add and we'll say range a1 on that new worksheet also its value to the third film in our top three films array I'm going to say top three films open brackets three then I can copy that line paste it in a couple of times and.
Then let's put it but in the the other two as well to arrange a two range a three I'm going to do this in reverse order a solid number two and number one for the top three films so that's in we're eating three values into the array and spits them back out onto a different worksheet when you finish with the array it's not 100% necessary but it is good practice to clear its contents and you can do that by using the erase keyword so if I say erase top three films that.
Will clear the contents of each element and because it's a string array it sets the elements to empty strings so there's a basic subroutine which declares a fixed size array populates it's three elements and then reads the values from those elements before clearing its contents all we'll do now is give this a quick run-through to show you how it works and it's a good idea at this point to view the locals window so head to the View menu and choose locals window because when you step through this ability and there's some useful bits of information you'll see in here so I'm.
Posts Related:
Going to click into the subroutine and start by pressing the f8 key and you can see in here that the locals window shows
You the list of your elements of your top three films array if you expand that it shows you element one two and three shows you their current values and what they type they are as you step through this regime you will see each individual element being populated and then will delete from those three elements and then finally this one will clear out all the elements and set them back to empty strings there's a basic idea of how LM.How arrays work when you're running your subroutines now with a small array like this one it's not too much effort to write a single line to populate each element and another line to read from each element but in the real world where your arrays are likely to contain hundreds thousands even millions of elements you certainly don't want to be writing a single line to refer to each one so for the next example we'll show you how you can write loops to both populate and relief from your erase so the example we'll do is.
We'll write a routine that populates a 10 element array and stores the names of the top 10 films in this list let's start by writing the new stability I'm going to call this one server loop over array and then we can declare our array again so I'm gonna call this one dim top 10 films this time and I'm going to set the elements from 1 to 10 as a string I'm also going to have a variable which allows us to count through our loops I'm going to say dim counter as.
Long so simple long integer what I can then do is well first we'll make sure I've gone back to sheet 1 I'm going to say sheet 1 dots up to 8 and then basically what we're going to do is offset from the top-selling column b12 all the way down to 10 rows down from there and populate the converter the corresponding element of the array so back in the VBA editor I'm going to say for counter equals 1 to 10 so I'm using exactly the same range as the element of.
Myarray enter a couple of times and say next counter and then to populate the elements of the array I'm going to say top 10 films open brackets I'm rather than stating exactly which number I'm using I'm simply going to refer to my counter variable so counter equals range B 2 dot offset and then I'm going to use the counter variable again to tell it how many rows down to offset counter clumsy row value so there's the loop that will populate the 10 elements of my array now let's loop over the contents.
Of the array and spit them out onto a new worksheet so I'm going to say worksheets dot add again and this time I'm going to loop through the loop in reverse order let's sleep in reverse order I'm going to say for counter equals 10 to 1 step minus 1 decrement the loop counter rather than incremented as usual so we'll say next counter and then fill in the content of the loop we'll say at Excel dot value so that's the the first cell in the new worksheet we'll make that equal to top ten films counter then I'll make sure I've moved.
Down to the next cell so I'm going to say active cell don't offset one comma zero dot select and then finally before we finished this ability and I'm going to say erase top ten films and again that's just good practices not only percent necessary to do so there's the subroutine written I'm going to display the locals window again view locals window then I'm going to begin stepping through the subroutine by pressing the f8 key and you'll see I've got my top ten films array it's got ten elements in it now they all are and as I begin.
Stepping through using the f8 key we'll see each element of the array being populated through this first four next loop so let's just get to the end of that one reasonably quickly keep moving pressing f8 and there we go so now we're going to add a new worksheet there it is and I'm going to set through and reversal to now counter ten to one step minus one so the counter will decrement this time and will read out the value of the 10th element of the array into the first cell so that will end up with Madagascar as the first value in the in the new sheet and that.
Was the value of the 10th element and as we continue going through we'll see each of the other array elements gets spit out into the new worksheet so again let's just step through this one reasonably quickly go all the way through to the end and then we see finally we'll erase the top ten films so you'll see all the contents of the top ten films array gets cleared out and set back as empty strings and then the subroutines so hopefully you can see using loops is a much more sensible way to go than populating single array elements with single lines of code if you've got more than three I think.
Probably even for just three I probably would have written a loop I'm not lazy but loops are certainly the more efficient way to populate and read from
Arrays with more elements one small problem with the way I've written my loops in this example is that I've used constant values to revert to the lower and upper bounds of a loop so that means that if I ever decide to change the way my array is declared I decide to change its size maybe I want to include the extra three films at the end of the list then I also have to.Change the numbers in the loop as well so there's an easy way to handle this if I change the bounds of my array so that it goes from 1 to 13 so that it includes all the film names currently in the list what I can then do is exchange these hard-coded constant numbers for functions which read the lower bound and the upper bound of my array so instead of the number 1 here what I can do is use the l bound function and pass into the album.
Function the name of the array whose bound I want to find whose lower bound I want to find I'm gonna say top 10 films so that will calculate with a lower bound of my top 10 films array is the value of 1 and I can do exactly the same thing then to calculate the upper bound you probably guess what the name of the function is it's called you bound so you bound top 10 films if I do the same thing again for the second loop if I use the l bound function to calculate the lower bound so exchange out for the number 1 oops I've missed the L there.
And then again for the you bound I copy that and paste out anyplace at the number 10 so this will mean that if I ever change the size of my array now I don't need to worry about changing the loop counters because they will always pick up on the current lower and upper bounds of the array so I just run this one through from start to finish we'll end up with a new worksheet G 7 if I look into excel I'll have all 13 films listed in reverse order all of the arrays that we've declared so far have contained multiple elements but only a single dimension you can think of.
A dimension is almost like a direction on the spreadsheet if I head back to excel we've effectively created a single dimension which is like a column in a spreadsheet so what if for the next example we wanted to store not just a single column like the film's name we also wanted to store next to each film is ID number and its release date and its length etc what we'd need to do in order to make that work is declare an array which has two dimensions the first I mentioned would have say ten elements letting a store effectively ten rows of.
Data and the second dimension would have perhaps five elements letting the store five units of information next to each row so to do that let's head back into the VB editor and we'll create another new subroutine called multi dimension array what we can then do is declare an array which has more than one dimension we can start in the same ways declaring a normal array so let's say dim and got on line top films and then open it out of parentheses and.
State the lower and upper bounds of the first dimension so I'm going to base my array from zero this time just to make things easier when we start populating here I'm going to say zero to nine so it's an array which has effectively ten rows in it you can think of the first dimension is a number of rows if I want to specify a second dimension then all I need to do is type in a comma and set the lower and upper bounds of the second dimension and for this one I'm going to say 0 to 4 and that effectively declares I say I guess five columns that's a.
Fairly neat way to think about it this two-dimensional array is a lot like a table it's got ten rows and five columns things get a little bit tricky when you realize that in VBA you can declare arrays with up to 60 different dimensions so you could carry on here and type in another comma and declare the lower up and a bounce at the next dimension and so on and so on and so on we're going to keep things much more simple for today at least we're going to stick to arrays which have just two dimensions for now the next thing we're going to do is state what data type.
We're going to store in this arrey and this is where we run into another small problem if you look back at the Excel spreadsheet you recognize that each different column has a different data type so there's some numbers some texts and veyts more numbers and more text so that's a little bit tricky because you can't declare different data types for different dimensions or different elements in a dimension what we'll do instead here that the sake of convenience is use the variant data type so that means that VBA will assign the correct data type as the array gets populated just to demonstrate.
The basics of how to populate a multi-dimensional array let's populate the first row by hand so we're going to say top films open brackets and now because we've got more than one dimension we have to specify the element for each dimension that we're populating so the first unit of information in our top films array will be 0 comma 0 I'm going to set that to be equal to range a3 value so that will store the ID number of the first film in the first element of the array like in the VB editor let's just make a quick copy of this line and paste it in four.
Times just to make life easier and then for each other unit of information in the first row I'm going to say top films 0 1 0 2 0 3 and 0 4 and then use column B C D and E and now I'm just going to use a locals window to watch what happens when I populate this array so I'm going to view the locals window and start stepping through this routine using the f8 key so as soon as I do that I can see the my array has been listed.
In the locals window and if I expand the top films array it shows me the number of elements in the first I mentioned so 0 to 9 so 10 different elements but can you see that each one of those elements actually has its own nested set of elements so if I expand top films 0 it shows me that that has 5 elements within it this is about how multi-dimensional arrays work I mentioned it is quite difficult to conceive of how a a 3 dimension or a 4 or a 60 time mention array works but rather than trying to think of them in terms of 3d space or 2d.
Space think of them in terms of a nested tree so if I had a third dimension films zero zero would have its own little nested set of units of information and so on and so on and so on anyway as we begin stepping through using the f8 key we'll see that for each unit of information that we populate just watch what happens to the value of zero zero and its data type gets stored in the correct element so the value 1 gets stored in top film zero zero and it.
Says variant double so it worked out this data type of the the first piece of information is a double if i press f8 for the next line it's going to be the name of the film so you can see that the value of that film is Marvel's The Avengers so our press FA there you'll see that this one gets converted to a string and so on and so on and so on date and then another double and another string and we can carry on doing this although we get very very tedious very quickly to populate every single element of this array by hand so what we'll do now when I'm ending the subroutine is write a loop that will populate this.
Array with the data for the top 10 films in this list so let's remove the locals window and let's clear out all these lines of code that populates the first row by hand and instead we'll have a couple of extra variables that allow us as to loop over the various cells we're gonna have to separate counter variables this time uncle mine dim dimension one as long then on the same line comma dimension two as long then I'm going to start.
Looping by saying for dimension 1 equals and to begin with I'm just going to hard-code the numbers I know I said this was bad practice but just for the sake of convenience here I'm going to say equals zero to nine and then close out Lee by saying next dimension one and then inside that loop what I'm going to do is say for dimension 2 equals zero to four and then close that loop inside there so I'm gonna say next dimension two rakahs failed to mention that would.
Help there we go the next event in team now inside this pair of nested loops what I can do is say top films open brackets dimension 1 comma dimension 2 equals a number of fir to the value of the cell that I want to store in that dimension so the easiest way to do this is to refer to the first cell in my array of data that's a 3 and offset from that point so I'm going to.
Say range a 3 dots offset dimension 1 comma dimension 2 dot value so the first time this inside this loop the first iteration of this this pair of loops dimension 1 will be 0 and dimension 2 will be 0 so I'll populate element 0 0 of top films by offsetting 0 rows down and 0 columns to the right from range a3 eventually it populates it with the value of cell a3 let's display the locals window again just before we start stepping through this one just.
Prove that it is actually working as we intend so as I start pressing f8 to step through I'm going to expand the top films array and expand the first element of the first dimension and as we continue to step through now we will see dimension one is zero and dimension two is zero so the first element of the array will get populated with the value of cell a3 then we'll go on to the next element of the second dimension I'm very careful to be explicit about what words I use here then Marvel's The Avengers gets.
Populated and you'll see that this essentially first row of data gets populated then we're going to exit the inner loop dimension two and move on to the next iteration of the outer loop dimension one so that will go to if actually top films one zero and will populate the first element of the second dimension of the second element of the first dimension of the array I'm going to get bored doing this and I'm going to trip myself up in the moment bytes by trying to describe this in detail so I'm.
Going to now is press f5 just to run all the way through to the end of that subroutine you can see clearly that this is populating each cell and probably what I'm not going to do it set a breakpoint on n sub so if I press f5 that will run everything all the way through to and so that but it hasn't yet ended so what I can do now is expand each of these different elements and show you that every single other piece of information has been populated in the array all the way through to the 10th element of the first dimension and the.
Fifth element of the second now I mentioned earlier that it's bad practice the hard-code the range of your loop counters because if you decide to change the bounds of your array then you'll also need to update these and it's easy to forget to do that I speak from bitter experience so I showed you that you use the L bound and you bound functions to calculate the lower and upper bounds of an array when we're working with multiple dimensions L bound a new bound are a little bit more complicated to use so let's start by.
Calculating the lower bound of the first dimension of the top films array so I'm going to replace the value 0 here with the l bound function open some brackets and I have to say which array I'm calculating the lower bound of so that's going to be top films but I also have to say which dimension I'm calculating the lower bound of for that array so we type in a comma there and say the number one and that calculates the lower bound of.
The first dimension of the top films array I can do the same thing here with the u-band functions rather than to nine I can replace that with you bound open parenthesis top films comma two so that calculates the earth sorry one again so I get ahead of myself that's sir that calculates the upper bound of the first dimension of the top films array then I need to do the same thing for their inner loop here so let's just quickly copy and paste the L down function from there so I can replace the value 0 with L bound.
Top films to the lower bound of the second dimension the top films array and then copy the um--and function and paste that over the number 4 and again replace the number one with the number 2 so that calculates the upper bound of the second I mention the top films array whew so having done all of that now I'm going to set a quick breakpoint on n sub again by clicking into this little that gray bar to the left hand side view the locals window again and then I'm going to begin stepping through using the f8 key and just expand my top films array and the the first element of the first dimension.
So as I step through will find that the L bound of top films dimension 1 is 0 you can see that there and the other bound of the first I mention of stop films is 9 so this will be looping through essentially in the same ways it before but it's now more resistant to changes if I modify the bounds of my array the elbe and a new band functions will happily work out what the new upper and lower bounds are I'm going to press f5 now to run all the way through to end sub but pause at that.
Point just so you can see that all of the other dimensions get populated in exactly the same ways previously and then I can end the subroutine and remove my breakpoint now that we know how to do this it's fairly trivial to loop over the array and read all these values back out somewhere as well so I'm going to close down the locals window just for the moment and same thing again I'm gonna add a new worksheet so worksheets dot add and then I'm going to loop over the array in exactly the same way but read its values into a new set of cells so the loop will be essentially exactly.
The same I'm going to copy the entire thing and then instead of reading values into the array I'm going to simply reverse this line so instead of saying top films dimension 1 dimension 2 equals something I'm going to replace that cut it paste it in at the start of the line instead make sure I've got an equal sign in between the value of the cell and reading the value from the from the array and instead of referring to range a3 I'm going to use active cell so it seems like that will start reading data.
In from the very first cell of the new worksheet that I've just added let's also make sure we clear the contents of the array by using erase top films there's nothing special that you need to do there with there with a race that will clear every element of every dimension of the array in one go and I'm going to set another breakpoint again in fact I'm going to set a breakpoint just before just on the erase lines edge before you erase the data from the array and then use the locals window and press the f5 key to run the the entire.