Excel Dancing Pendulums

A 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.

 

DANCING PENDULUMS

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.

 

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:

·         Pendulums

·         Control

·         Animation

Each is discussed below.

PENDULUMS

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 as ø = ø_max.sin( sqrt(g/L ).t ), where ø is in Radians and · is multiply, G is Gravitational Acceleration 981.24cm/s² and L is the Pendulums length.

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

In Excel

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.

 

NAMED FORMULAS

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

General Named Formulas

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

g                 =981.24                                     Gravitational acceleration, cm/s²

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

 

Named 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 measurement:

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. The 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 used.

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 Formulas.

 

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

 

THE CHART

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

A new series is added to the chart for each pendulum

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 Chandoo.org.

 

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 end.

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 Delete.

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 Pendulum.

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

ANIMATION

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 
            DoEvents
            ' Exit if Start/Stop Pressed
            If [D7] = "False" Then End 
        Next
    Loop

    ' 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 which:

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

o   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.

CONTROLS

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.

Time 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 section.

The Main Controls are Form Control Controls.

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 options.

Reset Parameters

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
 
        SetColors
        SetWires

End Sub

PENDULUM LENGTHS

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 that.

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.

 

LINKS

Pendulum Physics

Side Projects at chandoo.org

Newton Excel Bach

 

CLOSING

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:

Huis_Excel_Hero_Pendulum.xlsm.

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

Hui...