Random
By Richard Rost
2 years ago
How to Generate Random Dice Rolls in Access VBA
In this Microsoft Access tutorial, I'm going to teach you how to generate random numbers with the Rnd function. We're going to learn about the Randomize statement, then see how to roll a 6-sided die to generate a random number from 1 to 6. As a fun example, we'll generate ability scores for a Dungeons & Dragons character by rolling three six-sided dice.
Members
There is no Extended Cut, but here's the database for the Gold members.
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
Links
Syntax
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, Random, rnd, randomize, dice roller, roll a die, D&D character generator, D&D ability score generator, generate random numbers, Random number generator, dice simulation, tabletop RPG, ability score calculation, six-sided dice, D20 system, pseudo-random numbers, random seed, dice mechanics, random distribution, VBA random numbers, dice-rolling algorithm, custom RNG, D&D stats, D&D dice roller, roll 1d6
Subscribe to Random
Get notifications when this page is updated
Transcript
Get ready to roll some dice. Today we're going to learn how to generate random numbers in Microsoft Access VBA. We're going to use it to roll six-sided dice, and then we're going to use it to roll some ability scores for D&D characters.
If you're interested in generating random numbers for any reason in Microsoft Access, there are two things you need to know: the Randomize statement and the RND function.
The Randomize statement is used to initialize the random number generator in VBA. It essentially uses the system clock to give you a new seed value for the random number generator. Basically, it ensures that you do not get the same random numbers over and over again. I'll talk more about this at the end of the video.
Once you've initialized the random number generator, you can use the RND function to give you random numbers. Let's see how this works in our code.
Before we get started, this is a developer-level video, so if you've never done any programming in VBA before, go watch this video. It teaches you everything you need to know to get started in about 20 minutes. And go watch my status box video if you haven't seen it yet. I like to give information to the user in a status box, which is basically just a text box on a form to display information. It's like a print statement.
Also, today we will be using the Int function to chop off the decimal part of a number, so go watch my rounding video. And optionally, go watch my create a function video. We're going to create our own function to return the result of a dieRoll. Alright, go watch this video too. These are all free videos. They're on my YouTube channel and on my website. Go watch those, then come on back. I'll wait for you.
Alright, so here I am in my TechHelp free template. This is a free database. You can download it off my website if you'd like to. Let's go into design view. I'm going to slide this up here and make my status box a little bit bigger. Right here, we're going to go generate random.
Now, right-click build event, and I'll bring up my code builder. Slide it over here. There we go. We're in the hello world button click, that's fine.
The first thing I want to do is randomize my random number. So just type in Randomize. What does that do? It initializes the random number seed. We'll talk a lot more about that at the end of the video, but basically, you're shuffling the cards, so to speak. Now we can use RND, the random function, to return a random value. Let's see what that looks like.
Let's go: status = "Your number is: " & RND
Save it. Back out here. Close it, open it, and click the button. There you go. 0.532, for example.
Let's get a couple more. Alright. There are some random numbers.
What RND does is it returns a random value from 0 to 1. It's not very helpful if you're trying to roll dice, so we have to do a little math to it.
Let's start by multiplying this value by the maximum number that we want on our die roll. So if you want a six-sided die, a standard die - dice is plural, die is singular. If you want a standard six-sided die, multiply it by 6. You want a 20-sided die, multiply it by 20. If you want a number from 1 to 100, multiply it by 100. Save it, and roll some more dice.
Now, we're getting values from 0 up to (but not quite including) 6. If you roll enough of these, you'll see.
I do not ever want zeros. Pay attention to just the whole number part. Ignore everything after the decimal point. I do not ever want zeros, and I'm never going to get a 6. So let's add 1 to this value.
Come back in here: (random number * 6) + 1. Let's put this in parentheses. Yes, I know order of operations - multiplication goes first, but this makes it more readable to the user later or to you in the future. You do not need the parentheses, but it's good to have.
Save it. Go back over here. Roll our dice some more.
Now I'm seeing some sixes and I'm not seeing any zeros. That's good; that's what I want. I want sixes. I want the bottom number to be 1. That's perfect.
Now, last step. I'm going to chop everything after the decimal place off. I do not want to round it. I want to just truncate it and for that, we'll use the Int function. So we'll take the Int of this whole thing. Int of this big thing.
Int says, I do not care what's after the decimal place, just get rid of it. We're going to an integer basis.
Now, when I roll my dice, there we go. I've got three sixes in a row, that's a good score right there. Keep them going. Keep them going. 1, 6 is 2, 5, 4 is beautiful. If you roll enough of them, you'll see that's what we want.
If you want a 20-sided die, just change this to a 20, and now you should be getting numbers from 1 to 20 or 1 to 100 or whatever you want.
So now, we have valid dice rolls.
Now, here's where rolling stats for D&D characters comes in. That's Dungeons & Dragons for those who do not know. I spent my youth playing this game. I love it. I haven't played in many years, but to do that, you're creating a fantasy character.
He or she has six attributes: strength, intelligence, wisdom, dexterity, constitution, and charisma. That's the old school AD&D and basic D&D list. That's the order that I learned them, and that's the order we're keeping them in.
Second edition fans, I do not even know what they're doing. They're up to 5th edition now. I have not looked at a book in years. Still got them all on my shelf, but I have not seen any of the new ones.
Now, generating them like this every time becomes a bit of a hassle. So let's write our own function that will return a die roll for us.
Right here, we're going to make it. You can make it Public if you want and move it into a global module.
Public Function. Let's call it dieRoll. It's going to take in the max value as a Long and it will return a Long. So it's going to take in the max value so you can use it to roll any kind of dice you want. Six-sided, twelve-sided, four-sided (don't step on the four-sided, you gamers know what I'm talking about).
Send in the max value and then return a Long integer, which is your number.
Here we're going to take this, copy it and put it right in here, and say: dieRoll = (Int(RND * maxValue) + 1)
We are going to replace six with the max value. The dieRoll, the value that's returned, the value of the function is this whole thing: a random number from one to whatever the max value is.
Now, down here, get rid of this and say dieRoll(6): roll me a six-sided die. Oh Bentley, please bring forth the six-sided dice. I wish to roll up a character.
Save it. Always good to throw in a Debug.Print once in a while. Then roll me some dice. There they are. Beautiful.
If you want to roll up multiple dice, just add them together right here. If you want to say each ability score like strength and intelligence, that's 3d6 or three six-sided dice. So you're going to get a value from three to eighteen. Just say dieRoll(6) + dieRoll(6) + dieRoll(6). Now you're getting 3d6. Go. These numbers should now be from three to eighteen.
It takes a while to see either a three or an eighteen. Did we just see a three? I'm not sure. That's a nice thing about the status box as I can scroll down to see the previous stuff. Did we get a three earlier? Maybe, I do not know.
If you want to generate multiple stats, let's say that this one is strength and we'll copy and paste for each attribute: intelligence, wisdom, dexterity, constitution, charisma. Save it, click, go. There are your stats.
If you want to clear it every time you run it, just come up here and say: status box = "" (blank the box), and then run. Every time you click on it, it's another character.
If you built this database, save this. We're going to be using this in some future videos. One of the reasons I'm recording this video is because I want to talk about For Each Next loops, because I'm doing a loop series right now. I realized one of the good things For Each Next is good for is looping through an array and I have not really covered arrays yet. So in order to do arrays, I want to do an example that fills an array with random numbers. Then I realized I have to update my random number video because the last time I did it a few years ago, it was about generating random records, pulling random records out of a table.
This is the random records video, and in this one, I do talk more about random number seeds and that Randomize function, pseudorandom number generators, true random number generators. We talk about random seed values, how the seed value algorithm works. In the extended cut, I show you how to get a true random number from web-based APIs.
There's lots more of your instant random information: random records, random numbers, the random number generator seeds, go check this video out. I'll put a link to it down below.
Of course, I talk about random numbers a lot in my Access Developer Level 3 class. There's a lot there, too.
If you want to have more fun with things like this, like dice rolling, there are all kinds of things you can do. You can assign values to the different stats. You can give the user six rolls and say, you can put this one in strength, this one in dexterity, etc. You can do different modifications of dice rolls, like roll four six-sided dice and drop the lowest one. There are all kinds of things you can do with this.
If you're interested in seeing more videos like this, let me know. I do not know how many gamer nerds I have out there watching my lessons, but there's all kinds of cool stuff you can do if you're into this kind of thing. So let me know, post a comment down below, say "Yes, I want more dicey, nerdy, gamey stuff like this."
If you built this database with me today, hang on to it. We're going to use it for future videos. For today, that's your TechHelp video.
I hope you learned something. Live long and prosper, my friends. I'll see you next time, and go roll some dice.
Quiz
Q1. What is the primary purpose of the Randomize statement in VBA?
A. It creates new random numbers directly
B. It initializes the random number generator with a new seed
C. It returns a random value from 0 to 1
D. It rounds numbers to the nearest integer
Q2. Which VBA function returns a pseudorandom value between 0 and 1?
A. Randomize
B. Int
C. RND
D. Seed
Q3. Why is it important to use the Randomize statement before generating random numbers?
A. It prevents syntax errors in code
B. It ensures results are always the same each run
C. It allows the random number generator to use the latest seed, producing different sequences on each execution
D. It makes the numbers larger
Q4. How would you modify the RND function to simulate rolling a standard six-sided die, returning only integers from 1 to 6?
A. Use Int(RND * 6)
B. Use RND * 6 + 1
C. Use Int(RND * 6) + 1
D. Use RND + 6
Q5. Why is the Int function used when simulating a dice roll in VBA?
A. To convert decimals into percentages
B. To truncate the decimal part and get an integer result
C. To round the number to the nearest whole number
D. To initialize the random number generator
Q6. If you wanted to roll a 20-sided die, how would you modify the formula?
A. Use Int(RND * 6) + 1
B. Use Int(RND * 20) + 1
C. Use Int(RND * 10) + 10
D. Use RND * 20
Q7. In the sample dieRoll function, what argument does it take?
A. The number of dice to roll
B. The seed value
C. The maximum value of the die (e.g. 6 for a six-sided die)
D. The minimum value to return
Q8. To roll for an ability score in Dungeons & Dragons using 3d6, what would the code look like using the dieRoll function?
A. dieRoll(3,6)
B. dieRoll(6) + dieRoll(6) + dieRoll(6)
C. dieRoll(18)
D. Int(RND * 3) + 1
Q9. Which of the following is a true statement about the RND function in VBA?
A. RND always returns a value from 1 to 100
B. RND requires a seed value every time you call it
C. RND returns a random value greater than or equal to 0 and less than 1
D. RND only works after calling Int
Q10. What is the role of the status box in the tutorial's example?
A. It serves as a user input field
B. It acts as a button to roll the dice
C. It displays results and other messages to the user, similar to a print statement
D. It stores the seed value
Q11. If you want to allow a user to generate multiple stat scores for a D&D character, what VBA approach was suggested?
A. Use a loop to call dieRoll for each stat
B. Use a single call to dieRoll(18)
C. Use status box without clearing it
D. Use dieRoll with a string argument
Q12. Why is the dieRoll function written to take the max value as an argument instead of hardcoding 6 for a six-sided die?
A. To make the function reusable for any type of die
B. To reduce performance
C. To make the code longer
D. To display the max value to the user
Q13. If you want to generate a random number from 1 to maxValue inclusively using RND, which formula should you use?
A. Int(RND * maxValue)
B. Int(RND * maxValue) + 1
C. RND * maxValue + 1
D. Int(RND + maxValue) + 1
Q14. Which of the following is NOT one of the six D&D ability scores listed in the video?
A. Strength
B. Intelligence
C. Luck
D. Charisma
Q15. The video mentions using which programming concept to improve dice rolling functionality in future lessons?
A. File handling
B. For Each Next loops and arrays
C. Object-oriented inheritance
D. Event-driven menus
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-C; 10-C; 11-A; 12-A; 13-B; 14-C; 15-B
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 is focused on generating random numbers in Microsoft Access VBA. In this lesson, I will show you how to simulate rolling six-sided dice, as well as how you can use this technique for other applications, such as creating ability scores for Dungeons & Dragons characters.
If you need to generate random numbers for any reason in Microsoft Access, there are two important concepts to understand: the Randomize statement and the RND function. The Randomize statement initializes the random number generator using the computer's internal clock, which ensures that you get different sequences of random numbers each time you run your VBA code. Without initializing with Randomize, you might see the same numbers repeated the next time you run your database. I will touch on why this happens at the end of this tutorial.
Once the random number generator is initialized, you can use the RND function to get a random number between 0 and 1. This doesn't seem very useful for rolling dice at first, so I will show you how to transform that value to fit your needs.
Now, this is a developer-level topic. If you are new to VBA programming, I recommend starting with my beginner-level introduction to VBA. I also have a lesson about creating and using a status box, which is simply a text box on a form that displays output to the user, much like a print statement.
Additionally, we will use the Int function to remove decimal places from numbers, so check out my video on rounding if you need a refresher. Lastly, I recommend reviewing my lesson on creating your own functions in VBA, since we will be making a custom function for rolling dice today. You can find all of these tutorial videos on my YouTube channel and my website. Go through those, and then come back here ready to continue.
In my free TechHelp template database, which you are welcome to download from my website, I am working in design view. For today's project, I am using a button to trigger the random number generation and a status box to output results. In the button's code, the first thing I do is initialize randomization by calling the Randomize statement, prepping the random number generator for use.
Once that's done, the RND function produces a floating-point number between 0 and 1. By simply displaying this number, you can see results in the status box, but for simulating a dice roll, we want a whole number within a specific range. To turn the RND output into a dice roll, we multiply it by the maximum die value. For example, for a standard six-sided die, multiply the random value by 6. For a twenty-sided die, multiply by 20, and so on.
However, we have to make a couple more adjustments. By default, this calculation yields results from 0 up to, but not quite including, the maximum value (for example, almost up to 6, but never actually 6). Since we want dice rolls that start with 1 and go up to the maximum, we add 1 to the result. To ensure we always deal with whole numbers and don't get partial results, we use the Int function, which truncates everything after the decimal point rather than rounding. When you bring these steps together, you get proper integer values between 1 and the number of sides on your die.
If you want to roll different kinds of dice, just change the maximum value. For instance, set it to 20 for a twenty-sided die, to 100 for a percent die, and so on.
Next, I cover how you can use this to roll ability scores for a Dungeons & Dragons character. If you are unfamiliar, D&D involves creating a fantasy character with six primary ability scores: strength, intelligence, wisdom, dexterity, constitution, and charisma. In classic editions, you generate each score by rolling three six-sided dice and adding the results together, which gives you numbers from 3 to 18.
To streamline this process in Access VBA, it is useful to create a custom function called dieRoll that takes in the maximum die value as a parameter and returns the result. This way, you can write dieRoll(6) to roll a standard die, or whatever number you need. Create this function as a Public Function, so you can call it from anywhere in your database.
To simulate rolling three dice for each ability score, simply add together the results of three calls to dieRoll(6). You can display the scores for each attribute in your status box by assigning different rolls to each ability, such as strength, intelligence, wisdom, and so on. Every time you rerun the process, new scores are generated.
If you want a clear display for each new character, just clear the status box at the start of the code before adding updated results. This keeps things neat and makes it easier to see each character's stats.
Going forward, we will use this database template in future videos. One reason I recorded this lesson is to lay groundwork for discussing For Each Next loops and arrays. Before I can demonstrate filling an array with random values, I wanted to have my random number methods up-to-date.
If you want a deeper understanding of how the random number generator works in Access, such as the algorithms behind random seed values, I recommend watching my video about generating random records. In that lesson, I discuss pseudorandom number generators, how seed values work, and even how to get true random numbers from web-based APIs. That video's extended cut covers how to source a true random number. You will find details and links to those lessons on my website.
I discuss random number generation more extensively in my Access Developer Level 3 class. There are all kinds of additional ideas you can try with dice rolling as well. For example, you might assign custom values to different stats, let users choose where to place each roll, or use modified methods like rolling four dice and dropping the lowest.
If you want to see more tutorials on gaming tools or similar projects, let me know in the comments. I do not know how many fellow gamers are in the audience, but there are many fun directions we can take with these techniques.
Remember to keep the database you built today, as we will use it for future lessons. For today, that's your TechHelp tutorial.
For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.
Topic List
Initializing the random number generator with Randomize
Using the RND function to generate random decimals
Scaling RND output to a dice value range
Adjusting dice rolls to avoid zero and include the max value
Truncating decimal places with the Int function
Rolling six-sided and other dice with VBA
Creating a generic dieRoll function in VBA
Rolling multiple dice for DnD ability scores
Rolling 3d6 for each character attribute
Displaying multiple ability scores in a status box
Clearing previous output before generating new results
|
