Chess game with iterative formulas
In Excel there is something to those who we like the formulas that takes
us out of our squares, when you see a circular reference, because a cell
refers to itself in a direct or indirect way, forcing to remove or correct
the circular reference or, as is the case in this game, to accept it to
work by changing the number of times Excel iterates a formula.Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. Excel cannot automatically calculate a formula that refers to the cell — either directly or indirectly — that contains the formula. To use iterative formulas we must change Excel options:
- Enable iterative calculation.
- Maximum number of iterations = 1 in this game.
Microsoft.com: How to change formula recalculation, iteration, or precision
Rules and norms of the game of chess
The first rule that I have in mind is the classic:
Touch-move rule
Using only formulas, how pieces are touched? How do you get the piece touched? How to know the destination square?
The trick has been to insert 64 option buttons from form controls, one for each of the 8x8 = 64 squares of the chessboard, called Square11 to Square88, using the nomenclature of numerical chess notation. They are the 64 forms that are grouped in the Squares, as seen in the image to the right of this text to all forms in the Chessgame sheet.
These radio buttons are not visible through the squares were extended five times and, with a zoom of 20%, the buttons appear to the hinges of the doors of each square box. Each of these buttons takes the size of a square and it is linked to the same cell: Square. Pressing each button generates a different number from 1 to 64, so you know exactly what box is down.
The position of the piece played is stored in the cell: PositionPiece, until you touch a new square that is a valid position of the played piece, if it is found with cell formulas into MirrorSquare and Move.
Chess Game Download
You can download these chess game following this link:
Chess Game Specifications
This game lets you play chess between two humans. No takes a game
engine (impossible with formulas in Excel, it would not be effective and
bad performance).
Is intended to generate a chessboard with 32 initial pieces to review
the moves allowed by the rules of chess and to store games in an Excel
spreadsheet to do the database of games played in the NumericGames
sheet.
The specifications of the chess game are:
- Chess game between two humans programmed in Excel only with iterative formulas, without macros or VBA.
- Chessboard of 64 squares as a resizable form with the ability to flip the board on the side of the white or the black pieces.
- Touch of the pieces and squares with 64 radio buttons linked to a single cell: Square.
- Rules of short and long castling.
- En passant pawn capture.
- Option to view the game from the side of the white or the black.
- Option to view the cell references in algebraic or numeric notation.
- Option to select the style of chess pieces (figurines) or numeric or algebraic style.
- Option to display the last movement of a piece, its possible legal moves and attack positions of the white or black pieces.
- Option to select the chess game number.
- Option to start a new game.
- Panel to display each of the moves of the game in numeric notation. Algebraic notation will be added later.
Yet to be resolved:
- Valid positions in the king check.
- Checkmate detection.
- Promoting pawns.
- Save chess games in algebraic notation.
- Save FEN notation.
- Translate the game into several languages.
I hope you enjoy and make you enjoy such good times as I have been
debugging this chess game.
To further improve this game I wait for your supportive comments.
To further improve this game I wait for your supportive comments.
