In a previous post we have seen how to create the Tic Tac Toe game in Excel using VBA macros. In this post, we will leverage that VBA project to create the Four in a Row game in Excel. The logic and algorithms are more challenging though, as there are many more combinations in the Four in a Row grid. Check out this article and download the file to play Four in a Row in Excel.
Four in a Row Game
Four in a Row, also known as Connect Four, is a two-player connection board game where players take turns to drop a colored coin or disc into a vertically suspended grid, with the goal of having four discs aligned in any of the directions (horizontal, vertical, or diagonal) to win the game. Each player drops discs of a given color, and the discs fall straight down until the lowest available space within a column. Four in a Row’s board is probably one of the most original boards of logic games ever created. While most boards are placed horizontally, Connect Four stands vertically. Despite playing Four in a Row on a computer or in Excel is fun, the game is probably more exciting and enjoyable when played in a physical board though. Find below some good deals to get yours at Amazon.
Board and Player’s Move
The classic Four in a Row game is played on a board or grid with 6 rows and 7 columns, that stands in vertical position with players confronting each other on opposite sides. Excel Four in a Row displays the board in range B4:H9 of the worksheet “Board”. Each cell in that range contains a white circle (font color) within a dark background (interior color), simulating the Four in a Row grid.
The font color of the cell changes to either blue or red when the player or the computer makes the move, thus representing the placement of a disc. The event procedure Worksheet_SelectionChange captures the player’s move whenever a new cell is selected (note that the code is added to the sheet module). It first checks if the selection intersects with the grid using the Application.Intersect method (see code below), and if so, it calls the procedure MoveA to initiate the player’s move.
MoveA loops until the last cell with content in the selected column to add the player’s blue disc. Additionally, it adds a +1 to the same cell in the “Moves” hidden worksheet. That worksheet keeps numbers for each move in order to track the progress of the game (see next).
Checking the Moves
The hidden worksheet “Moves” keeps track of the game by adding a number for each move, mirroring the Excel Four in a Row main board (range B4:H9). This is the same numerical approach used in the Excel Tic Tac Toe game recently posted and explained in the blog. The numerical approach allows to instantly quantify the game and take appropriate action.
The player’s move count as +1, while the computer’s move count as -1. Then we sum rows, columns, and diagonals, using formulas in the hidden worksheet to quickly check the status of the game. A sum of +4 or -4 means a win or lose outcome respectively. That’s checked in the procedure “CheckWin”. A sum of +3 means there is a free cell to make a player’s winning move, and a sum of -3 means the computer can make the move to win. That’s used in the computer’s move algorithm (see next). Excel Four in a Row uses formulas within the hidden sheet (Moves) to sum the values for each group of four cells in a column (B10:H12), in a row (I4:L9), and in diagonal position (O4:O27).
There are 3 groups of four cells for each column, 4 groups of four cells for rows, and 24 groups of four cells for diagonals. In the example above, we see there is only one red disc in column C, and therefore, the third group (starting from the top) has the value of -1, while the two other groups above sum 0 (they are empty). For row 9, the third group starting from the left, has 3 blue discs and 1 red, thus the value is 2 (3-1). We can also see that one of the diagonal groups (D9,E8,F7,G6) sums +3, thus indicating a possible winning move. However, the move is not yet possible as cell G7 is empty. The computer will keep looking at that group and block the winning move when possible. Furthermore, it is going to add G7 as a forbidden move to avoid losing the game, as part of the move algorithm (see next).
Computer’s Move Algorithm
The procedure MoveB contains the code and logic to make the computer’s move. This is based on the sum of numerals in the hidden sheet “Moves”, that can be checked with the WorksheetFunction.CountIf method. The computer’s move algorithm in this version of Excel Four in a Row proceeds in the following order:
- First of all, it checks if there is a winning position in a column, row, and diagonal (in that order), looking for a sum of -3 in the respective formulas. If so, it gets the row or column number with the Find method of the range (containing the formulas) in order to take appropriate action and make the move if possible. For diagonals, it gets the four-cells range of the grid from column N.
- The second step is to check if the player has a winning position in order to block it (if possible). Thus, it checks for the sum of +3 in rows, columns, and diagonals, and blocks the winning position if possible. If not possible, it adds the empty cell that could lead to a winning move to a “forbidden” list array (ForbidArray). That would apply to cell G7 in the example above.
- Otherwise, it makes a random move, avoiding any cell that has been added to the “forbidden” list.
In the below example, the computer first tries to block the move on G6, but, as G7 is empty and cannot move there, it makes a random move that happened to be on F5.
In order to make the game against the computer more challenging, additional rules could be applied, e.g reacting when there are 2 discs in a row, column, or diagonal. The code would be very similar to the one used to react on 3 discs.
When executing the move, the computer adds a red disc to the selected cell on the Excel Connect Four grid in the main worksheet (Board), and adds the number -1 to the same position in the hidden worksheet (Moves). Finally, it calls the procedure “CheckWin” to see if there is a computer win or draw, similar to the check after the player’s move explained earlier.
Fine below the link to download Excel Four in a Row and enjoy playing against the computer. The code can easily be adapted for 2 players instead. And, as explained earlier, additional rules can be added to make the game against the computer more challenging.
No comments:
Post a Comment