In the last blog, we looked at how to create a simple macro that allows you to simulate rolling a 6-sided die at the press of a button.
While this was a good illustration of the basic commands needed to make a macro in Excel, I can't really think of a good argument for replacing your trusty, tactile, 6-sided die with a computer-based substitute. Over the next two blogs we will look at something better: how to record the results of each roll in a frequency table. In this blog we will introduce some new commands and in the following one we will look at how to use these in our dice roll simulation. By the end of blog three you will be able to make a working simulation that rolls a dice and records the frequency in a table after each outcome.
To get started you will need to have created the dice roll simulation from the previous blog. The code is given below in case you are starting from scratch:
Sub main()
DiceRoll = WorksheetFunction.RandBetween(1, 6)
Range("B2").Value = DiceRoll
End Sub
There are two new skills you will need to improve your spreadsheet: storing information in an array, and using a "For" loop.
Getting started with arrays
In the first blog we talked about variables: boxes to store information in which your code can work with. An array is very similar but rather than holding a single thing, they can contain lots of stuff. For the home furnishing buffs amongst you, if a variable is like a very small drawer, an array is anything from a bedside cabinet to an entire Welsh dresser.
We are going to use an array with 6 drawers to store our dice roll frequencies in. First we need to tell the computer the name of our new array. To do this, type the following line in the editor directly under Sub main()
There is quite a lot going on here. The Dim command is needed to signal that a new box of stuff is being created - it is a signpost so that the computer knows what is going on in the rest of the line. Results() is the name of our array; this could be anything but must end with the brackets to make it clear we have an array and not a boring, useless variable. As Integer tells the computer that the values stored in our drawers will all be whole numbers.
The reason for the 5 in the brackets is a little more complicated. Fortunately, if you have looked at anything with an index - such as nCr or sequence notation - the concept should be fairly familiar. We want our array to contain 6 drawers, one for each possible value on the die. The index in our array starts counting from zero so the 6 drawers are labelled 0,1,2,3,4, and 5. When we set up the array the number in the bracket is the size of the array, defined effectively by the final index number, in this case 5 not 6.
We now have an array but it is empty. Putting values into it is straightforward using the following code (don't type this in though, we will fill our array in a much cleverer way shortly!).
Results(0) = 4
This code tells the computer to put the value "4" in the first slot of our array. Each slot could be filled in the same way by changing the index number in the brackets. It would be much better though if we could read some information from our spread sheet rather than adding each value by hand. Something we will be able to do shortly..
Using a "For" loop
For loops are another fundamental of coding, they are a way of asking the computer to repeat some code lots of times while changing a variable incrementally. Every For loop has the following structure
For i = min_value to max_value
Some chunk of exciting code
Next i
The first thing we will do with a for loop is generate the header of our frequency table, the values 1 to 6 and write them to the spread sheet. Type the following into your spread sheet after the previous line.
For i = 0 to 5
Cells(5, i+2).Value = i+1
Next i
This will set i to zero, run the code chunk, then set i to one, run the code again, and so on until it has completed the loop 6 times in total.
Last time we wrote to a cell in Excel we used the Range() command, using a standard cell reference (e.g. B2). The "Cells()" command does exactly the same thing but targets the cell using coordinates (in a slightly unusual (y,x) format). In our code Cells(5,i+2) targets the cell in the 5th row and the i+2 th column. When i is zero, the cell targeted is (5,2), when i is one, the cell is (5,3) etc. These numbers are chosen purely based on where I want the frequency table to appear on the page, the key is that using the index from the for loop makes the code to put a different number in each adjacent column, creating a horizontally oriented table. The final part "= i + 1" simply assigns a number to each cell one greater than the current index. In practice the code generates the numbers 1,2,3,4,5,6 in adjacent cells starting in cell B5 and ending in cell G5.
That's all for this blog - join me in
part three for the final pieces in the puzzle to make your macro!