Bouncing Ball

In this tutorial I will show you how to create a simple 2D bouncing ball simulation in Excel. For those who know Mechanics, we'll be looking at the Increamental/Numerical Method. This is where you have an initial value and calculate the change per frame. For this model the values we need are an initial position, velocity and an acceleration.

The starting position for the ball is -5 meters horizontal and 5 meters above the ground. A small horizontal velocity so the ball move slowly across. For the acceration the gravitial constant of 9.81 has been set, and as that for goes down it's set to minus.

This is where we are going to store the values calculated each frame, the new velocity and the new position. Now to setup the macro, first we need some control values and two buttons Run and Reset.

Cell 'frame' is the number of the current frame, 'sec/frame', is how much a frame is worth in seconds for the calculation you might want to play about with this depending on the speed of the machine. 'total' is the end frame, 'Coefficient' is to do with friction and collision, put basicly it is the percentage of the energy thats left after a collision. Now last time we refered to each cell via absolute methods, "A1", "B1", etc. This time we'll give each cells names. The reason for names is once we've set up this model, we can move the cells around, but will still be linked via the name. Select cell B4 and in the dropdown box where it says B4 type 'Position' and press enter. You have now assigned the name 'Position' to Cell B4. Now select B5 and call it 'Velocity', B6 - 'Acceleration', B9 - 'SimVel' B10 - 'SimPos', F3 - 'frame', F5 - 'total' and F6 - 'Coefficient'. Now the buttons, control can also be given a name, one thats more useful that 'CommandButton1'. In design mode right click on a button and select properties.

In the properties window change the name and caption to 'Run', select the other button and change it to 'Reset'. While still in Design Mode double click on the Reset button and Enter the following: Private Sub Reset_Click() Range("frame") = 0 Range("SimVel")(1, 1) = Range("Velocity")(1, 1) Range("SimVel")(1, 2) = Range("Velocity")(1, 2) Range("SimPos")(1, 1) = Range("position")(1, 1) Range("SimPos")(1, 2) = Range("position")(1, 2) End Sub The above code resets the frame count and the simulation position and velocitys. Now if you double click on the Run button and Enter the following code: Private Sub Run_Click() Call Reset_Click While (Range("frame") < Range("total")) Range("SimVel")(1, 1) = Range("SimVel")(1, 1) + Range("Acceleration")(1, 1) * Range("frame")(2) Range("SimVel")(1, 2) = Range("SimVel")(1, 2) + Range("Acceleration")(1, 2) * Range("frame")(2) Range("SimPos")(1, 1) = Range("SimPos")(1, 1) + Range("SimVel")(1, 1) * Range("frame")(2) NewY = Range("SimPos")(1, 2) + Range("SimVel")(1, 2) * Range("frame")(2) If (NewY < 0) Then Range("SimVel")(1, 2) = -Range("SimVel")(1, 2) * Range("Coefficient") Range("SimPos")(1, 2) = Range("SimPos")(1, 2) + Range("SimVel")(1, 2) * Range("frame")(2) Else Range("SimPos")(1, 2) = NewY End If DoEvents Range("frame") = Range("frame") + 1 Wend End Sub

Now the above code might look very scary, trust me it's not. This is what it does: First we Resets the simulation, then start the while loop. In the loop we calculate the new velocity and position and check that this position hasn't gone below the X-axis. if it has we invert the current velocity reduce it by the coefficient and re-calculate the position. Lastly we refresh the spreadsheet and increament 'frame'. Now thats the simulation done, you have now modeled a ball bouncing, all we need to do is display the results. Select the Simulated Position Values and click on the chart wizard. Select a scatter chart, click next and select the series tab. Now make the 'X Values' point to the X simulated position cell and do the equivlent for the 'Y Values'. If you click on finish you'll now have a chart. Almost finish, now Excel Chart auto size the scales of the axis, what we need to do is lock them down. Select the X-axis of the chart and double click it, this should bring up the format axis dialog, now select the scale tab.

Untick the top four checkboxes set min and max to -5 and 5, the major unit to 1 and the minor to 0.5. Max and min is the range of the axis, major unit is the increament of the scale, set to 1 it will go -5, -4, -3, etc. If you have a small chart you might want to increase this value. Minor unit is only seen if minor gridlines are displayed, this should be set as a fraction of Major unit, a half, fifth, etc. or just left as Auto. Now click Ok and do the same to the y-axis the only difference is the minimum can stay as zero, but remember to untick auto.

Hopefully your final chart will look like the across. Make sure you not in design mode and click run. You should now see the ball bounce. Things for you to try: Different initial settings Adding another ball Collision with the Y-Axis I'll be continuing the Excel theme and post another tutorial some time next week. Not sure what about yet. If you have any requests, leave it in the comments or PM on 3dbuzz.com and I'll see what I can do.

Cheers thing2k