29 Dec 2017

On Christmas day, when no one was looking, I peeked at twitter and saw a tweet about an Excel based game called The Worksheet Saga. My first thought was “oh…it’s based on VBA. I wonder if the code is unlocked?”.¬† Read more to see how this game works and how I hacked it to win ūüôā

 

Origins Of The Game

The Game of Life, also known simply as Life, is a cellular automaton devised by the British mathematician John Horton Conway in 1970.

Further down on the same Wikipedia page:

Conway was interested in a problem presented in the 1940s by mathematician¬†John von Neumann, who attempted to find a hypothetical machine that could build copies of itself and succeeded when he found a mathematical model for such a machine with very complicated rules on a rectangular grid. The Game of Life emerged as Conway’s successful attempt to drastically simplify von Neumann’s ideas.

 

 

Who Built The Game In Excel?

Yifat Shaik built Conway’s game in Microsoft Excel based on a YouTube tutorial by Fly-Tech-Videos. Shaik is a Art Director, Game Designer and Illustrator. Visit her¬†site!

 

 

Below is FlyTechVideo’s YouTube video description:

 

Get The Excel Files!

See Fly-Tech-Videos original Excel file here on his google drive.

Get Yifat Shaik’s version here.¬†I’ll be reviewing her Excel version.

 

 

How Do I Play The Game?

The goal is to have REACTION area all in one color (blue or pink)

Yifat’s¬†Excel file lists the following instructions:

 

I started to get the concept by reading this description and then playing it. It’s a simple concept game that initially doesn’t seem like much but it becomes more and more intriguing as you play it.

Now I’m starting to understand how to play it….but how was it built in Excel?

 

 

How Was The Game Built?

Yifat’s Excel file uses¬†formulas, conditional formatting and VBA. It’s unprotected. Thanks Yifat!

 

Formulas

In the top left cell of range REACTION you’ll see this formula (expand formula bar):

=IF(B2 = 0,
N(“Cell is white”) +
IF(COUNTIF(A1:C3,”=1″) = WhiteToBlue,
1 + N(“Exactly 4 blue neighbours: turn blue”),
0 + N(“Otherwise, stay white”)),
IF(B2 = 1,
N(“Cell is blue”) +
IF(COUNTIF(A1:C3,”=1″)-1 = BlueToWhite,
0 + N(“Exactly 2 blue neighbours: turn white”),
IF(COUNTIF(A1:C3, “=1”)-1 = BlueToPink,
2 + N(“Exactly 3 blue neighbours: turn pink”),
1 + N(“Otherwise, stay blue”))),
N(“Cell isn’t white or blue, must be pink”) +
IF(COUNTIF(A1:C3, “=2”)-1 = PinkToBlue,
1 + N(“Exactly 3 pink neighbours: turn blue”),
2 + N(“Otherwise, stay pink”))))

This long nested IF formula sets each cell to 0, 1 or 2 if the countif formula results match the named range values. The named ranges store the game rule parameters.

 

 

Conditional Formatting

The ACTION and REACTION areas both have several basic conditional formatting rules to set the color of the cells (white, pink, blue and green).

 

 

VBA code

Right click on sheet ‘Game’. Select ‘View Code’. You’ll see six vba sub procedures.

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row >= 2 And Target.Row <= 51 And Target.Column >= 2 And Target.Column <= 51 Then
Cancel = True ‘cancel the right click menu’
If Target.Value = 0 Then
Target.Value = 1
Else
Target.Value = 0
End If
End If
End Sub

In sheet ‘Game’, each time you select a cell the code above checks to see if your selection falls within the ACTION area which is rows 2 to 51 and columns B (or 2) to AY (or 51). If your selection is outside these boundaries then nothing happens. If you select a cell with a value of 0 the code changes it to a 1. If you come back and select it again it becomes a 0.

 

 

Private Sub ResetB_Click()

Private Sub ResetB_Click()
Dim WhiteToBlue, BlueToWhite, PinkToBlue, BlueToPink As Integer
Range(“B2:AY51”) = 0
Range(“WhiteToBlue”) = 3
Range(“BlueToWhite”) = 2
Range(“PinkToBlue”) = 3
Range(“BlueToPink”) = 3
End Sub

Clicking the pink RESET¬†button starts a new game. It resets all cells in range B2:AY51 to 0. Above you’ll notice various named ranges that are set to their default values (3 or 2) according to the game’s default rules.

 

 

Private Sub Random_Click()

Clicking the pink RANDOMISE¬†button sets the ACTION input area¬†¬†Range(“B2:AY51”)¬†¬†¬†to a value of 0. This lengthy sub also randomly fills in a percent (based on your input) of the ACTION input area. I’ll let you read through the code. Hint: the green text is used as comments to describe the code.

 

 

Private Sub Worksheet_Calculate()

This codes informs you that the game is over. I’m too lazy to describe this part. But I became really curious to figure out how to trigger the ‘Game Over’ message without spending countless hours clicking the input cells. See Silly Hack to Win¬†section further down.

 

 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox (“Sorry Right Click is Disbaled for this Worksheet”), _
vbInformation, “Kutools for Excel”
End Sub

This code disables each cells right click menu and informs you of this via a message box.

 

 

Private Sub RuleChange_Click()

If you’re adventurous click RULE CHANGE to change the rules! You’ll see messages like this:

After changing the numbers I played around a bit. Try it! Crazy fun!

 

 

Silly Hack to Win!

When I was reviewing sub Private Sub Worksheet_Calculate() I thought:

I’m not going to play for infinity to see the ‘Game Over’ dialog box appear. The rules state that the game ends when REACTION area is either all blue or all pink. How can I hack it?

My Little Hack

  • Highlight all cells in the ACTION area
  • Click ‘End’ when you see the Run Time error message
  • In formula box replace the 0 with a 1
  • Hold down ‘Ctrl’ key and press ‘Enter’ key
  • Separately click all 4 top and bottom corner cells

It’s all Blue. Game over I win!¬†¬†ūüôā

 

 

Summary

I was impressed by the game’s origins as found on the Wikipedia page. Some serious thought went into this game! The concept of the game gives us something to think about.¬†

The videos from¬†Fly-Tech-Videos were helpful for understanding the concepts and original Excel design. Yifat Shaik’s modification of the game was excellent. Auditing the formulas & code in Yifat’s file was easy due to her clear game design. Read the¬†BoingBoing¬†post about Yifat! The next time someone says that they want to learn VBA I will recommend studying the code in this file. Think of it as ‘learn and play‘!

 

 

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

About 20 years ago I remember seeing a friend working on an Excel spreadsheet. It looked interesting. I thought to myself “What if I could learn more about Microsoft Excel than I know about Chess & Spanish?”

At the time it seemed like a distant possibility but I started learning and I’ve never looked back ūüôā

I’m a Data Analyst at McKinsey & Company.

Check out my YouTube videos and my blog posts.

Post a comment