Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Most of the variables you deal with when you write Access VBA code will be single variables, such as item prices, shipping rates, and so on; however, if you must deal with a set of values of the same type, such as a set of shipping rates, you can do so by creating an array. In this movie, we'll use the code in the Define Arrays code module, so I'll double-click that over in the Navigation pane to open it. And I have a very simple subroutine. Basically, I have the DefineArrays subroutine named, and then I create a variable called ChargesCount, and that is an integer.
An array is a set of values of the same type. And you define an array like you would any other variable, but you add a number, or numbers, in parentheses after the variable's name to indicate the number of elements in the array. So let's say, for example, that you ship products using one of six different shipping rates, and that rate is based on the package's weight. To create an array to hold those six rates, you would use this command. And typing below the ChargesCount As Integer line, it would be "Dim cur." That's the indicator I use for a currency variable.
And then type ShippingCharges. Then a left parenthesis and the number 5, then a right parenthesis, a space, "as Currency," and Return. Now notice that the number in the parentheses is one less than the number of elements. That's because the first element has the number 0 as its identifier. VBA, like most programming languages, starts counting at 0. That means an array with six items will have those items numbered 0 through 5.
If you want to number array item starting at 1, you can. To do that, you click in the declaration section and type "Option Base 1" and then Return. If you do that, make sure that you put comments elsewhere in your code so that your colleagues will know that's what you've done. In most cases, you should stay with 0 unless there's a good reason not to, so I'll go ahead and erase Option Base 1 by selecting it and then pressing Delete a couple of times. Now the next question is, how do you populate the array? There are a number of ways you can do that.
You can use a DoWhileLoop to read values in from a table, for example, or you can assign the values directly. So I'll click below the two declaration statements, press Enter, give myself a little bit of room to work. Now let's say that I want to create a variable called ShippingTypes. So for that it would be Dim strShipTypes, and again, that's all one word. And I will declare it as a variant.
Now note that even though I have named ShippingTypes as a string, or at least used my indicator to indicate that it will contain string values, I have to define the variable as a variant. The reason is that anytime you create an array, you must use the variant variable type. That's okay, because a variant can handle any type of data within it. With the ShipTypes variable defined as a variant, now I can define the array values. So for that it would be strShipTypes, then equal, and then a space, and the keyword Array, then a left parenthesis, and a series of series of values that you'd want to put into the array.
All of the strings must be within the sets of double quotes. So ("Overnight","TwoDay","ThreeDay", "Ground"), because that's the last item I want to put in, and press Enter. Now let's say that I want to display one of those values in a message box. To do that, type in "MsgBox," a space, and then variable name, which is strShipTypes, then a left parenthesis and then the number of the item.
Now remember that unless you change it, the first item is number 0. So I'll type in a 0 and then a right parenthesis and a down arrow so that we can see the line without interference. So I have a message box that displays the string ShipTypes array element 0, and that should be Overnight. So I'm going to press F5 to run the code. I see Overnight in the message box. The procedure I showed you assumes that you know how many items will end up in your array, but let's suppose you're creating an array from user input and you don't know how many items they'll enter.
In that case, you can create a dynamic array and change the array's size using a Redim statement later in the program. To define a dynamic array, you use a statement such as this one. You would say Dim curShippingCharges, but then instead of having a number inside of the parentheses, you would just leave it blank. So that means that you are defining it as an array, but you don't know how many elements will be in it. Now you can use what's called a Redim statement later on in your program to indicate the size.
So let's say that I want to work with the ShippingCharges. So I'll type Redim, a space, then curShippingCharges, and we'll make it 5. End the parenthesis and press Enter. So that redefines it. The problem is that if you re-dimension an array, Access deletes the existing values in the array unless you use the Preserve keyword after Redim. So, for example, if I wanted to keep my existing values in the ShippingCharges array, I would need to type Preserve after Redim, Preserve, and then press the down arrow.
Doing so would keep existing values inside of the array. Arrays help you maintain and look up data quickly without having to look up values in your tables. Maintaining these values in memory speeds up program execution and it simplifies your programming job significantly.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90325 Viewers
80 Video lessons · 137829 Viewers
59 Video lessons · 56557 Viewers
52 Video lessons · 70183 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.