This file is available in our store. Contents:
Purpose
Games can be an addictive distraction, and can also be some of the most effective teaching tools available. My purpose for developing games is as a showcase for interactivity and elegant design. The games are for solo play, but some have two-player competition to add interest and fun. They have compact play areas, tactile controls, and accessible support information. Each has lessons to be learned on design and VBA coding.
Poker
The idea of an Excel poker game raises several intriguing challenges; how to manage the deck as it is dealt out, how to enable discard and replace, how to evaluate hands, and of course, how to design the cards and the table to look good. Without nice looking cards it will not last long, so I’ll begin with that.
The Cards
For the card suits, I first looked in the symbol-designated fonts. The Symbol font has the four suit symbols in ASCII codes 167 to 170. To use this font for the cards would make the royal letters Greek, and character-level font setting is not an option. This made me turn to Unicode symbols for the suits. Exploring the Symbol picker, Alt-N,U, I didn’t find then in Calibri, but I found them in Arial, Hex codes 2660, 2663, 2665, and 2666. They display nicely in Calibri or any other non-symbol font. So I can paste them into standard font cells and they look great.
Now to create the cards, I make each one three characters, with the value and the suit separated by a space, and substituting T for 10. For hearts and diamonds I use two preset conditions; “Cell Value contains ‘♦’” and “Cell Value contains ‘♥’” to turn the font red.
The Deck
The “deck” begins as a “pack” of cards, and I’ll use these two terms to distinguish the full pack from the remaining undealt deck. In other words, Pack – Dealt = Deck. The critical point is that there can be no duplicate cards dealt. There must be a list of which cards have been dealt, then a process to eliminate them from the pool for the next randomly selected card.
The deck is maintained in three adjacent columns, beginning with a static range of 52 cells with every card in the pack. Next to that is a single-column table (Dealt[Dealt]) to hold the dealt cards, filled by the deal macro. This is followed by the deck column, calculated using an array formula.
Deck =FILTER(Pack,ISNA(MATCH(Pack,Dealt[Dealt],0)))
This logic I owe to a post by Keith at extendoffice.com on how to exclude one list from another. It is really quite ingenious, as I’ll explain now. (See https://www.extendoffice.com/documents/excel/4076-excel-exclude-one-list-from-another.html )
Normally we are using MATCH to find the index of an item within a list of items, as the arguments suggest: MATCH(lookup_value, lookup_array, [match_type]). With match_type 0 for exact match, if the lookup value is not in the array, MATCH returns N/A.
Here what we are interested in is not the numeric result of MATCH but a Boolean state of the intersection of two arrays, Pack and Dealt. Dealt exists within Pack. What we need is the part of Pack that does not intersect Dealt. This is called a symmetric difference, a.k.a. disjunctive union, denoted as Pack Δ Dealt – just FYI! (https://en.wikipedia.org/wiki/Symmetric_difference)
The MATCH trick is to look up Pack in Dealt. That sounds weird – Pack is bigger than Dealt! To test this, I pick an empty column and enter =MATCH(Pack,Dealt[Dealt],0). This returns an array with all 52 Pack members, with index numbers for those found in Dealt, and N/A for members not found in Dealt. That set of N/A Pack members is precisely what I need. FILTER of the Pack for ISNA of the MATCH array then returns the undealt cards.
As an array formula, it is entered in a single cell named Deck and spills down the column. The entire array is referenced as Deck#. A single cell then picks a random card from the deck:
NextCard =INDEX(Deck#,RANDBETWEEN(1,COUNTA(Deck#)))
For calculation control, I use Worksheet_Activate event to set manual calculation mode. Then in the deal macro, I use the Range.Calculate method to update Deck and NextCard before copying the value.
A bonus feature of deck array management is a display of the dealt and undealt cards by suit as a learning aid. This is done by filtering and transposing the respective columns, presented in the play area via linked image (see Camera Tool in Chapter 3 Formatting).
Top grid =IFERROR(TRANSPOSE(FILTER(Deck#, RIGHT(Deck#,1)=DeckSuit)),"") Bottom grid =IFERROR(TRANSPOSE(FILTER(Dealt[Dealt], RIGHT(Dealt[Dealt],1)=DeckSuit)),"")
Game Play
Two players each have five cards on the table, separated by the Deal button. The cards are covered by transparent shapes that give them a slight 3D appearance, and serve as toggle buttons to hold and unhold the cards. When a card is clicked, a hold cell is set true and the card becomes shaded. Unshaded cards are discarded and replaced in the next deal, which only calculates Deck and NextCard for unheld cards. The cell DealCount tracks up to three deals, and is reset to 0 when the hand is over.
Processing is done by multiple Subs working together like a dealer team. It begins when the player clicks the Deal button, which calls Sub DealButton(), the team boss. If DealCount is 0, DealButton() prepares the table for a new hand. Think of it like collecting all the cards and shuffling the pack. DealButton() then hands the pack to Deal(dc), along with dc, the DealCount value, before incrementing DealCount.
While DealCount is 0, Deal(dc)’s job is to deal ten cards, one at a time, alternating players. When the initial ten cards are dealt, action returns to the player, who looks at both hands and selects cards to hold.
The implicit rule is that at least one card in the hand must be held in order to replace the others. If none are held when the Deal button is clicked, all holds are set for that hand. If one player has a hold and the other doesn’t, the player with the hold can continue to deal. If both players’ holds are all set or none set, the hand is over and scored. This governance is performed by DealButton().
Presuming that at least one player has at least one card held, but not all five, Deal(dc) is called in to replace the discarded cards. In this case, the deal is by player precedence. That is, player 1 gets all discards replaced, followed by player 2.
EndHand() is called in after three deals, or if the all-or-none hold condition described ends the hand. EndHand() sets all holds, resets DealCount to 0, and sets the cell ShowHand TRUE. ShowHand enables the scoring formula and winner highlighting to appear. EndHand() in turn calls RecordScore().
RecordScore() is enabled by a checkbox in the play area. If the checkbox is set, RecordScore() will add the result of the hand to the table PokerScores.
These Subs are performing related tasks, and could be combined. Their separation is a convenience. When there are conditional paths in a main Sub, I like to have it call another Sub to focus purpose and for readability. This is similar to how I decide when to use Name Manager to break down complex formulas. The Deal(dc) Sub is shown here.
Sub Deal(dc As Integer) ' called by DealButton sub Dim Player, CardNum As Integer Dim Calc, Hold, Card As String Application.ScreenUpdating = False Select Case dc 'dc passes deal count Case 0 'on first deal, deal by card then by player For CardNum = 1 To 5 For Player = 1 To 2 Card = "Card" & Player & CardNum [Deck].Calculate 'refresh Deck = "not dealt" With [NextCard] 'NextCard = random pick of Deck .Calculate '(like shuffle) .Copy 'copy the random card End With 'paste random card to card cell Range(Card).PasteSpecial (xlPasteValues) 'paste same card to Dealt Range("Dealt[[#Headers],[Dealt]]").Select If Len(Selection.Offset(1, 0)) > 0 Then _ Selection.End(xlDown).Select Selection.Offset(1, 0).PasteSpecial (xlPasteValues) Next Player 'alternate players Next CardNum 'repeat for each of 5 cards Case Else 'after first deal, deal by player then by card For Player = 1 To 2 For CardNum = 1 To 5 Hold = "Hold" & Player & CardNum Card = "Card" & Player & CardNum If Not Range(Hold) Then ' skip if Hold cell is true [Deck].Calculate 'same as first deal With [NextCard] '... .Calculate '... .Copy '... End With Range(Card).PasteSpecial (xlPasteValues) Range("Dealt[[#Headers],[Dealt]]").Select If Len(Selection.Offset(1, 0)) > 0 Then _ Selection.End(xlDown).Select Selection.Offset(1, 0).PasteSpecial (xlPasteValues) End If Next CardNum 'next unheld card Next Player 'next plyr End Select Application.CutCopyMode = False [Dealer].Select End Sub
Scoring
Traditional poker scoring ranks ten hands, from royal flush down to high card. If one player has a higher-ranked hand, they win the hand. If both players have the same-ranked hand, then tiebreakers come into play. Various methods are used to judge each five-card hand by both value and suit.
Hand Evaluation
The ten ranked hands each have a condition to meet. Pair, two pair, three of a kind, full house and four of a kind are all based on counts of the card values; flush is based on the count of card suits; straight is based on sequential values.
The first step is to parse and tabulate the card values and suits so they can each be counted. This is done for each player by picking the left and right character of each card, creating two arrays.
Hand1Values =LEFT(Hand1,1) Hand1Suits =RIGHT(Hand1,1)
These are the basis of the evaluation, which suggests that the five-column range directly below them will be a logical and intuitive data collection area.
The five columns are divided into three for values and two for suits. The first is a list of values from 2 to A, followed by value count and rank columns. For rank, since the values are mixed alpha/numeric, RANK cannot be used; instead, MATCH assigns ascending numeric value to the card values as listed. The last two columns are a list of suits and a suit count.
Most scores use the counts, and the rank for tiebreaking. For example, three of a kind is COUNTIF(Values1,3), a flush is COUNTIF(Suits1,5). The straight requires a different method.
To identify a straight, the value counts are concatenated into a 13-character string. Scoring can then look for “1111*1” for an A2345 straight, or “*11111” for a royal straight, or “*11111*” for any other straight. The string is also used in tiebreaking.
Hand Scoring
Scoring each of the game hands against the ten ranked hands is a logic exercise. Combined conditions use PRODUCT as AND, and SUM as OR, so that the end result is 0 or 1 in every case. In the table below, hand names are substituted where they have already been defined.
Each COUNTIF evaluation can be only 0 or 1, except pair can also be 2, so one and two pair are converted to Boolean by equalities that return TRUE or FALSE. These are multiplied by 1 in order that all cases return 1 or 0. The final rank is high card, when no other condition is met, similarly evaluated as Boolean and converted to binary.
Some game hands can match multiple rank hands. A royal flush is also a straight flush, a flush, and a straight; a full house is also a three of a kind and a pair. This does not matter because we’re only looking for the first MATCH from top down. Here, low score wins because the lower the MATCH result, the higher the rank. SIGN returns -1, 0 or 1, so I use SIGN(difference)+2 as index to CHOOSE among three outputs; player 1 or 2 as winner, or 0 as tie.
Tie Breaking
It’s very common to have the same hand rank, and tiebreaks are a routine part of scoring. At the top, a royal flush tie is a deadlock. A straight tie can be won by highest card, and is deadlocked if the straights match. Four or three of a kind cannot be tied. With flush or card, you could still be tied after five tiebreaks, but I’ll stop at three.
Most tiebreaks are determined by searching the count string for the relevant marker; a straight is SEARCH(1111,String1), picking up the rank of the lowest card of the straight; four of a kind is SEARCH(4,String1) for the foursome rank. Two pair first finds the rank of the higher pair by SEARCH(2,String1,SEARCH(2,String1)+1), then the lower pair by SEARCH(2,String1).
For flush and card, and for tied pairs and two pairs, individual cards are compared by MAX(Rank1), then by MAXIFS(Rank1,Rank1,"<"&MAX(Rank1)). Pair and two pair use MAXIFS(Rank1,Values1,1) to find the top non-pair value.
Unlike the high hand where lowest value wins, all tiebreaks are highest value wins. Each one is converted to winner = 1 or 2, or 0 = tie, if and only if the higher level is tied. The winner of the hand is then the max of the high hand and three tiebreak cells.
Winning
After DealCount runs out, the winner of the hand is announced and highlighted. Each player’s header gives the name of the hand. This is only given at the end of the hand, to help the player learn to recognize the hands while playing. The winner’s message adds “wins” after, and “high” before, if result of a tiebreak.
Player1 header =IF(ShowHand,CHOOSE(Winner+1,"",IF(High,"","high "),"")& INDEX(Hands,IFNA(MATCH(1,Match1,0),10))& CHOOSE(Winner+1,""," wins",""),"")
Player2 header =IF(ShowHand,CHOOSE(Winner+1,"","",IF(High,"","high "))& INDEX(Hands,IFNA(MATCH(1,Match2,0),10))& CHOOSE(Winner+1,"",""," wins"),"")
Recording
Table PokerScores records each hand’s results with a time stamp, if the checkbox is set. A row above the table is filled with formulas, which is copied and pasted as values into a new table row. This enables some interesting statistics to be displayed in the play area. Winning % per player and per named hand are shown, with the number of hands recorded. This is further education for poker play. Score history is manually reset by deleting table rows. Because the table has some formula columns, it has a message, “to delete history, delete all table rows but one, in that one delete only the white cells.”
Design Features
The game includes the play area, calculation areas, documentation and the VBA code all on one sheet. The play area fits neatly within the screen, including the tabulation, scoring, and deck image, and various elements can be hidden for focused play. Subtle icons open and close row groups, and hide and unhide the deck image. Another jumps to the PokerScores table.
There are two types of hiding here. One is within the “home” or play area, where elements directly linked to game play are normally displayed, with buttons to hide them as desired.
The other type of hiding is the processing and recording ranges that are far off-screen, where you wouldn’t even know they were there. These have click navigation to avoid clumsy scrolling. One click to get there, and a back arrow button to return home. These areas are placed diagonally below and right of the home, so to be independent of its column and row sizes. DeckZone is accessed by click on the deck image, PokerScores by click on the counting marks icon.
Dice
There are many games one could make with dice, from Yahtzee® to craps. This game is none of those, it is just a simple practice game and a demo of how to simulate dice in Excel. To make it somewhat fun, the number of rolls per game can be set, and the “competition” mounts with each roll.
The Initial Answer
The interesting feature of the dice is the face with an array of dots to show the value. As I worked on it I found a number of ways to calculate the dots. In the interest of revealing the development thought process, the graphic below shows how I initially worked out how die value and pip position relate and can be determined mathematically.
To combine these conditions into a single formula to display the proper face is possible, but rather messy. Here was my penultimate solution:
Formula in each of nine cells per die: =CHOOSE(Die,Face1,Face2,Face3,Face4,Face5,Face6)
Face names as below defined in Name Manager
This also required a way to name a cell “Pip” which could represent each of the nine dot positions in turn, which was even more messy. In the end, the trick is to convert a number between 1 and 6 to a 3x3 array, so eventually my mind turned to arrays.
The Final Answer
Each die face is set to an array constant of 0’s and 1’s where 1 represents the dot positions. Columns are separated with commas and rows with semicolons. Expressing each face is a graphical task; as you look at the arrays you can almost see the faces.
Applying the die value is then accomplished by choosing the array corresponding to the value and using IF(array,Dot,””). This returns a 3x3 array where the 0’s are blank and the 1’s are replaced with whatever character or symbol is defined as Dot. The formula is entered in the upper left corner of the die range.
There are four dice, two per player, each with their own random number generator. The dice are numbered 11, 12, 21, 22 and follow the example shown:
DieFace11 =IF(CHOOSE(DieVal11,Face1,Face2,Face3, Face4,Face5,Face6),Dot,””) DieVal11 =RANDBETWEEN(1,6) Etc.
Now I have a cell named Dot in which the user can select from a picklist of symbols. The symbols on the dice are in Wingdings 2 font. This does not translate to the Data Validation picklist, they appear as the raw characters they are. A table lists the characters in both fonts for reference.
The dice are made exact squares by matching the pixel-width of columns to the pixel-height of rows. Some thought is given to what the maximum row height will be. It is usually determined by font size, but can also be affected by styles. Once I settled on 14 pt font for the dice, this gave me a row height of 18 (30 pixels), so I set the column widths to 2.56 (30 pixels).
The Worksheet_Activate event sets manual calculation, and a die icon is used to play the game. The macro tracks the rolls and accumulates the wins and values during the game, resetting them all at game’s end.
Conditional Formatting turns the winner’s play area yellow. The dice are in the middle of the play area and should remain white, so I give them a white fill format with the condition =TRUE, always on.
A folder icon toggles a column group to show or hide the pip list and the face arrays.
Snake
Snake is more puzzle than game. It tracks the SelectionChange event within a defined rectangle, and enters an arrow indicating the direction traveled. Selection can be done one cell at a time by the arrow keys, or extended by holding shift, or by dragging the mouse. If the selection is not entirely within the zone, nothing happens.
The arrows are a learning aid to see how selection works. In extend mode, arrows are like a growing school of fish, all swimming synchronized. The arrows are Unicode symbols 8592 to 8595. The symbols can be changed, but arrows are most intuitive. Next to each directional symbol is a count of how many of them appear in the zone, with Data Bars.
There are two games, Snake and Balance, turned on by icon, or both turned off to just practice. Snake ends when you select a cell that’s already filled, reports the % of the zone filled, and resets. Balance waits until the entire zone is filled, reports the balance score, and resets. The balance score is (total – range)/total; if there is an equal number of all four arrows, the balance is 100%.
The code is mainly within the SelectionChange event, with a few other subs for clearing the zone and selecting the games. Hidden cells track the selection column and row number and count. When extending up or left, the selection column and row are changed to the top left corner of the selection. When extending down or right, the selection column and row are not changed. Then, the number of selected columns or rows is used to determine which symbol to put in the selection.
Palindrome
A palindrome is any word or phrase that can be read forwards or backwards, disregarding spaces and punctuation. As you begin to string words together in the front half, in your head, you need to envision a reflection of it, to see if you can form real words from it. This sheet will create the reflection and let you edit the spaces and punctuation until it makes sense, if ever.
Input
The input phrase is entered in a single cell, with no punctuation. Then the pivot point is selected, referring to where the reflection begins. There are two options; 0 means pivot on the last input letter, 1 means pivot after the last input letter. For example, if input= “ref”, pivot 0 output= “refer”; pivot 1 output= “reffer”.
The editing area is laid out horizontally. The input phrase is parsed and reflected, character by character, into up to sixty columns for editing. Processing of the text is done in a table, PalProcess, and transposed to columns for intuitive interaction. The initial reflection, =TRANSPOSE( PalProcess[initial]), begins the interaction stage.
Below the initial reflection are two rows of white cells for adjustments, named Adjust1 and Adjust2. Adjust1 is for spacing. This row is formatted in Wingdings 3 font, and accepts only f (←) to close a space or g (→) to open a space. The spacing edits must proceed left to right because they accumulate, and must include an extra space for each punctuation mark to be added.
In the left half of the initial reflection, that is the input half, spaces can only be added for punctuation, since the input already has proper word spacing, and is trimmed in processing in case any extra spaces were inadvertently entered. In the right, reflected half, the letters are like Lego parts that need to be separated from one group and attached to another to form words, by entering a series of f= move left and g= move right.
Below the Adjust1 row is the Adjust2 row for capitalization and punctuation, in normal font. Case adjustment is done by entering any of a set of characters for upper and lower case. These characters are maintained in the Case table and can be modified; the initial pairs of (upper, lower) are (u,l), (^,v), ([,]), which do not conflict with likely punctuation. The idea is to let the user pick whichever pair of indicators they can easily remember and use. Punctuation marks can be entered under a space or at the end of the palindrome. Apostrophe (') must be entered twice (‘’) to show. These edits can be made column by column or after all spacing adjustments are done.
Processing
The processing table begins with an index column, 1 to 60. In the first half, up to the input length, MID picks out each character at the index position. For the second half reflection, the character picked is twice the input length minus the index, plus the pivot. Thus, if the input length is 12 and pivot= 0, the 13th output character is input character number (24-13+0)= 11, the 14th output is input number (24-14+0)= 10, etc. until the 23rd output is input number (24-23+0)= 1, back to the beginning. If pivot= 1, then there will be two of the last input character before the countdown.
Up to input length =MID(TRIM(Input),[@index],1) After input length =MID(TRIM(Input),2*InputLen-[@index]+Pivot,1) InputLen =LEN(TRIM(Input))
The next step is to account for spacing adjustments. This uses a cumulative count of left and right adjustment entries:
Lefts =COUNTIFS(Adjust1,"f",ColIndex,"<="&[@index]) Rights =COUNTIFS(Adjust1,"g",ColIndex,"<="&[@index])
When there is a right-arrow “g” entered, the cumulative count increases, that is, [@rights]> OFFSET([@rights],-1,0), and a space is output. Otherwise, the output character is offset from the index by subtracting [@rights] in the first half, and by adding [@rights] and subtracting [@lefts] in the second half.
Initial =IFERROR(IF([@rights]>OFFSET([@rights],-1,0)," ", T(MID(TRIM(Input),IF([@index]<=InputLen+[@rights],[@index]-[@rights],2*InputLen-[@index]+Pivot+[@rights]-[@lefts]),1))),"")
The final step is to adopt the capitalization and punctuation entries.
Upper =AND(NOT(PalProcess[@marks]="?"), NOT(ISNA(MATCH(PalProcess[@marks],Case[upper],0))))
Lower =AND(NOT(PalProcess[@marks]="?"), NOT(ISNA(MATCH(PalProcess[@marks],Case[lower],0))))
Marks =IF(ISBLANK(INDEX(Adjust2,[@index])),"",INDEX(Adjust2,[@index]))
Final =IF(Upper,UPPER([@initial]),IF(Lower,LOWER([@initial]),IF(AND( LEN([@marks]),OR([@initial]="",[@initial]="")),[@marks],[@initial])))
Output
Below the adjustment rows is the result, still in columns, =TRANSPOSE( PalProcess[final]). Below the input cell is the final palindrome output cell, =CONCAT(TRANSPOSE(PalProcess[final])).
A table is provided to save palindromes, with two buttons to record and review. The record button checks if the palindrome is already in the table, to avoid duplication.
Help
Two demos are provided that perform the inputs and adjustments, with helpful messages. Time between demo actions can be entered in seconds. The demo macros use the Application.Wait method to time the actions.
A collapsible row group gives a transposed view of the processing table, with formula text and data, as well as a button to go to the table itself.
Comments