34. VBA - Arrays (Programming In Microsoft Access 2013) 🎓

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop and today we're going to be continuing our series on VBA or Visual Basic for applications more specifically today we're going to talk about arrays and multi-dimensional arrays and some of the functionality that we can use arrays for all right so I'm going to hop back into my database here I've got a go button and on my go button I've got some code back behind here and I've set a break point and the way you set a break point is in the little margin here you just click next to one of your lines of code and that will set a little red dot here and highlight the line in red and essentially what a break point does is it sets a stopping point in your code so that you can inspect your code and look at the different values and kind of walk through your code to see line by line what's going on so I'm going to go ahead and go here and click on the Go button and you'll see that we have stopped at that break point and it's now highlighted now just an FYI the part that is highlighted has not yet been run all right it is the next part that is going to be run so when you run across values that are in the highlighted section they have not actually been calculated yet we'll get into that in just a little bit but essentially that's how you set a break point you can set a break point along among almost all the lines on here you can't set a break point next to any dim statement you can only do it on certain lines in your code such as a reading or when you're doing a value assignments and such alright so I've got a break point here now the way you step through the code and you go line by line is you can press the f8 key you can also add a button up here in the toolbox but I just prefer to go ahead and hit the f8 key so I'm going to hit that and see that the first thing that we come to after we've got our break point is that we're assigning the value one semicolon two semicolon three in our text variable right so basically it's what we call semicolon delimited all right where we're taking and separating each value with a semicolon this is a very common practice when you're storing information such as in a text like a text file something that's very common is called a comma separated values all right or a CSV file but in this particular case I'm going to use the semicolon to separate out the different values in my text string all right now I'm going to go ahead and dim an array called numbers as a string and you'll see that I call it a dynamic array a dynamic array is one where you have not set the upper boundary or lower boundary of the array it's just it's kind of malleable you haven't fully formed it yet it doesn't have that upper and lower boundary yet if I gave it a subscript value of say like 6 right out like that then it would be a static array because we basically said I know what the upper end of my array is it's going to be a 6 all right now another thing to keep in mind with arrays is that they are a base 0 that means that by default the first value in an array the subscript number is 0 so if you're looking to assign or retrieve a value from an array 0 would be the first number that you would use in the array to get the first value and the second value in that array would be 1 and the third value would be to etc ok there is a way that you can specify that in array of where the upper and lower boundary is and that is if you say 1 to say 5 something like that now you're setting the lower boundary to start at 1 instead of 0 and then if you tried to go look in the array at pointing it at 0 you would get an error so make sure that you understand that by default you're going to be starting with zero as the lower boundary and in this case five would be the upper boundary so 0 would be one values would be your first value then one would be your second value and etc so for right now we didn't have any boundary set we hit we didn't set any upper or lower boundary and we need to do that when we're going to use this special function called a split function what the split function does is it takes a text string and it looks inside the string for any particular character to separate those values by and up here we have a semicolon delimited so what we're doing is we're using the split function we're going to pass it two variables one called the text string and one that is in quotation marks which is the semicolon so we're going to say look in the text string for any instance of a semicolon and wherever you see that semicolon I want you to split up those values okay and what happens is that for each time that the split function runs into a new value separated by a semicolon it's going to add it to the numbers array here as a new value in the array alright so it's going to look through the text it's going to find one semicolon and it's going to see oh there's my semicolon so the first value the numbers 0 subscript is going to have the value of 1 and then it's going to go and find the next semicolon n whatever the value was there it sees oh it's two so in the numbers 1 subscript it's going to add the value of 2 and 3 would be the next one all right so we just now put all of those values into the numbers array here and now we're going to do a for each loop on that numbers array and for each item that we find in the numbers array I want to pop up a little message box just to show you that each individual item in the numbers array has a single value now so one is the first value - is the next value and three is the last value now the opposite of the split function is the join function now the join function we're going to take the numbers array and we're going to take each value in this numbers array and we're going to combine them together we're going to put them back into it a string variable here but we're going to take each very edge each value from the numbers array and we're going to separate it out with a colon so we're basically swapping out this semicolon and we're going to use a comma instead all right so we're putting all those values that we had in the numbers array we're joining them together into one single string or separating each value with a comma and lo and behold 1 comma 2 comma 3 all right so that's kind of neat those are those are - the really kind of neat functions of arrays something that you can actually use arrays for and the next thing we're going to talk about is the Redeem statement now a reading is important because up here we didn't really know what the upper and lower boundary of the numbers array was going to be and now that we've created the upper and lower boundary by using the split function let's say that we want to add more values to this numbers array but since we already set the upper boundary of their array to two subscript two we need to do something to raise that upper level to raise that upper boundary and that is using the read in keyword so here's the use of it you use read in and then in this particular case I'm also using the keyword preserve and then we're saying what the array is and then we're giving it a new subscript value in this case I'm giving it subscript value for now what the preserve statement does with the preserve keyword in the read M statement first of all it is optional you don't need to have it in here but by using the preserved keyword what you're doing is you're saying all of the value is that we're in the numbers array which is what we did up here when we split it up and we put it in we split the text string and put it into the numbers array I want you to preserve the values that are in the array and keep them there I just want to add or change the subscript range up to four all right the reason why we do that is because we want to keep those values that we put up there but if I left the preserve keyword out and just do to redeem numbers for all of those values that I put in up above here would be deleted they'd be completely cleared out all right so that's why I use the preserve keyword now one thing to keep in mind also about the preserve keyword is that you don't want to use it as much as you as much as you want to use it you shouldn't use it and that's because what essentially access has to do is take those values that you put in the numbers array and put them into a temporary array then basically delete this numbers array recreate it with the new subscript range and then take your temporary array that was made up in memory and dump all those values from the temporary rate and put them back into the numbers array so that's an awful lot of work for the Access database to be doing and that's why it's preferable that you don't use the preserve keyword if possible but obviously there are those circumstances where you just need to keep whatever the values were up in the previous code all right so I'm going to read them and preserve my numbers array I'm going to increase the subscript value up to four just one other thing before proceed just before I forget if I tried to read dimness as one to five I would run into an error because I'm using because I'm using the zero base as my array since I'm starting at zero and since I'm using the preserve keyword here and it starts at subscript zero I have to redeem it as zero if I did one two five I'd get an error I probably iterated that a little bit wrong but I hope you understand the thrust of that you're going to use that preserved key word you've got to make sure that you're redeeming with the same range all right so now I'm going to go ahead and read in this I'm going to go ahead add the numbers for discuss subscript three add the number five two subscript 4 and then I'm just going to run a little message box to show you that indeed we kept values one two and three and then we added the values four and five to our numbers array all right that's great next up we have the L bound and you bound functions which help us determine the lower and upper bound of an array so here I've got the l bound function and I'm passing it the array of numbers and what the L bound is going to return is what does the lower boundary of our subscript range and that would be zero notice I'm hovering the mouse over here we get the little drop-down box there's a little comment box that says L bound numbers equals zero all right that means that the lower boundary is zero which is the default for an array if I hover the mouse over the you bound function we can see that it returns a value of 4 because remember I read it up here with the upper bound of 4 so using the l bound and the you bound functions gives you the upper and lower boundaries of an array and that can become very very important when you're trying to loop through the code and that's what I'm doing right here I'm saying for I equals the L bound value which is 0 to whatever the you bound value which is 4 I want you to take the numbers array and whatever the value of I is I want you to add a value of I 2 that subscript of I all right so I'm taking like just just to go through this one time here I currently has the you of zero right we can see I is zero so what we're doing is in the array numbers with subscript of zero or I guess it's one no okay there we go I'm sorry I'm hovering it over at the wrong time I haven't yet set it it's still holding on to that one value that we put up there before there and that should be a little bit better so what I'm doing is I'm taking the numbers array subscript zero and I'm giving that subscript zero value of the numbers array a value of zero and I'm moving on to the next I so now one is going to equal 1 so 1 is equal to 1 2 is equal to 2 3 is equal to 3 and so on ok so now we're going to be basically getting 0 1 2 3 & 4 and again I'm doing it join separating out each value of the numbers array with a comma and we get 0 1 2 3 & 4 alright great so now let's talk about multi-dimensional arrays multi-dimensional arrays are great because they're in this particular case of what I've got here I've got basically you can think of this as the x axis and the y axis or or vice versa y axis and x axis whatever you want to think of but essentially what we're doing is we're setting from 0 to 4 so it'll be 5 values and then we're also having so I guess basically might be even easier to think this is like a table that's why I've even named it table of nums this would be like 0 through 4 column a rows excuse me and 0 through 4 columns so it'd be like this like I'm just taking these 25 cells here I'm doing 1 2 3 4 5 so that'd be 0 subscript 0 subscript 1 subscript 2 subscript 3 subscript 4 and then I'm doing a cross by 5 as well so that gives me a total in my array of 25 different spots made available one memory for me to store values okay I know it kind of came around if Hut's had a little weird but I hope that makes sense I think this illustration really helps out you can also add another dimension here I won't let me do it right now but you could actually add basically like a z-axis if you wanted that's why they're called multi-dimensional because you can add different dimensions of an array and your computer will be able to hold on to it alright so I've got a table of numbers array I've got four rows and four call or excuse me five rows and five columns again because we're starting with a default base value of 0 so 0 1 2 3 & 4 and then 0 1 2 3 & 4 for my columns here and what I'm doing and this is kind of complicated here but because I've got two different dimensions I need to iterate through each one of them so what I'm doing is I'm setting a for next code block here and inside of my for next code block I've got another for next code block the first or the top level for next is going through the X's and going for x equals 0 to 4 so that's going to be 5 times that we're going to iterate through this code I want you to basically iterate through this code five times okay and then we're doing the same thing but we're going to do it on the Y instead so this is essentially going to run this bit of code here 25 times in total and we'll see the first time through okay for x equals 0 to 4 so x is value right now is 0 and y is value is 0 and you can see if I hover the mouse over those values you can see x and y are equal to 0 so table numbs table of numbs array subscript the first value of the subscript 0 and the second dimension is also 0 and what I've done is I've set just for ease of use here I've got a string that I'm adding to the array where x equals whatever the value of x is and Y is equal to the value why so this is just kind of a neat little way to kind of show you x equals x and y equals y in our string alright so you're going to see it's the first time through X was 0 Y was 0 now it's going to go back up to the 4 and you can see why now equals 1 so X is 0 y equals 1 and that's sort of like saying okay 0 1 okay so alright this would be the cell so the first time through this was the cell this second time through this is the cell all right now the third time through this is the cell fourth time through this is the cell etc so you can see how it's going 1 2 3 4 5 and then the next time that we go you'll see it hits back up to it hits this X and changes X to 1 now and now we're iterating back through this loop again so that x equals let's hover it over the right thing here x equals 1 and y equals 0 so we're back to now we're down to this row so x equals 1 y equals 0 etc etc we're just going to keep on going through here I'm going to go ahead and set a little breakpoint here and run on down through and now this is a bunch of code that I've got in here which basically opens up a table TBL output and it's going to take those values that we put in up here I'm just going to put them into a table for us so you can see oops let me open up the right thing here so there we go so you can see Row 1 column 1 is this cell where we have x equals 0 y equals 0 the next time through Y equaled 1 but X was still 0 X is 0 y equals 2x is 0 y equals 3 X 0 y equals 4 and then down here x equals 1 y equals 0 etc so you can see the pattern this is how we get multi dimensions we've got one dimension going vertically one dimension going horizontally and then we could have another dimension going in the z-axis if you're familiar with the z axis and you can add even more dimensions than that I believe so alright I hope that's a good enough explanation I know there's a lot going on there arrays are kind of tricky things and I don't know if you caught this previously in a message but in one of my prior videos I'm not really an array guy I don't I don't really find them particularly useful but that doesn't mean that you won't run into some uses during your coding you may find them to be fascinating and work perfectly for whatever it is that you need them to do so I just kind of wanted to show you what they look like

Channel: Programming Made EZ

Views: 69,866

Rating: 4.8938055 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming

Id: JuBaUDRS3Lk

Length: 20min 44sec (1244 seconds)

Published: Sun Feb 02 2014