How to make a game of PONG in Excel – part #9

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.

We will add here 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.

[sociallocker][/sociallocker]

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.

<www.excelunusual.com>

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
“new_game.vaw”)

Sub New_Game_9()
[R33:S33] = 0

Call PlaySound(ThisWorkbook.Path & “\new_game.wav”, 0&, &H1)

On Error Resume Next
Serve_9
End Sub

<www.excelunusual.com> 2

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.

Sub Play_Tutorial_9()

RunPause = Not RunPause

Dim Pt0 As POINTAPI

Dim Pt1 As POINTAPI

GetCursorPos Pt0

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] < 25 Or [R33] < 25) Then Collision_Effects_9

If ([S33] = 25 Or [R33] = 25) Then

Serve_9

Call PlaySound(ThisWorkbook.Path & “\end_game.wav”, 0&, &H1)

Exit Sub

End If

Loop

End Sub

End Sub to be continued…

by George Lungu <www.excelunusual.com>

Leave a Reply

Your email address will not be published. Required fields are marked *