Excel games can be created with the power of functions and macros. Creating a game inside excel is something no one thinks about except the ones who are passionate about excel.
Let’s create one such excel games called “Roll the Dice”
Excel Games called “Roll the Dice” Using Functions and Macro
Dice rollers are needed for lots of games … If you want to have one always at hand then nothing can match the Excel dice roller which allows you to toss virtual dice.
Take a chance or risk…. Toss the dice… In all cases you are about to learn some amazing Excel tips and tricks that can be applied to so many work situations… far beyond this Dice Roller game.
In this tutorial I show you how to create a beautiful Dice roller…
I’ll be using functions, formatting, macros, protection and lots of tricks along the way.
Tossing dice returns 2 random numbers between 1 and 6
Each facet of a dice will show a circle in one or more of seven possible positions.
So, let’s have a close look at each circle:
The upper left & Lower Right positions are common between numbers
2,3,4,5,6
The upper Right & Lower Left positions are common between numbers
4,5,6
The Middle Right and Middle left positions appear only with number six
The middle center position is common between number 1, 3, 5
So, we can represent the positions in a grid 3 columns and 3 rows
Our goal is to create for each die a randomizing function that returns a number between 1 & 6
In a grid of 3 by 3 we want one or more circle to appear in the position corresponding to the random numbers
We’ll be using a simple trick to convert a character into a circle.
We’ll apply some formatting and then create a Macro to trigger the dice rolling.
If we get the same number on either side, that’s a double rolling with a probability of 6 out of 36 different outcomes … In such case a congratulations message will pop up showing the name of the returned combination.
Now let’s build our project from ground up in Excel.
Here is my start file, you can download the exercise file and follow along by clicking here below
I start by selecting columns A ► P and I adjusted the width to 11.92 (150 pixel)
I select columns Q ► Z and adjust the width to 2.75 (40 Pixel)
I select Rows 1 ► 10 and adjust the height to 24.00 (40 Pixel)
In the range AA3 ► AC5, which is a grid of 3 by 3, I write the numbers that should trigger a circle at each position of the grid
In the range Z12 ► AA17 we have a list of names of the 6 double rolling options
Creating the Randomizing Functions:
In Cells R2 & V2 I create 2 randomizing functions using either Randbetween or Randarray:
=Randbetween(1,6)
or
=RANDARRAY(1,1,1,6,1)
The Randarray is longer and does not provide extra benefit in this situation.
Randomizing function updates when we hit F9 returning different combinations of numbers from 1 to 6 every time.
Returning numbers to the dice grid
To start simulating dice we need to compare the result of the randomizing functions to the model we created in the range AA3 ► AC5 which was based upon interpretation to the circle positions,
Start by selecting the range R3► T5 and search for the number in R2 (returned by the first randomizing function) in cell AA3 (in the same relative position) and hit ► CTRL + ENTER
=SEARCH($R$2,AA3)
The search function will return a number if there is a match or a #Value! error if the number does not exist.
Hit ►F2 to put the function in the Edit mode.
To convert the Number and Value errors to Trues and False, wrap the Search function in an ISNUMBER function, then hit ► CTRL + ENTER
=ISNUMBER(SEARCH($R$2,AA3))
Hit ►F2 to put the function in the Edit mode one more time.
All these functions will then be the first argument of an IF function ► If I get a TRUE I need to return letter ”n” (lower case in double quotes) otherwise I want nothing hit ► CTRL + ENTER
=IF(ISNUMBER(SEARCH($R$2,AA3)),”n”,””)
Now repeat for the range V3 ► X5
Testing the functionality
Test the 2 randomizing functions by hitting F9 ► every time a different combination is returned and the corresponding letters “n” appear in the matching positions in the 3 by 3 grid
Changing numbers to circles
Now we want to change all the occurrences of letters “n” to circles to simulate real dice:
Select the 2 ranges R3:T5 & V3:X5 while pressing CTRL ► Change the Font Type to Webdings and the font size to14 pt ☺
Vertical and Horizontal alignment
Now the next step is important for a realistic appearance, I want to adjust the Vertical and Horizontal alignments of each circle as follows:
Identifying Double Rolling
If by coincidence the 2 randomizing functions return the same number ► That’s a double rolling with a probability of 6 out of 36 possible outcomes. Each double rolling has a name (I googled these names) and I want a message to be displayed if a player gets one of these 6 double rolling matches.
Preparation Function
In cell AA19 ► I create an IF function that compares the result of the 2 randomizing functions, if equal ► A VLOOKUP function (we can also use an XLOOKUP) will extract the name of the Double Rolling combination. Otherwise it returns nothing.
=IF(R2=V2,VLOOKUP(V2,Z12:AA17,2,0),””)
Building the Message
In the next step, I prepare the message that will pop up in case of double rolling:
In cell AA21, an IF function will look at cell AA19: If it is Blank then it returns a Blank, otherwise we’ll be combining some text to the result of the VLOOKUP function.
To get the message on 2 lines I want to insert a “New Line” in the message, which is done by using CHAR(10)
=IF(AA19=””,””,”Congratulations! Double Rolling “&CHAR(10)&AA19)
The Final Appearance
Now let’s prepare for the final appearance, note that I provided 2 formatted Square Shapes and a Dice feel free to modify or replace them with your own elements.
Very Important preparation step: On the View TAB uncheck Gridlines. Otherwise ► cell boundaries will appear in the dice.
Paste Special
Select the range R3 ►T5 (having the Search functions) and Copy
Scroll to the left side of the worksheet (say B6) and click on the down arrow of the Paste command to select Paste Special as Linked Picture (the lower right option)
This is a dynamic picture linked to the result of the first Randomizing function in cell R2
It is floating on top of the grid ► So you can Move it and you can resize it.
Create an outer Boundary
Bring one of the 2 Squares with no fill (whether by copying or cutting) paste it in the left side of the worksheet and rotate it (using the Rotation handle at the top).
Adjust the Size, Position and Rotation angle of the linked picture (representing the first die) so that it aligns perfectly with the Square shape. Once you are satisfied with the position, ► send the picture behind the shape:
Picture Format Tab ► Send Backwards ► Send To Back
With the picture and Shape still selected Click on the Down arrow for grouping and Group them.
NB: if you have difficulty selecting elements, you can bring the selection pane ALT + F10 also available on the Format Tab, to select the objects you want.
Now that the 2 objects are grouped you can move them and rotate them together.
Repeating for the second die
Repeat the above steps for the range V3 to X5 linked to the second randomizing function:
Copy and paste special, use the second frame, resize, align, rotate and group as we did with the first one.
Test changing the numbers by recalculating the Randomizing functions ► by hitting F9 multiple times. If by coincidence, the numbers are identical then it’s a double rolling.
Message for Double Rolling
Below the dice, create a Textbox to display the message in case of double rolling:
Insert Tab ► Textbox ► Click and drag to position. With the textbox selected, hit F2 (edit mode), the blinking cursor appears in the Formula Bar ► type an = sign then click on cell AA21 (where we created the IF function, the hit ENTER.
Test by hitting F9 to make sure the correct name of the double rolling combination is returned.
Format the font of the text box to your liking. I used Impact font 24pt and I removed the outer border (Shape Outline) of the textbox.
Creating Macro
Our dice generate new combinations of numbers every time we hit F9. It will be better if we bring a picture of a Die and we use it for triggering the randomizing functions instead of hitting F9.
To do that I’ll be recording a simple Macro
Click on the Developer Tab (Adding the Developer tab to the Ribbon) and in the “Code” group to the left make sure that “Use Relative Reference” is highlighted.
Then click on “Record Macro”
The “Record Macro” Dialog box opens ► give a name to the macro (I named it “Dice”) then hit OK. You are now in the process of recording.
Click on the Formulas Tab then click on “Calculate Sheet” in the Calculation group to the right side of the Formulas Tab.
Now to stop the recording, go to the developer tab and click on “Stop Recording”
Bring the picture of the Die (provided) or any picture of your choosing, position, resize and rotate in the desired place.
To attach the macro to the picture, Right Click and select “Assign Macro” ► the Assign Macro dialog box opens ► Select the macro you created.
Test by clicking on the picture ► With every click a new combination of numbers is returned.
Protecting the Project
The last step of this project is to avoid any changes by protecting the worksheet
Click on the Review Tab ► Click on Protect Sheet ► In the protect worksheet dialog box, Uncheck “Select locked cells” and keep the next option “Select locked cells” checked.
Providing a Password is optional, if you do you will have to confirm it. Hit OK and know you can enjoy your Dice Rolling game.
Have a Dice Day!! ☺ ☺ ☺
You can also watch the video tutorial on my YouTube channel where I have hundreds of interesting tutorials. For this tutorial click here below
