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.

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