Arrays
By Richard Rost
3 years ago
Static Arrays in Microsoft Access VBA. Dice Roller.
In this Microsoft Access tutorial, I'm going to teach you how to use static arrays and the Randomize function to simulate die rolls for a Dungeons & Dragons character. We'll define an array to hold six ability scores and another to label these scores with their corresponding stat names like STR, INT, and WIS. This lesson is a perfect blend of learning VBA array basics while having a bit of fun with random number generation.
Members
Members are going to learn how to load the array of stat names more easily using the Array function. There are some additional changes we'll need to make to the code, but it's an overall better solution.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Static array, array function, variant, load array with values, array loop, breadcrumbs, die roll, dice roll
Subscribe to Arrays
Get notifications when this page is updated
Intro In this video, we'll talk about how to use arrays in Microsoft Access VBA. I'll show you how to set up and use arrays to store multiple values, like ability scores for Dungeons & Dragons characters, so you can generate, keep, and display random numbers efficiently. We'll create both numeric and text arrays, loop through them, and display the results using a status box. This tutorial uses the example of a dice roller function but covers array concepts that can be used in a variety of Access applications.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today, we're going to learn about arrays.
What's an array? I'm going to tie in a few minutes. We're going to work more with our random dice roller and learn how arrays work.
In yesterday's video, we learned how to generate random numbers, and we made a dice roller function. We could say die roll six. That's a six-sided die and three of those together, and that's your ability score strength. We got intelligence and wisdom for making D&D characters. You can use this for anything you want, including boring business stuff if you need random numbers for that.
However, this isn't necessarily the most efficient way to handle this information. What if you want to actually save these values so you can do something with them later on, like sort them, add some values, or whatever? It doesn't really do you much good if it just generates and puts it on the screen.
We could set up six separate variables: strength, intelligence, wisdom, dexterity, and so on. Then we could put those dice rolls in those individual variables, but again, this isn't necessarily the most efficient way to do it.
So in today's video, I'm going to show you how to set up an array. We'll make an array called ability score. It can hold six values, and we'll loop through it and set those three dice rolls into that ability score for each particular value.
This is going to be a developer-level video, which means we need some VBA programming. If you've never done any VBA programming before, go watch this guy. It's my intro to VBA video. It's about 20 minutes long, and it teaches you everything you need to know to get started.
Also, make sure you understand how basic variables work. Go watch my status box video if you haven't yet already. This is how I like to display information to users in a text box on a form, and I made a function called status box that does that.
Go watch my video on random number generation. This was yesterday's video. This is where we actually create that dice roller function.
Finally, go watch my video on for next loops. These are all free videos. They're on my YouTube channel and my website. Go watch them and then come on back.
Here's yesterday's database where we built the dice roller function and it works. Let's take a look at the code. We've got basically six lines here, one through six, where we're just saying for each stat, just generate three dice rolls and display it.
But what if I want to hang on to this stuff? Like I mentioned in the intro, we could make six different variables: strength, intelligence, wisdom, and so on, make those long integers, and save those values in there. But that's not necessarily the most efficient way to do it. Especially if you've got a hundred different items. Six isn't that big of a deal, but if you have a hundred items to store, then you're starting to get into putting it into a table, which is the whole reason we're using Access in the first place. But for small sets of data, more than a few but maybe not thousands, you can use an array.
How do we set up an array? First we dim the array just like you dim a variable: dim ability score. I like to keep all my variable names, including array names, singular. In here, you're going to specify what value it goes from and to. I'm going to say 1 to 6.
You could just put a 6 in here, but arrays are zero-based. So if you don't specify 1 to 6, it's going to be 0 to 6, and technically you're going to get seven items. I don't specify 1 to 6, but for beginners who are just learning arrays, I like to put 1 to 6 so you don't get confused. Most of the time when we think of loops, we think of 1 to 6 or 1 to 100. You have to remember that arrays are zero-based, so if you don't put that 1 in, it's going to start at zero. You could put ability score 5 and you get 0 through 5, but let's just go with this for now.
Just like a variable, it's going to be as long. Then we need a counter too because we're going to loop through the list, so X as a long as well. We are going to randomize because we're going to be using our random number generator still.
Now we just need a loop: for X equals 1 to 6 and then next. That's our end of the loop. What goes in the middle? We're going to assign each one of these values, so it's going to be ability score X equals whatever you want to put in it - these three die rolls. Copy and paste, just like that.
Now we can get rid of this stuff. This here just loads the array. We've loaded up an array called ability score with six values, and each of them is a number from 3 to 18.
Now we can display them to the user in the same way. We're going to loop: for X equals 1 to 6, start the loop over, next. Right in here is where we can display what's in each of those array elements. We'll say status, and then let's just say for now, stat and X and a colon, and ability score X, just like that. So stat 1 is a 12, stat 2 is a 16, whatever.
Let's see what this looks like. Save it. Close it and reopen it. Click, and there you go. Stat one, stat two, stat three, that's our X, and there are our values. You can keep rerolling them and get the same values you had before. Now these are stored in an array called ability score.
What if you don't want it to say stat? What if you want it to say strength, intelligence, wisdom, and so on? We can make a second array to hold the names of the stats.
Dim, let's call it stat name 1 to 6 as a string. It's going to hold string values. These we can just load manually. So stat name 1 equals strength, stat name 2 equals intelligence, and so on. There are the rest of them.
Now, down here, instead of putting stat X, we put stat name X like that. That will print strength, intelligence, wisdom, and so on, because that's pulling it out of a different array.
Now when I run it, there you go. There's your strength, intelligence, and so on. You could hard code these. You could pull these values out of a table. There are all kinds of things you could do.
One thing you could do is use the array function to load a value list like that. There are some other things you have to do. You have to change the way you declare this and how you handle it, and I'll cover that in the extended cut for the members. It's a little bit more complicated than just that.
If you want to learn a lot more about arrays, I cover them in more detail in my Developer 21 class. Lesson four is all about static arrays, dynamic arrays, redimming arrays, upper bound, lower bounds, and all kinds of stuff, including loading them with record set data. I spend a lot of time on arrays in this class. I'll put a link to this down below.
Arrays are really good for doing bread crumbs. Bread crumbs, you'll see these a lot on websites. These are bread crumbs right here. This is Developer 21 class, D21, and before that's the developer page, before that's the Access page, before that's the courses page, and before that's the home page. You follow the bread crumbs back to where you want to go. It's called a bread crumb trail.
You can go further down and back up. You go to the lessons page, here's the lessons page, and the bread crumb will take you back there. This will take you back there, so you can keep following it up the list. That's what bread crumbs do. This is perfect for an array, because you can say, okay, what's your parent? Put that in the array. What's your parent's parent? Put that in the array. And so on. I cover that in Developer 21.
That is your introduction to arrays. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the primary benefit of using an array in the dice roller example described in the video?
A. It allows you to store multiple related values efficiently under a single variable name
B. It allows you to generate larger random numbers
C. It makes your code run significantly faster
D. It automatically sorts the values for you
Q2. If you declare an array with Dim abilityScore(1 to 6) As Long in VBA, how many elements does it have?
A. Six elements, indexed 1 to 6
B. Seven elements, indexed 0 to 6
C. Six elements, indexed 0 to 5
D. One element, indexed at 6
Q3. Why is it less efficient to declare six separate variables (such as strength, intelligence, wisdom, etc.) instead of an array for ability scores?
A. Because searching individual variables is slower than searching arrays
B. Because separate variables cannot be displayed on a form
C. Because using an array simplifies code and looping, especially with more items
D. Because arrays take up less memory
Q4. In the video, what task is performed inside the For...Next loop when setting values in the abilityScore array?
A. Rolling three dice and storing the total for each ability score
B. Multiplying each value by ten
C. Sorting the scores
D. Setting all values to zero
Q5. What is one reason the instructor prefers to declare arrays with explicit lower and upper bounds (such as 1 to 6)?
A. To avoid confusion for beginners about the default zero-based indexing
B. To make sure arrays start at zero
C. To make arrays compatible with SQL queries
D. To reduce syntax errors in VBA
Q6. How can you display ability score names such as Strength, Intelligence, and Wisdom instead of just Stat 1, Stat 2, etc.?
A. By using a separate string array to hold the ability names and referencing it in your display loop
B. By renaming the array variable
C. By using a database table lookup each time
D. By typing the names directly into a message box
Q7. What could be a use for arrays beyond storing ability scores in the context of web applications, as mentioned in the video?
A. Storing breadcrumb trails for site navigation
B. Handling user authentication
C. Rendering graphics
D. Sending email messages
Q8. What is a good rule for naming arrays according to the instructor?
A. Use singular form, since each element represents a single value
B. Always use uppercase
C. Always add "Array" at the end of the name
D. The name does not matter in VBA
Q9. What happens if you declare an array with only the upper bound (e.g., Dim arr(6) As Long) in VBA?
A. The array is 0-based and contains seven elements (0 through 6)
B. The array only contains one element at index 6
C. An error will occur
D. The array will not work at all
Q10. What is the function of the X variable in the provided code example?
A. It acts as a loop counter to access each element of the array
B. It stores the value of the highest die roll
C. It is used to declare the array size
D. It provides the name for each ability score
Answers: 1-A; 2-A; 3-C; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone focuses on understanding and using arrays in Microsoft Access VBA. I am your instructor, Richard Rost, and today I want to explain what arrays are and how you can use them to improve your database projects. We will build on our dice roller example, which we have been developing in recent lessons.
Yesterday, we created a function to roll dice for generating ability scores, such as strength, intelligence, and wisdom, commonly used for character creation in games like Dungeons & Dragons. This function simply displayed the results, which works if your only goal is to see numbers appear. However, that approach is not efficient if you need to keep and use these values later, such as for sorting, further calculations, or storing more information.
An initial solution would be to declare individual variables for each attribute, like strength or intelligence. While this works for a small number of values, it becomes cumbersome if you need to work with larger sets, such as storing a hundred items. For tasks like that, a database table is more appropriate. Arrays are the perfect solution when you have more than a handful of values to store and manipulate but do not need persistent storage.
Let me walk you through how an array can help organize these ability scores. First, you declare an array variable just as you would any other variable, naming it something like abilityScore. When working with arrays, it is a good practice to keep the name singular even though it contains multiple values. You define the size of the array by specifying the range it should cover. For ease of understanding, I set the array indices to go from 1 to 6, so each element matches up with one of the six ability scores.
It is important to note that arrays in VBA are zero-based by default. If you simply say you want six elements, you actually get elements numbered from 0 to 6, which is seven elements. To make the code clearer, especially for beginners, I start my arrays at 1 so the indices match common looping logic. You can certainly adjust this as you get more comfortable with arrays.
Along with the abilityScore array, you will use a counter variable to loop through each slot in the array and fill it with values. In this case, for each ability score, we roll the dice three times and assign the result to the corresponding spot in the abilityScore array. This approach is much cleaner and more efficient than having six separate variables.
Once you have stored the results in the array, you can display them by looping through the array again, outputting the results for each stat. At this point, you might see outputs like "Stat 1: 12," "Stat 2: 16," and so on.
But what if you want to show the actual names, such as "Strength" or "Intelligence" instead of just "Stat 1" or "Stat 2"? The best way to handle this is by creating a second array to store the names of the statistics. You define this array as a set of strings, give each index its corresponding name, and later display both arrays together. For example, you print the stat name from the statName array and the value from the abilityScore array at the same position, which results in clearer, more meaningful output for users, such as "Strength: 14."
This method keeps your code tidy, and you can easily expand it or change the stat names without altering the rest of your program. You can also explore other methods, such as using the Array function to load a value list for the stat names, but that requires a different declaration and handling of the array, which I cover in greater detail in my extended courses.
If you wish to explore arrays further, including dynamic arrays, resizing arrays, finding array bounds, and loading arrays using recordsets, I delve into these topics in my Developer 21 course. In that class, I also show advanced uses such as creating breadcrumb navigation, which shows the hierarchical structure or navigation path users have taken in your database or web application. Arrays are ideal for tracking this kind of information since you can capture each step and its parent in sequence.
That concludes this introduction to arrays in Access VBA. Arrays are a powerful and flexible tool for managing groups of related data in your code. For a full video tutorial with step-by-step instructions covering everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Creating an array in VBA
Understanding array index base (zero-based vs one-based)
Declaring and initializing arrays in VBA
Populating an array using a For Next loop
Storing multiple related values efficiently with arrays
Displaying array values in a loop
Using a counter variable with arrays
Creating and using a second array for stat names
Displaying related values from two arrays
Assigning values to string arrays manually
Using arrays to store D&D ability scores
Replacing generic labels with descriptive stat names using arrays |
