Skip to main content

View related sites

  • Thought leadership
  • Media
  • Newsletter
  • FAQ
  • Events
  • Contact us
  • Facebook
  • Twitter
  • LinkedIn

Cambridge Mathematics

  • About us
  • The Cambridge Mathematics Framework
  • Our services
  • For teachers and practitioners
  • Blogs
  • Research
  • Thought leadership
  • Media
  • Newsletter
  • FAQ
  • Contact us

Creating simulation resources to teach probability, part 2 of 4

  • Cambridge Mathematics
  • Mathematical Salad
  • Creating simulation resources to teach probability, part 2 of 4
  • Blogs
  • Teaching maths
  • Considering the research
  • Interviews and intersections
  • Mining for maths
  • Events and take-aways
  • Policy and big ideas

Creating simulation resources to teach probability, part 2 of 4

by Darren Macey, 26 September 2016

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

DD2 1a image

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()

DD2 1b image

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!

Useful links

  • Home
  • About us
  • The Cambridge Mathematics Framework
  • Services
  • For teachers & practitioners
  • Blogs
  • Research
  • Thought leadership
  • Media
  • Newsletter
  • FAQ
  • Contact us

About Cambridge Mathematics

Cambridge Mathematics is committed to championing and securing a world class mathematics education for all students from 3 – 19 years old, applicable to both national and international contexts and based on evidence from research and practice.

  • Cambridge Mathematics

View Related Sites

  • University of Cambridge
  • Cambridge University Press & Assessment
  • Faculty of Mathematics
  • Faculty of Education

© Cambridge University Press & Assessment 2025

  • Sitemap
  • Accessibility and Standards
  • Data Protection
  • Use of Cookies
  • Statement on Modern Slavery
  • Terms and Conditions
Back to top
We use cookies. By clicking any link on this page you are giving your consent for us to set cookies