In the previous section a special ball return formula was implemented. Bat #1 was assigned the y-coordinate of the ball and in this way a very simple opponent algorithm was implemented.
The problem with this algorithm is that by having Bat #1 perfectly tracking the ball, the opponent was unbeatable.
Excel PONG Tutorial #6
by George Lungu
– adding skill levels to the virtual opponent
– In the previous tutorial a basic ball return algorithm was implemented.
Bat #1 was simply assigned the y-coordinate of the ball. Having Bat #1
perfectly track the ball movements, made the opponent unbeatable
which was a situation useful only for testing the game.
This section (which is a continuation of part#5) shows how to add adjustable skill
levels to the virtual pong opponent.
37. Create a new copy of the worksheet
– Copy the last worksheet into a new one. Rename the new worksheet “Pong_Tutorial_6”.
– For this section of the presentation keep the same macros from Module3.
38. Insert a new entry in the worksheet called Level Opponent
– As set up in the last tutorial, Bat #2 perfectly tracks the ball y-coordinate.
We will build a new setup in which we will have the same bat follow the
ball with an adjustable speed value, proportional to the opponent’s skill
– Insert the label “Level Opponent” in cell O6 with font size 14 bold.
– Create a spin button: View => Toolbars => Control Toolbox => click Design Mode in the control toolbox => drag draw a spin button => right click Properties.
– Modify properties: change name to Level_Opponent, change min: 1 and max: 20.
– Double click the button and change the new code you see there into the following:
Private Sub Level_Opponent_Change()
Range(“P6”) = Level_Opponent.Value
Make sure to exit the design mode before using the macro by clicking the
“Design Mode” icon in the Control Toolbox !
39. Bat #1: y-speed formula and y-coordinate formula
– Instead of tracking the coordinate of the ball let’s have Bat #1 move towards the ball y-coordinate with a speed proportional to the skill level of the virtual opponent (vmin and vmax are proportional to the skill level of the opponent).
– The problem with this algorithm is that Bat #2, once gotten near the ball y-coordinate will start bouncing up and down since it overshoots the ball position.
– A better algorithm will be the almost identical to the first one but around the origin makes a smooth linear passage between vmin and vmax vmin
A simple implementation of the formula is given below (K1 and K2 are constants).
Ba t#2 Ba ll Ba t#2 1 Ba ll Ba t#2 2 skill
40. Excel implementation
By experimentation, K1 and K2 were chosen to 0.05 and 0.5 respectively
Bat y-speed and y-coordinate:
V27: “=V28+W27*Y23” Ba t#2_current Ba t#2_ previous y Ba t#2_current
41. Testing the new model
-Using various values for serve speed, bat size and opponent skill level verify the functionality of the model
– Adjust K2 to your preference. A larger K2 means a faster opponent and a lower K12 means a slower opponent.
by George Lungu <excelunusual.com>