Macros Project: Simulate a Poker Game                  © Mike Splane                       BUS4 91A

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 reset and the player will be given an option to quit the game. 

 

You can set up the workbook layout like this. Use a set of five worksheets to show th progress of the game. Use another worksheet for your data and calculations. The formulas and data should be protected and hidden from the player.

 

The “Play” worksheets

The “Hidden” worksheet

Instructions

Display of the hand

Messages and Macro Buttons.

 

The Macros:

·         Rules

·         Play

·         Hand1

·         Hand2

·         Quit

 

 

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 ”Hidden” worksheet.

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

 

Task 1

2.      Create this table in cells A3:B7

3.      Format cells B3, B4, B6 and B7 as currency, with no decimal places, and using a red font for negative numbers.

Your starting amount

1000

Amount of your  bet

 

Your hand is

="You have "&HandType

Won or lost on this hand

=IF(Payoff<0,"You lost  $“&Bet,"You won $"&Payoff)

Your current amount

=B3+Payoff-B4

 

Create a range name Bet for cell B4.

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.

4.      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)

 

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

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

 

Task 3

For the draw cards operation,

7.      Create a table in cells A15:B20 to show the cards to be displayed.

8.      Create a table in cells D15: E20 to show the keep/discard options. 

9.      Create Data Validation lists in cells E15:E20 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 E20.

10.  Create the range name DrawCards to refer to cell E20.

 

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 will 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

11.  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)

 

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

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

 

Task 5

14.  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(RV,2)

=COUNTIF(SV,1)

-COUNTIF(RV,3)

=COUNTIF(SV,2)

=COUNTIF(RV,4)

=COUNTIF(SV,3)

=COUNTIF(RV,5)

=COUNTIF(SV,4)

=COUNTIF(RV,6)

 

=COUNTIF(RV,7)

 

=COUNTIF(RV,8)

 

=COUNTIF(RV,9)

 

=COUNTIF(RV,10)

 

=COUNTIF(RV,11)

 

=COUNTIF(RV,12)

 

=COUNTIF(RV,13)

 

=COUNTIF(RV,14)

 

 

15.  Use the range name RVC to refer to the A31:A43 range of cells

16.  Use the range name SVC to refer to the B31:B34 range of cells.

 

Task 6

17.  Create this table in the range D30:G40.

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

Pays

Evaluation Formulas

Pay Off Amounts

a pair.

1 * Bet

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

=IF(E31=1,Bet,0)

two pair.

3 * Bet

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

=IF(E32=2,Bet*3,0)

three of a kind.

20 * Bet

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

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

a straight.

24 * Bet

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

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

a five-high straight.

25 * Bet

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

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

a flush.

30 * Bet

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

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

a full house.

40 * Bet

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

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

four of a kind.

500 * Bet

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

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

a straight flush.

2500 * Bet

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

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

Results

 

 

=MAX(F31:F39)

 

18.   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.

19.  Create the range name HandType to refer to cell F40.

20.  Create the range name Payoff to refer to cell G40.

 

Task 7

21.  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

 

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

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

 

Task 8

IMPORTANT!!

24. 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.

 

25.  First, select cells B3:B6 and open the format cell dialog box. On the Protection tab uncheck the box next to the word Locked.

26.  Do the same thing to the ranges E15:E20 and to A48:C99.

27.  Then, Protect the entire worksheet.

28.  Save the workbook as a macro enabled file.

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. 

 

Task 1

1.       Insert a second worksheet.

2.       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. 

 

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

 

3.       Select advanced properties. A dialog box pens.

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

 

Task 2

Create the Payoff Table

5.      Reproduce this table in the range T2:V10

6.      Note I had to merge and center the T and U cell in each row to get the Type of Hand labels to fit.

7.      Add comments to each type of hand to explain what it is or to show an example. Hide the comments.

.

Type of Hand

Pays

a pair

1 * Bet

two pair

3 * Bet

three of a kind

20 * Bet

a straight

25 * Bet

a flush

30 * Bet

a full house

40 * Bet

four of a kind

500 * Bet

a straight flush

2500 * Bet

 

Task 3

Create the labels to show on the cards.

8.       Change the number format in cells E42:F51 to text.

9.       Create this lookup table in cells E42:F59. 

10.   Create the range name CardLabels for the table. 

 

 

11.   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 4

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.

12.   Change height of rows 30 to 40 to 40.

13.   Change the height of rows 31 to 33 to 60.

14.   Change the heights of rows 35 to 37 to 60.

15.   Change the widths of columns D,H,L,P,T to 9

16.   Change the widths of columns E,I,M,Q,U to 5

17.   Change the widths of columns F,J,N,R,V to 12

 

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

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

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

 

20.   Fill cell E31 with a light yellow color.

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

22.   Enter the word “cards” into cell E32.

23.   Enter a dollar sign - $ - into cell D31.

24.   Format cell D31 using Fill Effects. I chose a light green color and “from the center” as my options.

 

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

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

27.   Select the range D31:F33. Copy the range. Paste it into cells H31 L31, P31, and T31 to show five identical card images.

 

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

 

28.   Enter this formula in cell D36 =LOOKUP(LEFT(E40,1),CardLabels)

29.   Enter this formula in cell F38 =LOOKUP(RIGHT(E40,1),CardLabels)

30.   Enter this formula in cells D38 and F36 =E40

31.   Remove the Bold formatting from cell E37.

32.   Enter the word “of” into cell E37.

 

33.   Format 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.

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

35.   Select the range D36:F38. Copy the range. Paste it into cells H36, L36, P36, and T36 to show five identical card images.

36.   Hide rows 41 to 60.

37.   Hide rows 16 to 29.

38.   Hide columns A and B

 

Part Three – Create Additional Worksheets.

 

Task 1

Make three copies of the Play worksheet.

1.      Right-Click on the worksheet tab for the Play worksheet.

2.      Select Move or Copy

3.      In the Move or Copy dialog box select Create a copy. Click on OK.

4.      Repeat three more times to create more copies of the Play worksheet.  You want to have five of them.

5.      Rename one of the “Play” worksheets as “Quit”

6.      Hide rows 35 to 40 in the Quit worksheet.

7.      Rename one of the “Play” worksheets as “Hand1”

8.      Hide rows 30 to 34 in the Hand1 worksheet.

9.      Rename one of the “Play” worksheets as “Hand2”

10.  Hide rows 30 to 34 in the Hand2 worksheet.

11.  Rename one of the “Play” worksheets as “Rules”

12.  Hide rows 35 to 40 in the Rules worksheet.

13.  Hide rows 35 to 40 in the Play worksheet.

14.  Click and drag on the worksheet tabs to put them into this order, from left to right:

Rules, Play, Hand1, Hand2, Quit, Hidden.

 

Add Macro buttons to the worksheets

Rules

·         Show the payoff/odds table.

·         Add a Play the Game button.

·         Add a Quit button.

 

 Play

·         Show the payoff/odds table.

·         Add a Place Your Bet button.

·         Add a Quit button.

 

Hand1

·         Show the payoff/odds table.

·         Add a macro button “Draw Cards Now”.

·         Add a macro button labeled “Quit Game”.

 

Hand2

·         Show the payoff/odds table

·         Add a macro button labeled “Play Another Hand”.

·         Add a macro button labeled Quit Game”

 

Quit 

·         Add a Play Again button.

Part Four– Create the Macros to Play the Cards.

 

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

 

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. I have given you suggestions below for how to write the macros but you are welcome to create your own or modify my ideas. My outline of the macro steps may be missing a few necessary tasks.  Please let me know if you spot any errors or omissions of key steps.

 

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. Create a series of worksheets to display the cards and messages at each stage of the game. The macro code to switch to a new worksheet would look something like this. You just have to replace the word “Rules” with the name of another worksheet, and change the range reference to whatever cell you want to show in the upper left corner of that worksheet.

 

Sheets("Rules").Select

Rows("1:1").Select – Check this code

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Range("A1").Select

 

Rules

·         Describe how the game works.

 

 Play

·         Show current cash values.

·         Show five cards, with the faces down.

·         Add a prompt for bet amount.

 

Hand1

·         Show the initial hand of five cards, with the faces up.

·         Add a series of prompts showing a Keep/Discard option for each card.

 

Hand2.

·         Show updated cash values.

·         Show the five cards in the final hand, after the draw is complete, with the faces up.

·         Show a message about the value of the hand.

·         Show a message stating the amount won or lost on this hand.

·         Show a message showing the current value of the “bank”.

 

Quit 

·         Show the amount of winnings/losses.

·         Add a goodbye message thank them for playing.

 

 

o   The macro button to push to deal the first hand.

 

Task 1

Play

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

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.

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

Display the Play worksheet.

 

When the “Place My Bet” button is pushed, run the Hand1 Macro

When the “Quit Game” button is pushed run the Quit macro

 

Task 2

Hand1 Macro

The macro should move the cursor into cell A1 in the Hand1 worksheet.

=“Do you want to replace the ” & D36 &” of “& F38 &”? Enter Y or N.”

=“Do you want to replace the ” & H36 &” of “& J38 &”? Enter Y or N.”

=“Do you want to replace the ” & L36 &” of “& N38 &”? Enter Y or N.”

=“Do you want to replace the ” & P36 &” of “& R38 &”? Enter Y or N.”

=“Do you want to replace the ” & T36&” of “& V38 &”? Enter Y or N.”

 

When the “Draw Cards” button is pushed, run the Hand2 Macro

When the “Quit Game” button is pushed run the Quit macro

 

Task 3

Hand2 Macro

The macro should move the cursor into cell A1 in the Hand2 worksheet.

The worksheet should show

The images of the 5 cards in the final hand.

A message   “Your hand is “and display the contents of cell B5 in the Hidden worksheet.

A message showing the amount won or lost on this hand

A message showing the amount the player has in his bank.

 

Show a “Play Another Hand” macro button labeled “Play Another Hand”

Show a macro button labeled Quit game.

When the Play Another Hand button is pushed, run the Play Macro

When the “Quit Game” button is pushed run the Quit macro 

 

Task 4

Quit macro.

The macro should move the cursor into cell A1 in the Quit worksheet

The worksheet should show

A message 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.”

An end of game message.

A macro button labeled  Start a new game.”

If the button is pushed, run the Rules macro.

 

Task 5

Rules macro

The macro should move the cursor into cell A1 in the Rules worksheet.

When the “Play Game button is pushed, run the Play Macro

When the “Quit Game” button is pushed run the Quit macro

 

Task 6

Test your macros and make sure the game runs correctly.

Part 5 Challenges (Optional)

This part of the assignment is not required.  Try to complete one or more of these challenges. They will definitely help to build your skills.

A.      Add sound (instructions and/or music). If you choose this option I can share a file.HeroGame.xlsm, which contains a macro that successfully does this.

B.      Add a splash screen (see the Macros Three assignment for help on splash screens.)

C.      Use pictures of cards instead of the graphics. You can download folders of images from Google images that contain complete decks.

D.     Add message boxes with questions and comments. One message box can ask for the player’s name and then insert that name into other messages. (See the Macros Two assignment for help with message boxes).

E.      Create a macro to automatically end the game when the player goes bankrupt or exceeds a set amount, for example $100,000, displaying appropriate messages for each case. You might need to add another worksheet for this task, to show the messages.

F.       Track statistics and display reports: Ten biggest winning hands (amounts), Ten largest bets (amounts), frequency of appearance of each type of (final) hand. I would do this by adding a Stats worksheet, and modify the Hands 2 macro to add/update the stats every time the Hand2 macro is run. I would then add a macro button to display Stats and build a stats macro to switch the user to that worksheet. 

 

Check for Understanding - Poker Project  BUS91A

 

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 (rank value) 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)

 

Have one person from the group Email the Poker Game file to mike.splane@sjsu.edu  List the team members’ names in the email message.

 

End of Course Survey

I'm asking for some feedback on the course, so I can make it better for the next group of students. Please fill this out and return it to me by email. Thanks - Mike
Survey                 My name is:__________________________
Yes/No questions
_____  Was the course valuable for you?
_____  Would you recommend this course to other students?
_____  Did you learn as much as you wanted to?
_____  Did you like working in teams?
_____  Did you like working on your own on the assignments?
_____  Did you like learning from videos?

Open Ended Questions
What should the instructor know to help him teach more effectively in the future? Were any of the assignments too hard, confusing, or time consuming?

 

 

 

 What would you do differently if you were teaching this course?


 

What changes, if any, should be made to improve the course content?



Please send your replay to 
mike.splane@sjsu.edu