The previous section explained how to add a game score in the upper part of the pong court. This section (which is a continuation of part#8) will explain the VBA upgrades needed to make the score functional.
Excel PONG Tutorial #9 – animating the score display, adding new-game and end-game sound effects
by George Lungu
– The previous tutorial (#8) showed how to build a game score
display in the upper part of the pong court.
This section (which is a continuation of part#8) will explain the VBA upgrades
needed to make the score functional. In this section we will
add new-game and end-game sound effects too.
Next tutorial will explain how to create a “demo play” option which means
that both bats will be automatically run by worksheet logic.
53. Create a new copy of the worksheet
-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_8). Rename the new worksheet Pong_Tutorial_9.
-Using the VBA editor insert a new module, Module3. Copy all the code from Module2 into Module3.
-Change the name of the Serve_8 macro into Serve_9.
-Change the name of the Play_Tutorial_8 macro into Play_Tutorial_9.
-Change the name of the Collision_Effects_8 macro into Collision_Effects_9.
-Reassign the Serve_9 and Play_Tutorial_9 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.
-Also make sure you call the Collision_Effects_9 macro within the Play_Tutorial_9 macro.
54. Write the “New_Game_9” macro Sub New_Game_9()
– We introduced two more sound files in the same folder with the excel file: “new_game.wav”
– Create a new button and assign to it the macro
to the right (written in Module3)
– This macro will reset the score, bring the ball in
the serve position and play a little tune (the
[R33:S33] = 0
Call PlaySound(ThisWorkbook.Path & “\new_game.wav”, 0&, &H1)
On Error Resume Next
55. Upgrade the existing macros
All the collision effects (sounds and scoring) will happen only if the sound is enabled and the score is below the maximum score of 25.
If the score reaches the maximum of 25 the ball is reset (Serve_9 macro is called), the end_game sound effect is played and the macro is exit.
After the sound effects accompanying the “missed bat events” are triggered, a score increment is added. If Player#1 misses the ball, Player#2 scores one point and vice versa.
RunPause = Not RunPause
Dim Pt0 As POINTAPI
Dim Pt1 As POINTAPI
Do While RunPause = True
[S6] = [P8] * (-Pt1.Y + Pt0.Y)
On Error Resume Next
Range(“R28:Y29”) = Range(“R27:Y28”).Value
If [P1] = “ON” And ([S33] < 25 Or [R33] < 25) Then Collision_Effects_9
If ([S33] = 25 Or [R33] = 25) Then
Call PlaySound(ThisWorkbook.Path & “\end_game.wav”, 0&, &H1)
End Sub to be continued…
by George Lungu <excelunusual.com>