How to Calculate Three Dice Probabilities using Visual Basic in Excel 97

By

Michael Shackleford

January 21, 2005

The object of this exercise will be to calculate the number of permutations for each total from 3 to 18 given the throw of three dice. There are 6*6*6 = 216 permutations of the throw of three dice. This exercise will show how to use Visual Basic in Excel to determine the number of permutations of each total. A practical application would be to determine the odds of the casino game Sic Bo.

Start by entering the following information in a spreadsheet.

1. First right click in a blank area of the toolbar.
2. Select Forms. You should see a toolbox with various controls.

3. Left click on the button. This should be in the second row on the right.
4. Go anywhere in the spreadsheet and click and drag on a region for your button.
5. An "Assign Macro" box should pop up. Just click OK and it will disappear.
6. You can get rid of the form toolbox by clicking the X in the upper right of the toolbox.
7. Right click on your button where it says "Button 1."
8. Select Edit Text.
9. Change the name on the button to "Calculate", or whatever you prefer.

10. Right click on the Calculate Button.
11. Select Assign Macro.
12. Select New.
13. This should open the Visual Basic Editor Window.
14. In the Visual Basic Editor will be a window that looks like this.

15. Enter the following code into the window.

16. Exit out of the Visual Basic Editor by clicking the X in the upper right corner.
17. Now click your Calculate button.
18. Viola! Hopefully the desired permutations appear in cells B2 to B17.

If you want to go back into the Visual Basic editor to make changes right click in any blank part of the toolbar and select Visual Basic. That should bring up a toolbar that looks like this.

Click on the following button.

That should bring up the Visual Basic Editor so that you may edit your code.

Other Links

Sic Bo section at the Wizard of Odds.
How to calcualte the 3-dice permutation in The Mathematics of Sic Bo.