This is the second blog post dealing with the Monty Hall Problem. In the first post, we laid out all the code and set up the groundwork in order to get a functioning game simulator. In this post we will be building on that skeleton and making a polished game. The first major change we are going to make is allow the player to select doors by clicking on the door they want, no more input boxes. This will lead to a much more natural feel to the game, incidentally, if you have a touchscreen you could then play this game by touching the door instead of clicking. But back to the task, I will show a screen shot of our final destination in order to help you visualize what we’re working toward:
As you can see, this is sleek and polished and much more pleasing to the eye than the previous, drab, build we did and it is also much more user friendly, as you will see.
First we will add the doors we need, I just used clip art for this example. Add six doors and name them “Door1”, “Door2”, “Door3, “Door12”, “Door22”, and “Door32”. We need six because we can’t assign a door to more than one macro, fair enough. But, we also need a method that allows the player to select a new door should they so choose in the final phase of the game. The system I devised is to use three primary doors for the first selection and then a set of secondary doors for the second selection. If this sounds a little confusing just bear with me, all will make sense soon.
Each door will initiate a macro, the macros themselves will assign a variable then call the next subroutine. Doors 1 through 3 will all assign our variable “guess” then call the subroutine goat. If the player clicks door number 1 then “guess” will be assigned the value of 1 etc. It is sometimes easier to just look at the code:
Sub door1()
guess = 1
Call goat
End Sub
Sub door2()
guess = 2
Call goat
End Sub
Sub door3()
guess = 3
Call goat
End Sub
Now simply right click on door 1 and assign it the macro door1, right click door number 2 and assign it to macro door2, etc.
For the second set of doors, we will do the exact same thing except our macros will not be assigning “guess” they will be assigning “newguess”. Add three more subroutines:
Sub door12()
newguess = 1
Call change
End Sub
Sub door22()
newguess = 2
Call change
End Sub
Sub door32()
newguess = 3
Call change
End Sub
Right click on door12 and assign it to macro door12; door22 to macro door22; door32 to macro door32. Phase one is now complete, the next section will set up the spreadsheet and the section following it will update our code.
Make columns A,B and C the width of your doors and rows 1, 2, and 3 the height of your doors. My columns are each 142 pixels wide and my rows are 243 pixels in height. Place door 12 in cell A1, door 22 in cells B1, and door 32 in cell C1. Meticulously place doors 1, 2, and 3 directly above their secondary counterparts and make sure they are the same size, we want the transition to be as smooth as possible. My doors are 2.55” in height and 1.48” wide for reference. If you are having trouble placing them exactly on top of one another you can write a macro to manipulate the images, I won’t touch on that subject here just now. Finally, make sure the doors 1, 2, and 3 are on top of the other doors, all you have to do is right click on the door and then click either send to back or bring to front depending on which door you are on.
Now, add three click art images of a goat. Simply name them “Goat1”, “Goat2”, and “Goat3”. Place each goat behind their respective doors and right click on them, all goats should be moved to the back, behind all of the doors.
You can now add in a game show host image and a speech bubble, color the cells around the doors so that everything looks prettier and add in a game scoreboard. I used the border option on some cells to make my floor look wooden and I chose blue for the wall, check the previous image if you want some ideas of how to position things. My cells behind the doors are a dark gray (black was too suffocating) and I made the borders around my doors a thick white. Add another shape that we will later assign the reset macro to and position that at will. Make a note of the name of the speech bubble because we will also have a macro to change the text in the bubble as the game goes through different phases, this way we can create the illusion that the game show host is speaking to us. That should be all the prep work required, feel free to take your time on this and make it really pop. There is an important lesson here, if you are making spreadsheets for others they need to be user friendly and should ideally be beautiful as well. Depending on what type of person you are, this is either the end of or the beginning of the fun part, moving on…
We should re-write the subroutine “goat” a little bit. Since debugging is no longer needed (for the mechanics portion anyway) I don’t want my macro typing anything into the cells, it will simply assign the random variable “x” to a door and leave it completely hidden from the player. That is the only change to be made to “goat”, the routine will still call “choose”, which hasn’t changed at all, here is the total code we have so far:
Dim guess As Integer
Public newguess As Integer
Public x As Integer
Sub door1()
guess = 1
Call goat
End Sub
Sub door2()
guess = 2
Call goat
End Sub
Sub door3()
guess = 3
Call goat
End Sub
Sub goat()
Randomize
x = Int((3 – 1 + 1) * Rnd + 1)
Call choose
End Sub
Sub choose()
If guess <> x Then Call reveal
If guess = x Then Call reveal2
End Sub
The subroutines “reveal” and “reveal2” have changed considerably but we are still coding on the skeleton of the previous versions. Let’s focus on “reveal”, the special case where “x” does not equal “guess”. The very first thing we will do is hide doors 1 through 3, they have all served their purpose and are no longer needed. The way to do this is to set the shape’s visibility to false like so: ActiveSheet.Shapes(“Door1”).Visible = False. Now comes the trick, recall that in this special case we need to have two very specific doors still closed to the player and only one specific door open/revealed. The door the player chose and the door with the goat. In the previous version we determined which door to open using the equation Z = 6-(x + guess) but now, since all doors are hidden, we shall simply reveal the two doors we need. This is why I chose the naming convention “door12” etc. Have the macro set the visibility of door “Door” + x + 2 to true. Do the same for door “Door” + guess + 2. Since this will be the end of the first phase of the game, I will have the macro change the text in the speech bubble shape to something that prompts the player to make their second guess. I also find it good practice to force Excel to update the screen whenever I am moving around images and that can be achieved easily using the application.screenupdating syntax. Here is that in practice:
Sub reveal()
ActiveSheet.Shapes(“Door1”).Visible = False
ActiveSheet.Shapes(“Door2”).Visible = False
ActiveSheet.Shapes(“Door3”).Visible = False
ActiveSheet.Shapes(“Door” & x & “2”).Visible = True
ActiveSheet.Shapes(“Door” & guess & “2”).Visible = True
Application.ScreenUpdating = True
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “Now, will you change your original guess or stick with the same door?”
End Sub
Of course, before we can update the final phase of the game we have to account for the special case where “X” does equal “guess. The tactic here will be the same, hide the three initial doors and reveal only the two needed for the game’s final phase. The main change here is that we don’t need to paint the cell’s background, that was the previous indicator that a door had been opened but since we have real doors this time we can simply do away with that. We still need to calculate a “y” value however and that procedure hasn’t changed much. There is a small change for the case where “x” and “guess” both equal 2 but it remains functionally the same. Once the macro runs through the lines of code here and establishes a “y” value then we simply make visible the doors we like. In this case we would like doors “Door” + x + 2 to be visible (or “Door” + guess + 2 because they are equal in this case but since “x” is quicker to write than “guess” I went with that) as well as “Door” + y + 2. We want the door corresponding to the value of “y” to be showing because our code has given us a random door that doesn’t contain the goat and wasn’t guessed by the player. Nothing mechanically has changed except instead of painting a cell and implying “don’t select this cell” we are making two doors visible, thereby forcing the player to select between two instead of three. And as before, this ends phase one of the game, update the prompt in the speech bubble and update the screen as well. Here is everything we just talked about in code form:
Sub reveal2()
ActiveSheet.Shapes(“Door1”).Visible = False
ActiveSheet.Shapes(“Door2”).Visible = False
ActiveSheet.Shapes(“Door3”).Visible = False
If guess = 3 Then
y = Int((2 – 1 + 1) * Rnd + 1)
End If
If guess = 1 Then
y = Int((3 – 2 + 1) * Rnd + 2)
End If
If guess = 2 Then
y = Rnd
If y >= 0.5 Then y = 1
If y < 0.5 Then y = 3
End If
ActiveSheet.Shapes(“Door” & x & “2”).Visible = True
ActiveSheet.Shapes(“Door” & y & “2”).Visible = True
Application.ScreenUpdating = True
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “Now, will you change your original guess or stick with the same door?”
End Sub
Now to wrap everything up. We have had the player select a door, had the host open a door that neither has a goat, nor has been selected by the player, and have prompted the player to select a second door, either changing their guess or sticking with the original selection. The original set of doors are now all invisible (and therefore unclickable!) and all that remain is some combination of doors 12 through 32. Since we had the foresight to write the macros and assign variable “newguess” all we need to do is finish up the game. Once the player selects either door 12, 22, or 32 the final variable is set and the game is completed. First, make all the doors vanish! (Doors 1, 2, and 3 are all already gone so all we need to do is make the same happen for the secondary doors). And, perhaps most importantly, make one of the three goats visible. It should be the goat corresponding to random variable “x” and can easily be done by making “Goat” + x visible using the same syntax as for the doors (and just about any image you will ever use in Excel). Update the screen, then make the all-important test to see if “newguess” is equal to “x”. In the event of a win, I decided not to use a message box this time and instead chose to change my text in the speech bubble so that the game show host tells you the outcome, and I have also moved the scoring cells but everything else remains the same, here is what that looks like:
Sub change()
ActiveSheet.Shapes(“Door12”).Visible = False
ActiveSheet.Shapes(“Door22”).Visible = False
ActiveSheet.Shapes(“Door32”).Visible = False
ActiveSheet.Shapes(“Goat” & x).Visible = True
Application.ScreenUpdating = True
If newguess = x Then
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “You win a goat!”
Cells(3, 7).Value = Cells(3, 7).Value + 1
Else
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “No goat for you!”
Cells(3, 8).Value = Cells(3, 8).Value + 1
End If
End Sub
All that remains is to create a macro that resets the whole project but keeps the running tally of wins and losses intact. This is very simple, just make the secondary row of doors and the goats invisible, then make the primary row of doors visible and reset the prompt in the speech bubble. Since variable assignment is done once the player initiates the game we actually don’t need to reset any variables either.
Here is that code:
Sub reset()
ActiveSheet.Shapes(“Door1”).Visible = True
ActiveSheet.Shapes(“Door2”).Visible = True
ActiveSheet.Shapes(“Door3”).Visible = True
ActiveSheet.Shapes(“Goat1”).Visible = False
ActiveSheet.Shapes(“Goat2”).Visible = False
ActiveSheet.Shapes(“Goat3”).Visible = False
ActiveSheet.Shapes(“Door12”).Visible = False
ActiveSheet.Shapes(“Door22”).Visible = False
ActiveSheet.Shapes(“Door32”).Visible = False
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “Pick a door, any door.”
End Sub
I simply used a square box with the text “Play again!” written on it and assigned it to the reset macro. That should cover just about everything, here is what the game looks like in three steps:
And that should complete our Monty Hall game project, we started by getting the mechanics down and ended up with a fully working, polished game that anyone would be proud of. The next blog post will radically change the look of the spreadsheet and automate the whole game in order to gather vast amounts of data, and quickly. Then we can analyze which game strategy works better and do a few more interesting things before I decide to put this whole concept to bed. Stay tuned!
Read the third and final Monty Hall post here!
Download the spreadsheet and find the full code here:
Dim guess As Integer
Public newguess As Integer
Public x As Integer
Sub door1()
guess = 1
Call goat
End Sub
Sub door2()
guess = 2
Call goat
End Sub
Sub door3()
guess = 3
Call goat
End Sub
Sub goat()
Randomize
x = Int((3 – 1 + 1) * Rnd + 1)
Call choose
End Sub
Sub choose()
If guess <> x Then Call reveal
If guess = x Then Call reveal2
End Sub
Sub reveal()
ActiveSheet.Shapes(“Door1”).Visible = False
ActiveSheet.Shapes(“Door2”).Visible = False
ActiveSheet.Shapes(“Door3”).Visible = False
ActiveSheet.Shapes(“Door” & x & “2”).Visible = True
ActiveSheet.Shapes(“Door” & guess & “2”).Visible = True
Application.ScreenUpdating = True
Range(“T1”).Value = “Now, will you change your original guess or stick with the same door?”
End Sub
Sub reveal2()
ActiveSheet.Shapes(“Door1”).Visible = False
ActiveSheet.Shapes(“Door2”).Visible = False
ActiveSheet.Shapes(“Door3”).Visible = False
If guess = 3 Then
y = Int((2 – 1 + 1) * Rnd + 1)
End If
If guess = 1 Then
y = Int((3 – 2 + 1) * Rnd + 2)
End If
If guess = 2 Then
y = Rnd
If y >= 0.5 Then y = 1
If y < 0.5 Then y = 3
End If
ActiveSheet.Shapes(“Door” & x & “2”).Visible = True
ActiveSheet.Shapes(“Door” & y & “2”).Visible = True
Application.ScreenUpdating = True
Range(“T1”).Value = “Now, will you change your original guess or stick with the same door?”
End Sub
Sub door12()
newguess = 1
Call change
End Sub
Sub door22()
newguess = 2
Call change
End Sub
Sub door32()
newguess = 3
Call change
End Sub
Sub change()
ActiveSheet.Shapes(“Door12”).Visible = False
ActiveSheet.Shapes(“Door22”).Visible = False
ActiveSheet.Shapes(“Door32”).Visible = False
ActiveSheet.Shapes(“Goat” & x).Visible = True
Application.ScreenUpdating = True
If newguess = x Then
Range(“T1”).Value = “You win a goat!”
Cells(3, 7).Value = Cells(3, 7).Value + 1
Else
Range(“T1”).Value = “No goat for you!”
Cells(3, 8).Value = Cells(3, 8).Value + 1
End If
End Sub
Sub reset()
ActiveSheet.Shapes(“Door1”).Visible = True
ActiveSheet.Shapes(“Door2”).Visible = True
ActiveSheet.Shapes(“Door3”).Visible = True
ActiveSheet.Shapes(“Goat1”).Visible = False
ActiveSheet.Shapes(“Goat2”).Visible = False
ActiveSheet.Shapes(“Goat3”).Visible = False
ActiveSheet.Shapes(“Door12”).Visible = False
ActiveSheet.Shapes(“Door22”).Visible = False
ActiveSheet.Shapes(“Door32”).Visible = False
Range(“T1”).Value = “Pick a door, any door.”
End Sub
Dim guess As Integer
Public newguess As Integer
Public x As Integer
Sub door1()
guess = 1
Call goat
End Sub
Sub door2()
guess = 2
Call goat
End Sub
Sub door3()
guess = 3
Call goat
End Sub
Sub goat()
Randomize
x = Int((3 – 1 + 1) * Rnd + 1)
Call choose
End Sub
Sub choose()
If guess <> x Then Call reveal
If guess = x Then Call reveal2
End Sub
Sub reveal()
ActiveSheet.Shapes(“Door1”).Visible = False
ActiveSheet.Shapes(“Door2”).Visible = False
ActiveSheet.Shapes(“Door3”).Visible = False
ActiveSheet.Shapes(“Door” & x & “2”).Visible = True
ActiveSheet.Shapes(“Door” & guess & “2”).Visible = True
Application.ScreenUpdating = True
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “Now, will you change your original guess or stick with the same door?”
End Sub
Sub reveal2()
ActiveSheet.Shapes(“Door1”).Visible = False
ActiveSheet.Shapes(“Door2”).Visible = False
ActiveSheet.Shapes(“Door3”).Visible = False
If guess = 3 Then
y = Int((2 – 1 + 1) * Rnd + 1)
End If
If guess = 1 Then
y = Int((3 – 2 + 1) * Rnd + 2)
End If
If guess = 2 Then
y = Rnd
If y >= 0.5 Then y = 1
If y < 0.5 Then y = 3
End If
ActiveSheet.Shapes(“Door” & x & “2”).Visible = True
ActiveSheet.Shapes(“Door” & y & “2”).Visible = True
Application.ScreenUpdating = True
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “Now, will you change your original guess or stick with the same door?”
End Sub
Sub door12()
newguess = 1
Call change
End Sub
Sub door22()
newguess = 2
Call change
End Sub
Sub door32()
newguess = 3
Call change
End Sub
Sub change()
ActiveSheet.Shapes(“Door12”).Visible = False
ActiveSheet.Shapes(“Door22”).Visible = False
ActiveSheet.Shapes(“Door32”).Visible = False
ActiveSheet.Shapes(“Goat” & x).Visible = True
Application.ScreenUpdating = True
If newguess = x Then
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “You win a goat!”
Cells(3, 7).Value = Cells(3, 7).Value + 1
Else
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “No goat for you!”
Cells(3, 8).Value = Cells(3, 8).Value + 1
End If
End Sub
Sub reset()
ActiveSheet.Shapes(“Door1”).Visible = True
ActiveSheet.Shapes(“Door2”).Visible = True
ActiveSheet.Shapes(“Door3”).Visible = True
ActiveSheet.Shapes(“Goat1”).Visible = False
ActiveSheet.Shapes(“Goat2”).Visible = False
ActiveSheet.Shapes(“Goat3”).Visible = False
ActiveSheet.Shapes(“Door12”).Visible = False
ActiveSheet.Shapes(“Door22”).Visible = False
ActiveSheet.Shapes(“Door32”).Visible = False
Sheets(“Sheet1”).Shapes.Range(Array(“Oval Callout 4”)).TextFrame.Characters.Text = “Pick a door, any door.”
End Sub