In this part of the tutorial the analysis of the ball movement is taken farther, to include such effects as bouncing off the walls of the court and collision with the bats.
[sociallocker]
[/sociallocker]
Excel PONG Tutorial #4 – combining kinematics with collision events
by George Lungu
– In this tutorial (which is a continuation of part#3) the kinematics of
the ball is further analyzed.
– three different of collision effects are introduced. These effects are
the wall collision effect, the bat collision effect and the bat miss effect
– These effects play an important role in the play, scoring and sound
effects involved in the game
25. Create a new copy of the worksheet and macros
-Copy the last worksheet into a new one. Rename the new worksheet “Pong_Tutorial_4”.
-Insert a new module, Module3, using the VBA editor
-Copy both macros from Module2 into the new module (including the RunPause variable declaration)
-Change the name of the macros:
Dim RunPause As Boolean
Sub Serve_4()
Range(“R28:Y30”).Clear
Range(“R28:U28”) = Range(“R23:U23”).Value
End Sub
-Reassign the new macros to the buttons in the new worksheet
Sub Play_Tutorial_4()
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
Loop
End Sub
—————————————————————-
26. Inserting the “collision event table”
– In the range R15:T19, create a table with logical
collision information formulas
– We need this information later in the Kinematics
formulas to see when and how we need to take
trajectory change decision
– In later models there will be various sounds
triggered by the collisions
– Populating this table with formulas will be
explained in the following pages
<excelunusual.com> 2
27. Horizontal (upper or lower) wall collision formulas
– The ball movement is created by changing the position of the ball in time. Therefore the ball trajectory is a
string of dots.
– A natural way to detect collisions is finding “wall straddles” of the ball trajectory, which means looking for two
consecutive points along the ball trajectory when the ball is on one side of the wall at a certain time and on the
other side of the wall in the next time step.
– When this condition occurs we will use it to revert the sign of the perpendicular (to the wall) speed component
while leaving the speed component parallel to the wall unchanged.
If (Dn-1* Dn) < 0 a collision is detected. The sign of the perpendicular speed component needs to change in order to turn the ball away from the wall (red)
<excelunusual.com> 3
Let’ see how we write the collision detection formula taking into consideration the width and length of the court and
the present (x0,y0) and past (x-1,y-1) coordinates of the ball (BR stands for ball radius):
The ball must be within the court horizontal boundaries.
The absolute value of the current y-coordinate of the ball exceeds half of the width of the court minus the ball radius.
The absolute value of the past y-coordinate of the ball is less than half of the width of the court minus the ball radius.
The above formula works well because the system of coordinate is placed exactly in the center of symmetry of
the court (both x and y). We also assumed that the x-axis of this system of coordinates is parallel to the length
of the court and the y-axis is parallel to the width of the court. Translated in spreadsheet formulas the above
expression becomes:
T15: “=AND(R27>-V9/2,R27<V9/2,ABS(S27)>V10/2-V11,ABS(S28)<=V10/2-V11)”
28. Ball-bat collision formulas
– A similar logic applies to the ball-bat collision formulas
– In this case however, there are two different event detections:
1. The ball is hit and it must bounce back into the court reversing the sign of the horizontal speed component (the “collision with bat” event)
2. The ball is not hit and it must continue its flight – one party wins a point (the “missed bat” event)
<excelunusual.com> 4
How do we write the ball-bat collision detection formula for Bat #1 taking into consideration the length of the court,
the distance of the bat from the x-end of the court (Players_in), the ball radius (shorthand BR) the present (x0,y0)
and past (x-1,y-1) coordinates of the ball?
The ball must be within the vertical The current x-coordinate of the ball The past x-coordinate of the
boundaries of the bat (with an added 10% exceeds the coordinate of Bat#1 (in ball is within the coordinate
bonus – without that it would just be the negative direction) plus the ball of Bat#1 plus the ball radius
Bat_size/2. By playing it I found out that 10% radius over-extension gives the player a better feel for the game)
How do we write the ball-bat collision detection formula for Bat #2 (which is located to the right of the court)?
Translated in spreadsheet formulas the above expressions become:
T16: “=AND(S5-P12/1.8<=S27,S27<=S5+P12/1.8,R27<-V9/2+V8+V11,R28>=-V9/2+V8+V11))”
T18: “=AND(S6-P12/1.8<=S27,S27<=S6+P12/1.8,R27>V9/2-V8-V11,R28<=V9/2-V8-V11)”
<excelunusual.com> 5
29. The missed-bat event formulas
– This event is important since it is a score changing event, moreover when the bat misses the ball there will be a
special sound triggered by the Play macro
– How do we write the missed-bat event detection formula for Bat #1 taking into consideration the length of the
court, the distance of the bat from the x-end of the court (Players_in), the ball radius (shorthand BR) the present
(x0,y0) and past (x-1,y-1) coordinates of the ball? It is very similar to the ball-bat collision event.
The ball must be out of the vertical The current x-coordinate of the ball
The past x-coordinate of the boundaries of the bat (with an added exceeds the coordinate of Bat#1 (in the ball is within the coordinate 10% bonus) negative direction) plus the ball radius of Bat#1 plus the ball radius
How do we write the missed-bat detection formula for Bat #2 (which is located to the right of the court)?
An outline of all the collision event formulas are presented below:
T16: “=AND(S5-P12/1.8<=S27,S27<=S5+P12/1.8,R27<-V9/2+V8+V11,R28>=-V9/2+V8+V11)”
T17: “=AND(NOT(AND(S5-P12/1.8<=S27,S27<=S5+P12/1.8)),R27<-V9/2+V8+V11,R28>=-V9/2+V8+V11)”
T18: “=AND(S6-P12/1.8<=S27,S27<=S6+P12/1.8,R27>V9/2-V8-V11,R28<=V9/2-V8-V11)”
T19: “=AND(NOT(AND(S6-P12/1.8<=S27,S27<=S6+P12/1.8)),R27>V9/2-V8-V11,R28<=V9/2-V8-V11)”
by George Lungu <excelunusual.com>
