Classic game! In this post we see how to create the Tic Tac Toe game in Excel using VBA macros. Despite its simplicity, Excel Tic Tac Toe is a good example to understand logic and algorithms as the basis of AI behind computer's moves. Play Excel Tic Tac Toe and try to beat the machine!
Tic Tac Toe Game
The Tic Tac Toe game (American English), also known as noughts and crosses in British English, Xs and Os in Irish Englsh, and tres en raya in Spanish, is one of the oldest board games still played today. It confronts two players who mark empty spaces in a 3x3 grid with either Xs or Os, with the goal to align three of each marks horizontally, vertically, or diagonally, to win the game.
The logic of the game is quite simple. If both players make perfect moves, they force a draw. There are many simple methods and algorithms to make the right moves. One of the most documented and used is the minimax algorithm. This Excel version of Tic Tac Toe uses a slightly different algorithm based on a numerical notation of +1 and -1 for each placed Xs and Os respectively.
Player’s Move Execution
The layout of Excel Tic Tac Toe is a simple 3x3 grid represented by the Range B4:D6 in the main worksheet (Board). Two Shape objects are added as buttons to start the game, by assigning the respective start procedures to each of them (StartA and StartB in Module1).
The event procedure Worksheet_SelectionChange captures the player’s move when changing the selected cell or range (note that the code is added to the Board sheet object module). Then it checks if the selection falls within the Excel Tic Tac Toe 3x3 grid, i.e. if the row is between 4 and 6, and column between 2 and 4 (columns B through D), using the Row and Column properties of the target cell. If so, it calls the procedure MoveA in Module1 (that’s the only standard module in this VBA project).
Another way of checking if the cell is selected within a range is using the Application.Intersect method, which in this case has a flaw as it could wrongly capture the move when selecting the whole row or column. That would be:
The procedure MoveA first checks if the game has started (GameStart is True) and the target cell is empty. In that case, it adds an X to the target cell and the number 1 to the same position in a hidden worksheet (Moves). The latter represents the numerical strategy that governs the move checks and computer’s move algorithm in Excel Tic Tac Toe (see next). Then it calls the CheckMove routine to see if the move creates a win or draw, and finally calls the MoveB procedure (computer’s move) if the game is not over.
Move Checks Numerical Strategy
The hidden worksheet Moves keeps track of the game by adding a number for each move in the same position or cell of the Excel Tic Tac Toe game board (range B4:D6). This is a simple numerical approach that allows to instantly quantify the game and take appropriate action.
The Xs count as +1, while the Os count as -1. We can then sum rows, columns, and diagonals, to quickly check the status of the game. A sum of +3 or -3 means X or O wins respectively. A sum of +2 or -2 means there is a free cell to make a winning move by X or O respectively. That’s used in the computer’s move algorithm (see next).
Taking advantage of Excel capabilities, Excel Tic Tac Toe uses formulas within that hidden sheet (Moves) in range B7:D7 to sum the values by column, E4:E6 to sum rows, and E3 and E7 to sum the diagonal trios.
The procedure CheckMove checks if the move leads to a win, lose, or draw, by finding the values +3 or -3 within the range B3:E7 in the worksheet Moves (this range includes both board and formula cells). It does so using the WorksheetFunction.CountIf method (see more about the WorksheetFunction Object here). It also counts the number of items within B4:D6 with WorksheetFunction.CountA; if the count is 9 the game is over with a draw.
Computer’s Move Algorithm
The procedure MoveB contains the code and logic to make the computer’s move. The algorithm in this version of Excel Tic Tac Toe consists of the following steps:
First of all, it checks if there are 2 Os in line to make an O’s winning move, otherwise, it checks for 2 Xs in line to block the X’s winning position. That can easily be checked using the formulas in the worksheet Moves (explained earlier) along with the CountIf function. To know the exact row and column of the target position we use the Match function in the procedure CheckMoveB.
Next, if there are no +2 or -2 (as it happens at the beginning of the game), it follows the logic below in that consecutive order to make the right move:
1. It takes the centre position if free (defensive move)
2. It takes a corner position if free, unless corner or edge advantage
3. It takes any other free position
When making the move, it adds an O to the selected cell on the Excel Tic Tac Toe grid (worksheet Board) and adds the number -1 to the same position in the hidden worksheet (Moves). Finally, it calls the CheckMove procedure to see if there is a computer win or draw, similar to the check after the player’s move explained earlier.
The proposed algorithm in this version of Excel Tic Tac Toe makes nearly perfect moves and is very difficult, if not impossible, to beat. Making slight changes to the logic and adding more random decisions would probably make the game more fun to play.
No comments:
Post a Comment