A college science professor named Roger Blickensderfer wrote to me about an animation he was working on. He wanted to trace the path followed by a point on the rim of a rolling wheel. Roger had cobbled something together, and I fiddled with it, and it kind of worked. But I decided I wanted it to do a bit more. I started with Roger’s basic idea and added my flexible animation from the Gas Prices – Animated Bar Chart for Excel example. I added a second point, which can be placed at a different distance from the axis of the wheel, and I added a checkbox to show or hide the curves traced by these two points.
I built an animation which can be extracted from this downloadable zip file. The top of the worksheet has the playback controls and other inputs to the left and the chart to the right. The first example uses a wheel radius of 10 and a second point at a radius of 0 (the axis of the wheel)
Press the button to start the animation. The red line stays at the same height, while the blue line traces a curve called a cycloid.
Here are the traces when the second point is at a radius of 5 (midradius).
Here are the traces when the second point is at a radius of 15 (outside the radius of the wheel rim). This is similar to the case of a train wheel, which has a flange to keep the tain on the track. The outer surface of the flange actually moves backwards at the bottom of its travel.
In this example, the second point has a radius of -10, so it appears on the rim opposite the first point.
I found another explanation of this in Rolling: the physics of wheels, on the web site of the School of Physics at The University of New South Wales. There is a large collection of similar animations on their Physclips page.
Tim Mayes says
Jon, this is really slick. Looks like you are about halfway to an Etch A Sketch. :-)
derek says
Sounds like a theme for your next competition: everybody enter their animated Excel charts.
Doug Jenkins says
Works nicely with Excel 2000 but in 2007 it goes straight to the end.
Adding:
Application.ScreenUpdating = False
Application.ScreenUpdating = True
before the DoEvents statement is one way to make it work, albeit slowly
Another approach to animation at:
http://newtonexcelbach.wordpress.com/2008/07/20/newtons-cradle/
Jon Peltier says
Doug –
I haven’t tried animations lately in Excel 2007. An oversight caused by my avoidance of that version. I recall that animations worked poorly during preliminary assessment of Excel 2007 years ago. Does switching ScreenUpdating off and on cause any flickering of the chart?
Update:
I just tested the rolling wheel and gas prices animations in Excel 2007 SP1. The animations worked about the same as in 2003, not quite as smoothly but without the bad behavior I recall from animations I tested in the past.
I also just tested these animations in Excel 2007 No SP (Thanks, Virtual PC). The gas prices animation worked as it did in 2007 SP1, but the rolling wheel advanced only one step in the rotation, then waited until all worksheet calculations were complete, before it jumped to the end.
Prior to SP1, Excel 2007 was positively sluggish at updating charts with more than a hundred or so points. If you haven’t upgraded to SP1, you might be suffering from this problem.
Alvaro Ledesma says
I really like this one. It is the first time in a while that I have no clue what was going on in a spreadsheet. I kept looking at the graph and trying to find your macros for the animation… and nothing. The full animation and controls are so simple and yet provide a lot of functionality with little code. It is an elegant solution for the task on hand.
Alvaro
Doug Jenkins says
Jon – I do have SP1 installed. I find the animation runs about half speed in 2007 (compared with 2000), with the screenupdating turned off and on at each step. That’s pretty typical of macros in 2007 that do a lot of interaction with the spreadsheet in my experience.
Jon Peltier says
Doug – Interesting. I find the macro runs more slowly in 2007, but the chart keeps up with the worksheet calculations.
AdamV says
In our A-level maths class (so aged 17 / 18 years) we each had to research a particular class of curves and present interesting facts about them (with proofs of course).
Cycloids are really neat – the length of one arc is four times the diameter of the circle which traces it. The area under the curve is three times the area of the circle.
Cycloids are also found as the solution to a couple of special cases for falling objects. Wikipedia does a good job of these here:
http://en.wikipedia.org/wiki/Brachistochrone_curve
http://en.wikipedia.org/wiki/Tautochrone_curve
Jon Peltier says
Adam –
Thanks for the information.
Jay says
In my experience I’ve found that switching screen updating off and on does speed up things (obviously) but when working with animations and especially on a slow computer, it can cause switchoever to the next “frame” a little sudden and therefore “jarring”. However I could be wrong as I use a box that was made a few million years ago :-)
Also if this helps anybody looking at ways of capturing the movement of the mouse in any of their animations, this example on my website may be useful:
http://www.databison.com/tracking-mouse-movements-in-excel/
Jay
Jon Peltier says
Jay – Thanks, that’s a cool demonstration. Your blog has quite a few other interesting posts, too. I’ll thank you in advance for “wasting” my time.