The previous section explained the VBA upgrades needed to make the score functional and new-game and end-game sound effects were added.
This tutorial shows how fix some previous bugs and create a “demo play” option which means that both bats are automatically run by worksheet logic. This is the final part of the of the Pong tutorial series.
[sociallocker]
[/sociallocker]
Excel PONG Tutorial #10 – adding a demo playing mode
by George Lungu
– The previous tutorial (part#9) showed how to
upgrade the VBA code to make the score display
functional by applying the game rules.
It also introduced start and end of game sound effects.
Besides fixing some bugs from the previous sections,
this section will show how to modify the worksheet
and VBA code to get a “demo” (hands free) playing
mode, in which the computer plays both parties in
the game. This is the last section of the Pong tutorial.
<excelunusual.com>
1
56. Create a new copy of the worksheet
– In the folder “Pong_Tutorial_Archive” open Pong_Tutorial_Advanced.xls and copy the last worksheet (Pong_Tutorial_9).
-Rename the new worksheet Pong_Tutorial_10.
– Using the VBA editor insert a new module, Module4. Copy all the code from Module3 into Module4.
– Change the name of the Serve_9 macro into Serve_10.
– Change the name of the Play_Tutorial_9 macro into Play_Tutorial_10.
– Change the name of the Collision_Effects_9 macro into Collision_Effects_10.
– In the code, make sure you replace all suffixes “_9” with “_10” (such as call the Collision_Effects_10 macro within the
Play_Tutorial_10 macro).
– Reassign the Serve_10 and Play_Tutorial_10 to the Serve and Play buttons in the new worksheet.
– Reassign the Enable_Sounds macro attached to the new worksheet to the new Enable_Sounds button.
57. Upgrade a few formulas in the worksheet
T36: “=IF(R33<10,-70,-50)”
X36: “=IF(S33<10,70,80)” This adjusts the x-position of the least significant digit of the score
Replace Y_Bat#1 and Y_Bat#2 with Limited_Y_Bat#1 and Limited_Y_Bat#2 in cell U27 (fixes a bat collision bug):
U27:“=IF(T16,SIN(1.4*(S28-V4)/P12)*SQRT(T28^2+U28^2),IF(T18,SIN(1.4*(S28-V5)/P12)*SQRT(T28^2+U28^2),IF(T15,-U28,U28)))”
Replace all the “Bat positions” coordinates in the Collision Events table with the “Limited bat positions”. The previous formulas
created a bat collision bug.
T16: “=AND(V4-P12/1.8<=S27,S27<=V4+P12/1.8,R27<-V9/2+V8+V11,R28>=-V9/2+V8+V11)
T17: “=AND(NOT(AND(V4-P12/1.8<=S27,S27<=V4+P12/1.8)),R27<-V9/2+V8+V11,R28>=-V9/2+V8+V11)
T18: “=AND(V5-P12/1.8<=S27,S27<=V5+P12/1.8,R27>V9/2-V8-V11,R28<=V9/2-V8-V11)”
T19: “=AND(NOT(AND(V5-P12/1.8<=S27,S27<=V5+P12/1.8)),R27>V9/2-V8-V11,R28<=V9/2-V8-V11)”
<excelunusual.com> 2
– In Y33 create a cell for “Serve Turn” which can be 1 or -1 depending
which player serves. During every serve this cell’s value will toggle
between 1 and -1, therefore the players will share the serve.
– In cell Y27 create a current time value (recurrent time calculation of
time since the last serve Y27: “=Y28+Y23”.). We will need this for
enhancing (randomizing) the Bat#2 movement during the demo mode.
– In cell O35 create an indicator for the demo mode (it shows if the
demo mode is turned ON or OFF).
58. Create a “Demo” button and a “Demo” macro
Sub Demo()
-This macro will first stop the game then it will
RunPause = False
toggle the value in cell O35 between “Demo ON”
If [O35] = “Demo ON” Then
and “Demo OFF”
[O35] = “Demo OFF”
– If the demo mode is off, clicking the “Demo” Serve_10
Call PlaySound(ThisWorkbook.Path & “\end_game.wav”, 0&, &H1)
button will reset the game by triggering the
Else
“New_Game_10” macro and then trigger the
[O35] = “Demo ON”
“Play_Tutorial_10” macro starting a new game
New_Game_10
with both players automatic. Play_Tutorial_10
End If
– If the demo mode is on, clicking the “Demo”
End Sub
button will stop the game, reset the ball
(Serve_10) and play the “end_game” sound effect.
<excelunusual.com> 3
59. Upgrade the bat formulas
– In the Y_Bat#2 cell (X27) replace the old formula with the following:
X27: “=IF(O35=”Demo OFF”,S6,V27+SIN(Y27/100)*P12/3)”
-The new formula implies that the new Y coordinate of Bat#2 is determined by the mouse position in the play
mode (with the option Demo OFF). However in the demo mode (Demo ON), the y-coordinate of Bat#2 is that of
Bat#1 plus a slow sinusoidal component to allow the bat to hit the ball off center.
– Without this sinusoidal component, the demo game would be boring since centered bats send the ball at a
zero angle of incidence. The game would in that case be using almost static bars positioned along a horizontal
line (unless the players’ level would be very low for the ball initial speed).
60. Upgrade the “Speed_Y_ball” formula (U27)
– A bug was observed when the ball occasionally fails to bounce off the upper or lower wall.
The formula for the y-speed component of the ball was modified so that it prioritizes the wall collisions before the bat collisions or bat misses:
U27:“=IF(T15,-U28,IF(T16,SIN(1.4*(S28-V4)/P12)*SQRT(T28^2+U28^2),IF(T18,SIN(1.4*(S28-V5)/P12)*SQRT(T28^2+U28^2),U28)))
61. Upgrade the “Serve” formulas
– Upgrade the serve formulas to take R23: “=-IF(Y33=1,-1,1)*(V9/2-V8-V11) into account the new alternative serve setup (players take turns at serve). T23: “=IF(Y33=1,-1,1)*P$10*COS(V$23)
62. Remove the “New Game” button
– We will still use the “New_Game_10” macro but it will be integrated within play and the serve macros. I decided to take it off since it was confusing to the user. Jan-Ove (JO) Waldner-nicknamed Lao Wa
<excelunusual.com> 4
63. Create the “Maximum Score” cell and macro
– The value in cell P4 will contain the maximum score value achievable in a game. It is a value adjustable between the limits 5 and 99 using a spin button and the following macro:
Private Sub Max_Score_Change()
[P4] = Max_Score.Value
End Sub
64. Upgrade the “New_Game” Sub New_Game_10()
-The only addition here is the initialization of the “Serve Turn” cell Y33. This will ensure that Player #1 will serve first.
Sub New_Game_10()
[Y33] = 1[R33:S33] = 0
Call PlaySound(ThisWorkbook.Path & “\new_game.wav”, 0&, &H0)
On Error Resume NextServe_10
End Sub
65. Create a logic table for end-of-game sprites
– There are three more sprites to be moved around depending on the score
after the game ends.
These are the winner sprite, the looser sprite and the logo. Figuring out the logic of the formulas is left as an exercise to the reader, the only thing to mention is that the VBA will control only cell AC1.
66. Upgrade the “Serve” macro
– The only addition here is the line “[Y33] = -[Y33]” at the beginning of each serve. This flips the turn to serve between the players. Range(“R28:U28”) = Range(“R23:U23”).Value
[A42] = [BG1].Value
[Y33] = -[Y33]
Range(“R28:Y30”).Clear
End Sub
<excelunusual.com> 5
67. Upgrade the “Play_Tutorial_10” macro
Due to this block, this macro will not only be able to be started or stopped from the same button but when the score reaches the maximum, the macro will reset (New_Game) the game and proceed to play from there.
It acquires the cursor coordinates where the mouse was clicked to start the macro
It hides end-of-game sprites
Gets the mouse coordinates relative to the initial click point (where the macro was triggered)
It plays collision sounds but only if the sound option is on, the score is not maxed out, and the collision conditions are met
Automatic serve: if the ball reaches far back behind the bat a new serve is automatically initiated. The distance behind the bat which triggers the serve was adjusted so that the applause or crowd laughter have enough time to play
Procedure run at the end of the game (in this order): bring the ball in serve position (Serve_10), stop the demo, bring the end-of-game sprites into view (AC1=110), play the end_game sound effect, exit the sub
Conditional Do loop: every-thing here happens repeatedly every loop cycle (tens of times/second)
Sub Play_Tutorial_10()
Sub Play_Tutorial_10()
If ([S33] >= [P4] Or [R33] >= [P4]) Then
New_Game_10
RunPause = True
Else
RunPause = Not RunPause
End If
Dim Pt0 As POINTAPI
Dim Pt1 As POINTAPI
GetCursorPos Pt0
[AC1] = -999 ‘hide end-of-game sprites
Do While RunPause = True
DoEvents
GetCursorPos Pt1
[S6] = [P8] * (-Pt1.Y + Pt0.Y)
On Error Resume Next
Range(“R28:Y29”) = Range(“R27:Y28”).Value
If [P1] = “ON” And ([S33] < [P4] Or [R33] < [P4]) Then
Collision_Effects_10
End If
If Abs([R28]) > 180 * [P10] Then
Serve_10
End If
If ([S33] = [P4] Or [R33] = [P4]) Then
Serve_10
[O35] = “Demo OFF“
[AC1] = 110
Call PlaySound(ThisWorkbook.Path & “\end_game.wav”, 0&, &H1)
Exit Sub
End If
Loop
End Sub
-The end-
by George Lungu <excelunusual.com>
