Over the course of the previous three blogs, we created a macro to simulate rolling a 6 sided die and then recording the result in a frequency table. In doing so we learnt the difference between variables and arrays, discovered how to read and write values from an excel spreadsheet, and explored how to repeat code in a for loop.
These are just a handful of the commands that can be used within a macro, but it may come as something of a surprise that with just these few instructions, some very complex things can be built.
When planning a resource that uses a macro I think about the end point first and identify the key function that I want from the finished product. In the case of the dice roll simulation it was
“I want to be able to simulate rolling a die, and after each roll the results appear in a frequency table and are displayed on a bar chart.”
Once I’ve got the basic idea, I try to sketch out the logic on paper before writing the code, thinking about what I need to be able to do and what variables I will need. There isn’t a right or wrong way to code these short macros, but a bit of planning before beginning to write can help avoid convoluted logic and unnecessary extra variables! It is always good to ask yourself “could this be simpler?”
The thing about coding is that once I start writing I start thinking about other things I could add to make everything more engaging, exciting or polished - so once the basic functionality is in place I revisit all the ideas I’ve had and try to think what would be useful. For our dice roll simulation some things immediately spring to mind. A reset button would be useful to clear the results and start again. Perhaps a way of automating the process so I can simulate rolling a die a hundred times or a thousand times in a few milliseconds would be good in a classroom, or how about the ability to change the number of “faces” on our simulated dice?
A reset function is the most straightforward thing to add and requires no extra coding skills. It will need to be triggered by its own button and run independently to the dice roll itself so we need to create a brand new macro. This could not be easier: the final line of your current code is “End Sub”, so just type in the following code immediately underneath it:
Reset() is the name of the new macro and by now you may be able to interpret the rest yourself. The code repeats itself 6 times, the first line of the loop rewrites the top line of the frequency table, and the second line replaces the individual frequencies with a zero.
The final step is to add a button (see blog 1 if you’ve forgotten how) and hook it up by assigning the new macro.
You actually have all the skills needed to edit your code to produce multiple rolls so why not give it a go? To change the number of faces you may need some extra commands, most of which can be found by Googling “how do I do a thing in VBA Excel”. Let us know how you get on and if you have any questions or need any pointers, post them in the comments section below.