Excel Dancing Pendulums


Guest posty by Ian Huitson (Hui

Firstly, a thank you to Daniel for allowing
me to make this post on his Excelhero.com blog.

I have admired Daniels work since he first
started this blog and this post will demonstrate some of the Charting, Named
Formula and VBA techniques that I have picked up from studying the examples highlighted
throughout the site and from what he also teaches in the Excel Hero Academy.



A few weeks ago I visited the Newton Excel
Bach web site where I spotted the Dynamically Defined Dancing Pendulums

Having noticed that Doug had done a nice
animation in Strand7 (a Finite Element Analysis program), I thought “I can do
that in Excel” and so I did.



Excel Dancing Pendulums



This post looks at the techniques used
within Excel to produce this model.

I am not going to describe the Physics
involved in Pendulum motion here but I am going to discuss the implementation
of this model in Excel.

The Pendulum Project is broken into 4 Main areas:




Each is discussed below.




A pendulum consists of a Fixed End
(Fulcrum), Moving End (Bob) and Connector or Wire.

I am not going to describe the Physics
involved in Pendulum motion
nor the mathematics

But we need to know a few simple things about
the pendulum.

One point will be fixed at the fulcrum and
assigned an arbitrary location of (x=0, y=0), we choose this to simplify the
maths, the other/moving end, the Bob, will move according to the equations of
pendulum motion.

For a single Pendulum we need to know the
location of the pendulum at any time, t.

The location is expressed in angular terms
ø = ømax.sin( sqrt(g/L ).t ), where ø is in
Radians and · is multiply, G is Gravitational Acceleration 981.24cm/s2
and L is the Pendulums length.





Knowing the pendulums angular location ø
and its length, we can transform that to X & Y coordinates using simple




In Excel a Pendulum can easily be
represented on an Excel Scatter Chart as a single series consisting of two points
with a joining line.





We can then add any number of new series to
represent different pendulums.

Each Pendulum is modelled as a single
series consisting of 2 coordinates, one being the fixed or Fulcrum end which
we will locate at (0,0) and the Bob of the Pendulum, which will move according
to the equations for the pendulums motion.

To setup the pendulum I have used Named Formulas for the Coordinates of the Pendulum.

The advantage of Named Formulas is several
but Named Formulas,

Calculate blindingly fast;

Are flexible enough to allow us
to represent our two point series; and

Can easily be controlled using
some simple VBA code to allow animation

Can easily be controlled using built
in Excel Controls.



To setup a Pendulum in Excel we will use a
number of named formulas.


Named Formulas

t                 =0                                                 Run
time starts at 0 and increments every unit

g                 =981.24                                     Gravitational
acceleration, cm/s2

OMax       =’1’!$B$4*PI()/180               Max angle of
Pendulum, radians.



Formula for each Pendulum

Each pendulum is represented by 4 Named
Formulas, starting with p1 for pendulum 1 and then a suffix for the various


p1Len =’1’!$B$9 The Length is stored on the worksheet (Discussed later)
p1o =OMax*SIN(SQRT(g/p1Len)*t) Current angular position of Pendulum 1 at time t
p1x =p1Len*SIN(p1o)*{0;1} Current orthogonal X position of Pendulum 1 at time t
p1y =-p1Len*COS(p1o)*{0;1} Current orthogonal Y position of Pendulum 1 at time t


The formula for the X and Y locations are
worth examining. T
he Excel Scatter Chart needs a Range of
size 2 to retrieve the Start and End X locations of the Series and an
associated range for the Y values for that series.


In this model we will be using a Named Formula for the X and Y values. This is done in the Named Formula using an Array.


So for the X Series =p1Len*SIN(p1o)*{0;1}

The {0;1} are the two points of the Series
representing the pendulum.


In this case values of 0 and 1 have been

The Fulcrum End will always return 0 as it
is multiplied by 0

The Bob end of the Pendulum will return the
value p1Len*SIN(p1o)

And similarly for the Y values for the
series =-p1Len*COS(p1o)*{0;1}


Noting once again the use of the Array {0;1}
for the series and the ‘-‘ so that
all Y values are below 0.


It should be noted that these 4 formulas could have been combined into 2 formula for the X & Y position as:


p1x            =’1’!$B$9*SIN(OMax*SIN(SQRT(g/’1’!$B$9)*t))*{0;1}

p1y            =-‘1’!$B$9*COS(OMax*SIN(SQRT(g/’1’!$B$9)*t))*{0;1}              


This would have meant that there would only
be 32 equations for the 16 Pendulum rather than the 64 used.


It is felt that for clarity of discussion
and readability of the example this method would be clearer.


Side project


The above 4 named fromulas need to be entered
for each of the other 15 pendulums.

During this project I instigated two small
side projects.


The first was a small piece of VBA code to
import the named formulas into Excel from a range of worksheet formulas.


The second was a small VBA code to add the
16 series to the Chart with all the details once again sourced from a worksheet


The construction of these two projects is
covered in detail at Chandoo.org.




Once the named formulas are established, a
Chart can be added to the sheet.

A new series is added to the chart for each

Series Name:              1

Series X Values:         =’1’!p1x

Series Y Values:         =’1’!p1y


These were repeated for each of the 16
Series. Once again this process was automated and is described in detail at 


Each series was then selected and a Built
in Circle Marker put at one end of Size 15 and a small marker put at the other

The Axis need to be scaled so that the
scale of each axis is equal or the pendulums will swing in a non circular path.
This is easily done manually by adding a Square Shape to the Worksheet and changing the chart’s size so that the two axis are the same length.





Once they are correctly scaled the Axes can
be removed along with the Titles and Grid Lines, by selecting them and pressing

The Bob was then changed to a Circle, The
fill color was set and it was resized to 15 Pts. The Pendulum’s wire, the
series line, was also set to a Black Color and resized to 0.5 Pt.

The above is then repeated for each

In the Controls section below, I will discuss
how to change the colors of the Bob and Wires of the pendulum during the



In the above section we saw how to setup a
Pendulum as an Excel scatter chart based on a number of Named Formulas. The Named
Formulas ultimately rely on a single named constant, t,  which holds the time value.

To animate the chart all we need to do is
increment the time named formula and the chart will adjust itself to the new
position based on the revised time.

To do this I have used a simple piece of
code which is shown below:

The animation code:

Sub Pendulum()
Dim ti As Double
Dim i As Double
' Stop people breaking execution
Application.EnableCancelKey = xlDisabled
' Set a default Mouse Cursor while animating
Application.Cursor = xlNorthwestArrow
ti = [tinc]
Do While [D7] = "True"
For i = 0 To 2000 Step ti
' Add a Name to the Name Manager
' called t with a new Time Value i
Application.Names.Add "t", i
' Exit if Start/Stop Pressed
If [D7] = "False" Then End
' Reset Mouse Cursor
Application.Cursor = xlDefault
End Sub


The code loops a variable i from 0 to 2000
in steps obtained from B3 via the ti variable.

For every loop of the variable i, the code:

Stores a new named constant ‘t’
with value = i

Executes a DoEvents command

allows the Chart to Update
based on the new X & Y values as a result of the new time ‘t’

Checks any keyboard/mouse input

It then checks the value from
D7 and if False exits, or if True continues

The use of minimal and simple VBA code and named
formulas results in a system that can update quick enough to result in a very
smooth animation.




A number of user definable controls have
been added to the Pendulum Project

The most obvious of these are the Check
Boxes and Option Buttons on the Chart, but there is also a number of numerical
inputs and a slider.





Inc, Swing (Max) and Gravity

The 3 cells B3:B5 hold values for the Time
Inc, Swing (Max) and Gravity respectively.

These can be changed either before execution
of the pendulum or during execution. The Gravity is also controlled by the
slider to its right. The slider is linked to the cell D5 under the slider and
the Cell B5 divides the sliders value by 10.

The other two cells which allow manual
input are C9 & C10 and these will be discussed in the Pendulum lengths

The Main Controls are Form Control

The Start/Stop Check Box is linked to the cell
D7 as well as executing the Sheet1!Pendulum_Animate
subroutine which controls the animation.

The Pendulum Color Option Buttons are
grouped and linked to E9 and pressing any option button executes the Set_Pendulum_Colors subroutine. Which
reads the value of Cell E9 and calls the Color_Markers
subroutine with the Color type (1=Colored, 2=Grey & 3=Zebra) as an option.

The Wire Color Option Buttons are grouped
and linked to E10 and pressing any option button executes the SetWires subroutine. Which reads the
value of Cell E10 and calls the Color_Wires
subroutine with the with the Wire type (1=Black, 2=Grey & 3=None) as



A subroutine, Reset, has
been provided which allows either user defined reset of all parameters as well
as on being executed automatically on startup.

Sub Reset()
With Worksheets("1")
.Range("D5").Value = 981.42 'Gravity
.Range("D7").Value = "False" 'Starting position
.Range("E9").Value = 1 'Bob Color
.Range("E10").Value = 1 'Wire Color
.Range("B3").Value = 0.015 'Initial Time Inc
.Range("B4").Value = 15 'Initial Swing Angle
End Sub


Earlier in the post I mentioned that the
use of named formulas was much faster than cell references, which it is. So why
are the pendulum lengths stored on the worksheet?

The calculation of the length of the
pendulums is important to ensure the harmonics are correctly modelled.

To get the nice synchronous effects seen
during a long run of the animation the series must be in harmony with each
other. This simply means that after a certain time that the pendulums will
re-align themselves.

The simplest way to do this is to choose
lengths of pendulums which are suited to integer repetitions within the same
time frame.

That is, choose a pendulum length so that
its natural frequency is evenly divided into say 60 Seconds.

The cell formulae from B8 to C24 do just

Column C sets up a number of Integer
Frequencies based on the values in C8 and C9.

The lengths in Column B are then pro-rated
to a standard 60cm pendulum based on the required Cycles/T.

You can change C9 & C10 during
execution to see what effect it has.



Pendulum Physics

Side Projects at chandoo.org

Newton Excel Bach



You can see by the animated GIF at the top
of the post and the attached workbook that smooth animation of 16 Pendulum is
possible within Excel.


Here is the workbook:



For best results, run the dancing pendulums with no other workbooks open…






Here’s a list of other animated charts on Excel Hero:
 – solarsong
 – Excel, A Presentation Platform (Number Spiral)
 – Lilac Chaser (Optical Illusion)
 – Stereokinetic (Optical Illusion)
 – Illusory Contours (Optical Illusion)
 – Breathing Square (Optical Illusion)
 – Enigma (Optical Illusion)
 – Two Sinusoids (Optical Illusion)
 – Perpetual Collisions (Optical Illusion)
 – Freezing Rotation (Optical Illusion)
 – Reverse Spoke Illusion (Optical Illusion)
 – Stepping Feet Radial Illusion (Optical Illusion)
 – Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

Excel XML – Dynamic Periodic Table

When we say Excel XML, there are several things we could be referring to. In fact, the newer Excel file formats actually use XML natively to save workbooks – these are the Excel file types that end in “x”, such as my_model.xlsx.
But what I am writing about today is how easy it is to use XML in Excel. XML files are often from a web source that keeps the information within the files current. Sometimes these sources are referred to as web services, as the files can be generated from a database, on demand.

If you want to use XML data in your workbook, you can do so in a huge number of ways with VBA. But there is an extremely simple solution that requires no programming whatsoever. The easiest way to facilitate an XML to Excel conversion is to employ the New Web Query dialog. Just select the Data tab on the Ribbon and then click on From Web. Enter the address of the XML file and click Go.
Excel does a great job of removing all of the XML tags, leaving just the data we want. Click Import.  If the XML file does not refer to a schema, Excel creates one automatically and is very accurate when doing so.
The result is an Excel Table placed at the location you specify. You can now use this data in any way you would normally use Excel data. But there is a huge bonus here. You can REFRESH the table whenever you like and if there is newer data available from the source, the table updates itself. This means your entire model can be updated, simply via an update at the source.
To demonstrate, please download my dynamic Periodic Table Excel template.
It is built on the foundation of several XML source files. To be sure, the data that describe the chemical elements do not change at break neck speed, but they do change as new elements are discovered and better experimentation procedures produce more accurate property values.
I modeled this template based on a dynamic web periodic table that I created in 2007. It works great in Chrome, Firefox, and Safari, but only works (slowly) for version 8 of Internet Explorer. If you scroll to the bottom of the web version, you will see the links to the XML data.
The Tables in the Excel version use these XML links. So when I update those XML files on my server, your copy of the Excel template will update as well. Cool, no?
This Excel XML technique could be used in a myriad of business applications.
Please let me know what you think of this technique, the Excel template, and even the dynamic web Periodic Table.
Learn Advanced Excel!
Advanced interface design is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning.
I’ve received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they’ve ever made. Wow.
In a few weeks I will be offering the course again. There’s already over 300 people on the interest list for the next class. Why not join us? Just fill the form below and I’ll send you more information as we get closer!
And finally, if you have some interest in my upcoming VBA-only courses offered through the academy, please fill in this interest list form:
If you are reading this in email or RSS and connot see the above form, please click here.