Macros Project: Simulate a Poker Game                  © Mike Splane                       BUS4 916X

For this project you will be working with macros to create a video game of poker solitaire.  The player will be given a “bank” of money to start the game. He will then choose an amount to bet on the next hand. A five-card Poker hand will be dealt and displayed. The player will select which cards to discard and which ones to keep. Replacements will be dealt for the cards that were discarded and the new hand will be displayed.  The payoff amount on his bet, and his new bank balance, will be shown. The display area of the hand will be reset and the player will be given an option to quit the game.  If a player loses all of his money, or reaches the maximum winnings allowed, the game will end automatically.

 

I have posted an Excel Worksheet for you to use as a template. http://bus91l.altervista.org/Data/Poker.xlsx

 

You can set up the workbook layout like this. Use one worksheet for the user view, use another worksheet for your data and calculations. Or you could two worksheets for your layout, one for the display area and one to store the formulas and data. If you use two you can protect the one containing the data and formulas, but you will have to write several that refer to cells in other worksheets. The formulas and data should be protected and hidden from the player.

 

The “Play” worksheet

The “Hidden” worksheet

Instructions

Display of the hand

Messages and Macro Buttons.

 

 

Messages – summary of the results of the hand.

Table showing the cards in use for this hand, the mini-deck.

Table showing the current five cards in the hand.

Table showing Replace card and Draw cards now options.

Table showing rank and suit values of the cards in the hand.

Table showing count of how many cards of each rank and suit are in the hand.

Table evaluating the type of hand and its payoff value.

A table containing card names which uses random numbers to “shuffle” the “deck” after each hand is played.

A table listing the card names in alphabetical order, used for VLOOKUP functions.

 

Part One – Create the first ”Hidden” worksheet.

Change the name of Sheet1 to Hidden. All of the 7 tasks in this part will be performed in the “Hidden” worksheet.

 

Task 1

Create this table in cells A3:B7

Your starting amount

1000

Your hand was

="You have "&HandType&"." 

Result of this hand

=IF(Payoff<0,"You lost your bet.","You won $"&Payoff)

Amount of your  bet

500

You now have

=B3+Payoff-B6

 

Create a range name Bet for cell B6.

 

Task 2

Enter this label and formula into cells B9 and C9

Start deal at card

=HOUR(NOW())+(RIGHT(1,(MINUTE(NOW())))*2)

The purpose of this long formula is to make the point in your deck where you start the deal a random one.

 

Build a table to show the five cards in the hand and the five cards to use as their replacements if any cards are drawn. Create this table in the range A10:E12.

 

Card 1

Card 2

Card 3

Card 4

Card 5

=OFFSET(C48,StartDeal,0)

=OFFSET(C49,StartDeal,0)

=OFFSET(C50,StartDeal,0)

=OFFSET(C51,StartDeal,0)

=OFFSET(C52,StartDeal,0)

=OFFSET(C53,StartDeal,0)

=OFFSET(C54,StartDeal,0)

=OFFSET(C55,StartDeal,0)

=OFFSET(C56,StartDeal,0)

=OFFSET(C57,StartDeal,0)

 

Create the range name StartDeal to refer to the cell C9.

Create the range name Cards to refer to the range A11:E12.

 

Task 3

For the draw cards operation, Create a table in cells A15:B20 to show the cards to be displayed. Create a table in cells D15: E20 to show the keep/discard options.  Create Data Validation lists in cells E11:E16 so only Y,y,N,or n can be entered.

 

There needs to some mechanism to delay the appearance of the selected cards until all five keep/discard boxes have an entry. That is the purpose of cell E16. Create the range name DrawCards to refer to cell E16.

 

Card 1

=IF(AND(E$20="Y",E15="N"),A$12,A$11)

 

Keep card 1?

User enters Y or N

Card 2

=IF(AND(E$20="Y",E16="N"),B$12,B$11)

 

Keep card 2?

User enters Y or N

Card 3

=IF(AND(E$20="Y",E17="N"),C$12,C$11)

 

Keep card 3?

User enters Y or N

Card 4

=IF(AND(E$20="Y",E18="N"),D$12,D$11)

 

Keep card 4?

User enters Y or N

Card 5

=IF(AND(E$20="Y",E19="N"),E$12,E$11)

 

Keep card 5?

User enters Y or N

 

 

 

Draw cards ?

User enters Y or N

 

 

Note, in the Excel template it is possible to enter Y to see the result of drawing cards, then switch the setting back to N to return to the original hand. I did not put a restriction on this so you can play with the program and test it out. In the completed project you should probably replace the formula with a macro, so the user can’t return to the starting five cards and draw a different set of replacement cards,

 

Task 4

Create this group of cells in the range A23:C27 to create a table showing the rank value and suit value of the cards in the hand.

 

=B15

=VLOOKUP(B15,DECK,2)

=VLOOKUP(B15,DECK,3)

=B16

=VLOOKUP(B16,DECK,2)

=VLOOKUP(B16,DECK,3)

=B17

=VLOOKUP(B17,DECK,2)

=VLOOKUP(B17,DECK,3)

=B18

=VLOOKUP(B18,DECK,2)

=VLOOKUP(B18,DECK,3)

=B19

=VLOOKUP(B19,DECK,2)

=VLOOKUP(B19,DECK,3)

 

Create the range name RV for the cells in B23 to 27.

Create the range name SV for the cells in C23 to C27.

 

Task 5

Create a table using CountIF functions in the range A30 to B43 to show how many times each value appeared in the rank value and suit value ranges

 

RankCount

SuitCount

=COUNTIF(RVC,2)

=COUNTIF(SVC,1)

-COUNTIF(RVC,3)

=COUNTIF(SVC,2)

=COUNTIF(RVC,4)

=COUNTIF(SVC,3)

=COUNTIF(RVC,5)

=COUNTIF(SVC,4)

=COUNTIF(RVC,6)

 

=COUNTIF(RVC,7)

 

=COUNTIF(RVC,8)

 

=COUNTIF(RVC,9)

 

=COUNTIF(RVC,10)

 

=COUNTIF(RVC,11)

 

=COUNTIF(RVC,12)

 

=COUNTIF(RVC,13)

 

=COUNTIF(RVC,14)

 

 

Use the range name RVC to refer to the A31:A43 range of cells and RSV to refer to the B31:B43 range of cells.

 

Task 6

Create this table in the range D30:F40. It uses IF functions to evaluate the hand to figure out the type of hand you have and formulas to calculate the payoff ratio for each type of hand.

 

Type of Hand

Evaluation Formulas

Pay Off Amounts

a pair.

=IF(AND(COUNTIF(RVC,2)=1,(MAX(RVC)=2)),1,0)

=IF(E31=1,Bet,0)

two pair.

=IF(AND(COUNTIF(RVC, 2)=2,(MAX(RVC)=2)),1,0)

=IF(E32=2,Bet,0)

three of a kind.

=IF(AND(COUNTIF(RVC, 2)=0,MAX(RV)=3),1,0)

=IF(E33=1,Bet*3,0)

a full house.

=IF(AND(COUNTIF(RVC, 2)=1,MAX(RVC)=3),1,0)

=IF(E34=1,Bet*6,0)

four of a kind.

=IF(MAX(RV)=4,1,0)

=IF(E35=1,Bet*8,0)

a straight.

=IF(AND(MAX(RVC)=1,MAX(RV)-MIN(RV)=4),1,0)

=IF(E36=1,Bet*4,0)

a five-high straight.

=IF(AND(MAX(RVC)=1,MAX(RV) =14,SUM(RV)=28),1,0)

=IF(E37=1,Bet*4,0)

a flush.

=IF(MAX(SVC)=5,1,0)

=IF(M65=1,Bet*5,0)

a straight flush.

=IF(SUM(E36:E38)=2,1,0)

=IF(E39=1,Bet*3,0)

Results

 

=MAX(F31:F39)

 

 

Enter this long formula into cell F40. =IF(E31=1,D31,IF(E32=1,D32,IF(E33=1,D33,IF(E34=1,D34,IF(E35=1,D35,IF(E39=1,D39,IF(E36=1,D36,IF(E37=1,D37,IF(E38=1,D38,"Garbage")))))))))

If you copy and paste it should work. If it doesn’t work, and you get the #NAME? error message, delete the two quotation marks around the word garbage, then type them in again.

 

Create the range name Payoff to refer to cell F40.

 

Task 7

Create these two tables in the A47 to C99 range and the E47 to G99 range.

 

Random Number

Number

Name

 

 

Name

Rank Value

Suit Value

=RandBetween (100,999)

1

AC

 

2C

2

1

=RandBetween (100,999)

2

KC

 

2D

2

2

=RandBetween (100,999)

3

QC

 

2H

2

3

=RandBetween (100,999)

4

JC

 

2S

2

4

=RandBetween (100,999)

5

TC

 

3C

3

1

=RandBetween (100,999)

6

9C

 

3D

3

2

=RandBetween (100,999)

Etc

 

 

 

 

=RandBetween (100,999)

52

2S

 

TS

10

4

 

The card names in column E must be sorted alphabetically. 2-9 then AJKQT.

The Card Values in column F should be A=14 J=11, K=13, Q=12, T=10.

The Suit Values in column G should be C=1 D=2 H=3 S=4

 

Create a range name SortDeck for the A48:C99 range.

Create a range name Deck for the E48 to G59 range.

 

Task 8

IMPORTANT!!

Test the worksheet to make sure all of the formulas are working correctly. Try different combinations of cards to make sure that all of the hands evaluate correctly and the payoff values are correct.

 

After you are completely certain that everything is working right, you will want to protect the worksheet so none of the formulas or other information is accidentally altered. Some cells must remain unprotected since values will be added or changed in them.

 

First, select cells B6 and format it. On the protection tab uncheck the box next to the word Locked. Do the same thing to the ranges E15:E20 and to A48:C99.

Then, Protect the entire worksheet.

Save the workbook as a macro enabled file.

 

This is where I stopped when completing the Excel template for you to use. Here’s where to download it. The rest is left up to your team to complete.

http://bus91l.altervista.org/Data/Poker.xlsx

Part Two – Create the “Play” worksheet.

The player will be given a “bank” of money to start the game. He will then choose an amount to bet on the next hand. A five-card Poker hand will be dealt and displayed. The player will select which cards to discard and which ones to keep. Replacements will be dealt for the cards that were discarded and the new hand will be displayed.  The payoff amount on his bet, and his new bank balance, will be shown. The display area of the hand will be reset and the player will be given an option to quit the game.  If a player loses all of his money, or reaches the maximum winnings allowed, the game will end automatically.

 

Task 1

Insert a second worksheet. Change the name of this sheet to Play. You should have two worksheets in the workbook. If you have more than two delete the ones you are not using.  All of the tasks in this part will be performed in the “Play” worksheet.

 

Click on the file tab. Click on the INFO option. Click on the work Properties in the upper right.

 

Select advanced properties. A dialog box pens. Click on the Summary tab.

Enter your name(s) into the Author category.  Click on OK.

 

Task 2

Create the labels to show on the cards.

 

Create this lookup table in cells A42:B5.

Create the range name CardLabels for the table. 

 

CardLabels

2

Two

3

Three

4

Four

5

Five

6

Six

7

Seven

8

Eight

9

Nine

A

Ace

C

Clubs

D

Diamonds

H

Hearts

J

Jack

K

King

Q

Queen

S

Spades

T

Ten

 

Enter these formulas:

·         In cell E40 =Hidden!B15

·         In cell i40 =Hidden!B16

·         In cell M40 =Hidden!B17

·         In cell Q40 =Hidden!B18

·         In cell U40 =Hidden!B19

 

 

Task 3

Create the Card Images:

 

 

Here is what I did to create this display. Note: In many of these steps you can select multiple cells, columns or rows at the same time. After making your first selection hold down the control key as you select additional areas.

 

Note: The MAC version of Excel does not use the same measurements for rows and column sizing, so you may have to experiment to obtain similar cell shapes.

Changed height of rows 30 to 40 to 40.

Changed the height of rows 31 to 33 to 60.

Changed the heights of rows 35 to 37 to 60.

 

Changed the widths of columns D,H,L,P,T to 9

Changed the widths of columns E,I,M,Q,U to D,H,L,P,T to 5

Changed the widths of columns F,J,N,R,V to 12

 

Created the images to show on the back of the cards.

Formatted all of the cells in the D31:F33 range as bold, 14 point, Calibri font.

Changed the alignment of the cells in the D31:F33 range to center the text both horizontally and vertically.

 

Filled cell E31 with a light yellow color.

Copied the formatting from cell E31 into cells D32, E32, F32, and E33.

Entered the word cards into cell E32.

 

Entered a dollar sign - $ - into cell D31.

Formatted cell D31 using Fill Effects. I chose a light green color, from the center, as my options.

 

 

Copied the contents of cell D31 into cells F31, D33, and F33.

Added a “Thick Box” border to the D31:F33 range.

Selected the range D31:F33. Copied the range. Pasted it into cells H3,1 L31, P31, and T31 to show five identical card images.

 

Created the images to show on the front of the cards.

 

Entered this formula in cell D36 =LOOKUP(LEFT(E40,1),CardLabels)

Entered this formula in cell F38 =LOOKUP(RIGHT(E40,1),CardLabels)

Entered this formula in cells D38 and F36 =E40

Removed the Bold formatting from cell E37.

Entered the word “of” into cell E37.

 

Formatted the cell backgrounds in the D36:F38 range using the same steps as in range D31:F33, but using a light blue solid fill and a blue gradient fill.

Added a “Thick Box” border to the D36:F38 range.

Selected the range D36:F38. Copied the range. Pasted it into cells H36, L36, P36, and T36 to show five identical card images.

 

Task 4

 

Create the Macros and messages.

You need to create a list of messages to display to the player, describing what action they need to take during each step of the game. Every time you run a macro you will need to change the screen display, change the instructions, and show a different set of macro buttons.

 

Can you do all of this in a simple way? Yes.  If you had each set of instructions on different rows of the worksheet you could use a short macro for each step in the game to display just the rows that were relevant and to hide all of the rest.

 

Your starting screen should show

o   The rows in the Play worksheet that contain game information and instructions.

o   The macro button to push to Start a new game.

Hide all of the other rows that contain content.

 

When the “Start a new game” macro button is pushed, run the

 

Start a New Game macro

Set up the initial values

·         Set bank balance to 1000. Cell B3 in Hidden worksheet.

·         Set keep card options to Y. Cells E15:19 in Hidden worksheet

·         Set draw cards option to N. Cell E20 in Hidden worksheet

·         Sort the deck. Put the cursor in cell A48 in the Hidden worksheet. Click on the AtoZ sort icon and choose smallest to largest.

Show the messages

·         Select the rows that contain the content for this step:

o   The face-down images of five cards.

o   Your bank balance is. 

o   Display instructions - place your bet.

o   Show a macro button to push when the bet has been placed.

·         Click the Format cells icon. 

·         Select unhide. 

Hide the instructions.

·         Select the rows that were used in the previous step.

·         Click the Format cells icon. 

·         Select hide. 

 

When the “My bet is placed” macro button is pushed, run the

 

Show Initial Hand Macro

Generate a new hand:

·         Select the StartDeal range.

·         copy the formula.

·         paste it back into the same cell.  This will generate a new starting point for the first card to be dealt.

Show the messages contain the content for this step:

·         Select the rows that show:

o   The images of the five cards in the initial hand.

o   “Your hand is “and display the contents of cell B4 in the Hidden worksheet.

o   Display the draw cards instructions.

o   Show the set of draw cards options. Cells D15 to D19 from the Hidden worksheet.

o   Show a “Draw cards Now” macro button to push when the user is ready to draw replacement cards.

·         Click the Format cells icon. 

·         Select unhide. 

Hide the place your bet instructions.

·         Select the rows that were used in the previous step.

·         Click the Format cells icon. 

·         Select hide. 

 

When the “Draw Cards Now” macro button is pushed, run the

 

Show Final Hand Macro

 

Show the messages contain the content for this step:

·         Select the rows:

o   The images of the five cards in the final hand.

o   Show the contents of cell A3:B7 in the Hidden worksheet.

o   Display message Quit game? with a macro button

o   Continue game? with a macro button.

·         Click the Format cells icon. 

·         Select unhide. 

Hide the previous instructions.

·         Select the rows that were displayed in the previous macro.

·         Click the Format cells icon. 

·         Select hide. 

 

If the Quit button is pushed - proceed to Quit Game macro.

 

If the Continue button is pushed – proceed to Continue Game macro.

 

Quit Game macro.

Show the messages which contain the content for this step:

·         Select the rows that show:

o   A row containing an if Function to show the game result.

§  If B7 is less than 1000,

§  show a message: You have lost & (1000-B7) & during this game.

§  If not, show a message: You have won & (B7-1000) & during this game.

o   Show a message: Would you like to start a new game?

o   The rows in the Play worksheet that contain game information and instructions.

o   The macro button to push to Start a new game.

·         Click the Format cells icon. 

·         Select unhide. 

Hide the previous instructions.

·         Select the rows that were displayed in the previous macro.

·         Click the Format cells icon. 

·         Select hide. 

 

Continue Game macro

Set up the initial values

·         Set keep card options to Y. Cells E15:19 in Hidden worksheet

·         Set draw cards option to N. Cell E20 in Hidden worksheet

·         Sort the deck. Put the cursor in cell A48 in the Hidden worksheet. Click on the AtoZ sort icon and choose smallest to largest.

Show the messages

·         Select the rows that contain the content for this step:

o   The face-down images of five cards.

o   Your bank balance is & the value of Cell B3 in the Hidden worksheet.

o   Display instructions - place your bet.

o   Show a macro button to push when the bet has been placed.

·         Click the Format cells icon. 

·         Select unhide. 

Hide the instructions.

·         Select the rows that were used in the previous step.

·         Click the Format cells icon. 

·         Select hide. 

 

 

Check for Understanding - Poker Project

 

Name ___________________________             Section Number __________

 

I understand what each of these formulas does.

Y/N   Cell A11  =OFFSET(C48, HOUR(NOW())+(RIGHT(1,(MINUTE(NOW())))*2),0)

Y/N   Cell B15   =IF(AND(E$20="Y",E15="N"),A$12,A$11)

Y/N   Cell A23   =VLOOKUP(B15,DECK,2)

Y/N   Cell A31   =COUNTIF(RVC,2) [RVC means Rank Value Count.  It counts the sum of the number times each rank-value appears in the hand.]

Y/N   Cell E31   =IF(AND(COUNTIF(RVC,2)=1,(MAX(RVC)=2)),1,0)

Y/N   Cell E32   =IF(AND(COUNTIF(RVC, 2)=2,(MAX(RVC)=2)),1,0)

Y/N   Cell E33   =IF(AND(COUNTIF(RVC, 2)=0,MAX(RVC)=3),1,0)  

Y/N   Cell E34   =IF(AND(COUNTIF(RVC, 2)=1,MAX(RVC)=3),1,0)

Y/N   Cell E35   =IF(MAX(RVC)=4,1,0)

Y/N   Cell E36   =IF(AND(MAX(RVC)=1,MAX(RV)-MIN(RV)=4),1,0)   [RV is the value assigned to each denomination of card.  A=14, K=13, Q=12, etc.]

Y/N   Cell E37   =IF(AND(MAX(RVC)=1,MAX(RV) =14,SUM(RV)=28),1,0)

Y/N   Cell E38   =IF(MAX(SVC)=5,1,0)  [SVC means Suit Value Count.  It counts the sum of the number times each suit-value appears in the hand.]

Y/N   Cell E39   =IF(SUM(E36:E38)=2,1,0)

Y/N   Cell A48   =RANDBETWEEN (100,999)

 

Email the CFU form to your instructor. Attach a copy of your Poker Game Excel file to the email.