Contents:
Download
To download this workbook, click on the link below and download the zip file. Inside will be the Excel file containing the game.
Macros will need to be enabled in order to play as the game uses VBA code in the background to run.
How to Play
To play is simple, there are a few buttons on the right side of the worksheet that control the entire game.
Just like in real Yahtzee, you have 13 turns to try and score as many points as possible. Each turn consists of 3 rolls, the initial roll, and two re-rolls. In-between re-rolls you have the option to freeze (or not re-roll) certain dice, giving you the best chance to score.
Let's say that with this roll, I'm going to go for fives. I would want to click freeze dice displaying 5. You can see they're frozen by the grey color.
Then I'll press the "Roll Dice" button. So close to a Yahtzee!
Now it's time to take a score. To the left, you'll find a scorecard with some more buttons. To lock in your score each turn, you'll need to click one of the "Take Score" buttons.
Repeat until you've finished all 13 turns and you'll have your final score!
You then have the option of starting a new game using the "Next Game" button, or you can "Reset All" in case you want to start over.
How Does it Work?
Now that we know how to play, how does it all work? There are a few main parts we can go over to illustrate the main ideas.
Creating the Dice Display
Arguably the most important part of Yahtzee is the Dice.
To create the dice used here, I first created a quick tracker of each dice and their respective rolls. Then, using the "Roll" number for each dice, I used a series of IF statements inside each pip of the dice.
Since the top left most pip shows whenever a 2, 3, 4, 5, or 6 is rolled, anytime that the "roll" number matches that roll, a 1 is displayed. If not, a 0 is displayed.
Repeating this format and formulas for each pip gives us a grid of 0s and 1s in place of the pips.
Here you can see how a six would look if rolled.
From here, we can add conditional formatting to add a black background every time a 1 is present and we get the finished die. This is repeated for all 5 dice.
See the file for the exact formulas used.
How to Roll and Freeze the Dice
Now that we have the dice correctly made, now we need a way to roll the dice. For this, we'll use our first macro.
To "roll" each die, we can use the built in VBA function RandBetween. Just like the Excel function, this will set a cell to a random number that falls between two set numbers. In our case, 1 and 6.
This rolls all of the dice. But what if we wanted to freeze specific dice and make sure they don't roll?
We can create some toggle buttons linked to the cells next to our die tracker. If the button is on, or frozen, then "TRUE" will be displayed, if not "FALSE.
That way, we can create a check to see if a dice is frozen or not, and only re-roll the un-frozen ones.
Here is the full dice rolling subroutine.
Sub Roll_Dice()
Dim rng As Range
Dim i As Integer
' Check number of rolls remaining
If roll_counter = 2 Then
MsgBox "Out of rolls, take a score."
Exit Sub
End If
' Set the target cells for the random numbers
Set rng = ThisWorkbook.Worksheets("Calculations").Range("C6:C10")
' Set the cells to check for the "Frozen" condition
Set checkCells = ThisWorkbook.Worksheets("Calculations").Range("D6:D10")
' Generate random numbers between 1 and 6 for each die, if the corresponding check cell does NOT have the value "Frozen"
For i = 1 To 5
If checkCells.Cells(i, 1).Value <> True Then
rng.Cells(i, 1).Value = Application.WorksheetFunction.RandBetween(1, 6)
End If
Next i
roll_counter = roll_counter + 1
ThisWorkbook.Worksheets("Yahtzee").Range("R15").Value = roll_counter
End SubHow to Calculate Scoring and Enter the Score
Now that we have can roll and freeze our dice, the next thing we need to work on is converting those rolls into a score.
To tackle this problem, I first created a table that calculates the maximum possible score for every score-type in Yahtzee based on each roll.
Here is the roll tracker and the score tracker right below it.
For this particular roll, a 1, 2, 3, 4, 4 were rolled.
With this roll, we could either choose to take our 1s, 2s, 3s, 4s, the small straight, or our chance. The score table calculates the total possible score for each score-type.
This makes it easy to transfer over our score to the score sheet, as the calculations are all done in the workbook using formulas.
Once the scores are calculated, we then need to enter them into the score sheet.
One problem I thought was interesting is how to verify if a straight is rolled.
I solved this by sorting the rolls of each dice in ascending order, and used the following formula to determine if a sequential set of 3 or 4 dice in a row existed in the current roll.
=IF(SUMPRODUCT(--(INDEX(I6#,SEQUENCE(ROWS(I6#)-1,1,2,1))-INDEX(I6#,SEQUENCE(ROWS(I6#)-1,1,1,1))=1))>=3, TRUE,FALSE)I6# being the spilled sorted array, and the >=3 determining the length of the sequence.
Entering in Scores
The same basic macro is used for each "Take Score" button. Each button first checks if a score exists for a given score-type, and if none exists, it copies the score from our table and enters it into the score sheet.
Sub take_ones()
game_counter = Worksheets("Yahtzee").Range("R18").Value
Dim scoresheet As Range: Set scoresheet = Worksheets("Yahtzee").Range("E11").Offset(0, game_counter - 1)
Dim score_calculation As Range: Set score_calculation = Worksheets("Calculations").Range("D14")
If scoresheet <> "" Then
MsgBox "Score Already Taken"
Exit Sub
Else
scoresheet.Value = score_calculation.Value
End If
Call New_Turn
End SubThe only exception to this is the Bonus Yahtzee, as you are only able to take this bonus score if you've already scored a Yahtzee.
How to Track Games and Manage Turn Numbers
Now we can almost play through a full game of Yahtzee. The only thing left now is to be able to track what turn and game number we are on
Each time a score is taken, the New_Turn sub is called. This macro unfreezes all of the dice, resets the roll counter, re-rolls the dice, and increments the turn counter. When the turn counter reaches 14, its game over, and a final score is returned.
Sub New_Turn()
' Unfreeze all 'freeze' check cells
Set checkCells = ThisWorkbook.Worksheets("Calculations").Range("D6:D10")
checkCells.Value = False
' Reset roll counter
roll_counter = -1
ThisWorkbook.Worksheets("Yahtzee").Range("R15").Value = roll_counter
' Re-roll dice for new turn
Call Roll_Dice
' Increase turn counter
turn_counter = turn_counter + 1
ThisWorkbook.Worksheets("Yahtzee").Range("R16").Value = turn_counter
game_counter = Worksheets("Yahtzee").Range("R18").Value
If turn_counter > 13 Then
MsgBox "Game Over! Final Score: " & ThisWorkbook.Worksheets("Yahtzee").Range("E31").Offset(0, game_counter - 1).Value
End If
End SubNew Games
This version is also set up to handle 3 games in a row. We have a separate macro for this feature.
Using the following macro, the three games are activated in turn, which highlights the active game on the score sheet, and moves the score entry cells over one.
Sub New_Game()
roll_counter = 0
turn_counter = 0
Set game_counter_range = ThisWorkbook.Worksheets("Yahtzee").Range("R18")
If game_counter_range >= 3 Then
MsgBox "Out of games, please reset all to start another game."
Exit Sub
Else
game_counter_range.Value = game_counter_range + 1
End If
Call New_Turn
End SubResetting All
The final step is to create a button to reset everything. This just resets all of our values back to their original settings and clears all of the scores. Giving us a blank slate to work with and start fresh.
Sub Reset_All()
Set game_counter_range = ThisWorkbook.Worksheets("Yahtzee").Range("R18")
Set checkCells = ThisWorkbook.Worksheets("Calculations").Range("D6:D10")
roll_counter = -1
game_counter_range = 1
turn_counter = 1
ThisWorkbook.Worksheets("Yahtzee").Range("R16").Value = turn_counter
' Clear upper and lower score sections
Worksheets("Yahtzee").Range("E11:G16").ClearContents
Worksheets("Yahtzee").Range("E21:G28").ClearContents
' Unfreeze all 'freeze' check cells
checkCells.Value = False
' Re-roll dice for new turn
Call Roll_Dice
End SubAnd there you have it. How to create a a fully playable version of Yahtzee within Excel. If you would like to see the formulas and macros in more detail, everything inside the downloadable file is open and available to view.