Entertain – teach.
(Horace)
If you already had some programming experience in a past life (hello, Basic, Pascal, etc.), then most likely you have already passed the “game writing” stage. However, it is quite possible to shake the old days and stretch the brains. If you have never programmed games, then it is never too late to start this very exciting process. All we need is Excel (any version) and 15-20 minutes of time to get started.
We will train on the well-known in the narrow circles of programmers the game “Life” (Life). It was invented by the British mathematician John Conway back in 1970 based on the work of the legendary John von Neumann, the great-grandfather of all modern computers. If you haven’t come across it before, it’s not a problem, the rules can be explained in half a minute:
The game is played on a large (sometimes even infinite) squared field (“universe”). As you can imagine, Excel is perfect for this 🙂
At one moment in time, each cell can be in two states − alive (let’s denote it with some icon or just one) or dead (empty). The initial state of all cells in the game is called first generation.
If we take a block of cells 3×3 with the current cell in the middle, then there are 8 neighboring cells around it. The further fate of the cell depends on how many living cells (N) will be in this surrounding area. There are several options:
- If the cell was empty (dead), but it has exactly 3 living neighbors, then life is born in it.
- If the cell is alive, but it has less than 2 neighbors, then it dies of loneliness.
- If the cell is alive, but it has more than 3 neighbors, then it dies from overpopulation.
- If the cell is alive and has 2-3 neighbors, then the cell continues to live.
Here, in fact, are all the rules. You can even say that it is wrong to call all this a game, because. there are no rivals in the usual sense. You set up the first generation, start the process, and then just watch your “colony” develop over several generations.
However, do not underestimate the deceptive simplicity of this logic – the number of combinations, game scenarios and the variety of figures in such a game universe is striking in its diversity and truly endless. In mathematics, such models are called cellular automata. And the most interesting thing is that you can implement such a model in any version of Excel literally in 20 lines of code.
Come on.
Step 1. Preparing the play space
Let’s create three sheets in the new workbook:
- game – this will be the main sheet of the game, where we will observe the development of our “colony”
- Next – this leaf will form the next generation, which will then replace the current one
- start – on this sheet we will set the initial configuration, i.e. first generation in our game
On each sheet (you can select them in advance by holding the Shift or Ctrl, so as not to repeat the same actions three times), mark the playing field, for example, 30 by 30 cells. Subsequently, the size of the field can be adjusted in accordance with your appetites and the power of your PC:
To the list start mark the first generation with units in any desired way:
Step 2. Write a macro
Now it’s time to uncover our VBA and write a macro that will do all the work, namely:
- Copy first generation from sheet start per sheet game.
- Pass through the cells of the playing field on the sheet game and check surrounding neighbors (block 3×3) for each of them.
- Depending on the results of the check, mark on the next generation sheet Next the same cell as live (1) or dead (empty).
- Copy the resulting new generation from the sheet Next instead of the current one on the game sheets game.
- Repeat steps 2-4 several times, replacing one generation with another and displaying on the screen the changes in our “colony”.
First, open the Visual Basic Editor on the tab developer (Developer). If such a tab is not visible, then it will first need to be displayed through File – Options – Ribbon Setup (File — Options — Customize Ribbon)by enabling the corresponding checkbox.
In the editor window that opens, create a new module using the menu command Insert – Module, and then copy and paste our macro code there:
Sub Life() Dim cell As Range, n As Integer, i As Integer Set rGame = Worksheets("Game").Range("B2:AE31") Set rStart = Worksheets("Start").Range("B2:AE31") Set rNext = Worksheets("Next").Range("B2:AE31") Set wNext = Worksheets("Next") rStart.Copy Destination:=rGame For i = 1 To 50 rNext.ClearContents For Each cell In rGame.Cells n = WorksheetFunction.CountA(cell.Offset(-1, -1).Resize(3, 3)) - cell.value If cell = "" And n = 3 Then wNext.Cells(cell.Row, cell.Column) = 1 If cell = 1 And (n = 2 Or n = 3) Then wNext.Cells(cell.Row, cell.Column) = 1 If cell = 1 And (n 2) Then wNext.Cells(cell.Row, cell.Column) = "" Next cell rNext.Copy Destination:=rGame Next i End Sub Now let’s break it down line by line for clarity:
Since we will have to reference and work with the ranges of the game space (B2:AE31) several times in the code on each of the three sheets of the book, it makes sense to immediately arrange them as variables. This is done in a block:
Set rGame = Worksheets("Game").Range("B2:AE31") Set rStart = Worksheets("Start").Range("B2:AE31") Set rNext = Worksheets("Next").Range("B2:AE31") At the same time, we also create a variable wNext, which refers to the entire next sheet – this will also be useful to us in the future:
Set wNext = Worksheets("Next") Then, before starting the game, we must transfer the first generation from the sheet start per sheet game. This is done with a direct copy command using the already created variables:
rStart.Copy Destination:=rGame
Since we want to scroll in our game not one, but several (for example, 50 to start) generations, then further actions are enclosed in a loop:
For i = 1 to 50 ... Next i
And inside this cycle, we, firstly, first clear the workspace on the sheet Next to form the next generation:
rNext.ClearContents
And, secondly, we launch a nested cycle of passing through all the cells of the game universe on the sheet gameto check each of them – this is implemented by looping through the collection:
For Each cell in rGame.Cells ... Next cell
The link to the next checked cell will be stored in the cell variable. For this cell, we need to first build a 3×3 neighborhood with it in the middle. This is done with the construct:
cell.Offset(-1, -1).Resize(3, 3)
Here’s the method Offset(-1,-1) virtually shifts the current cell being checked one row up and one column to the left, and then the method Resize(3,3) again, virtually stretches that one cell to the new 3 by 3 dimensions:
To count the number of filled cells in the resulting neighborhood, the worksheet function is used COUNT (COUNTA), which in VBA can be called using an object WorksheetFunction. Thus, the number of live neighbors in the 3 by 3 area surrounding the current cell is obtained by the expression (remembering to subtract the current cell from the resulting number):
n = WorksheetFunction.CountA(cell.Offset(-1, -1).Resize(3, 3)) - WorksheetFunction.CountA(cell)
Next, we need to check the received number of neighbors and mark the current cell on the next generation sheet as live or dead, according to the rules of the game. This performs a block of three checks:
If cell = "" And n = 3 Then wNext.Cells(cell.Row, cell.Column) = 1 If cell = 1 And (n = 2 Or n = 3) Then wNext.Cells(cell.Row, cell.Column) = 1 If cell = 1 And (n 2) Then wNext.Cells(cell.Row, cell.Column) = ""
When the loop through the cells is completed, then the generated next generation from the next sheet must be copied to the place of the current one on the game sheet – we do this with a familiar construction:
rNext.Copy Destination:=rGame
That, in fact, is the whole logic.
It remains to return to Excel on the sheet game, launch our game via the tab Developer – Macros (Developer — Macro) and enjoy the process of development of our colony:
- What are macros and how to program them in Microsoft Excel
- Game of Life Handbook – LifeWiki