Podcast: Play in new window | Download

Subscribe: Apple Podcasts | RSS

Three week ago I visited the Newton Excel Bach web site where I spotted the Dynamically Defined Dancing Pendulums NewtonExcelBach.

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

This post will not go through the logic of constructing and animating the pendulums in Excel as I have described that over at Excelhero.com and readers who are interested are encouraged to visit there and explore the techniques used.

During the Pendulum project I came across two sub-projects which I felt are worthy of a post here at **Chandoo.org** as they are a great demonstration of some basic VBA techniques as well as demonstrating the ability of VBA to quickly simplify repetitive tasks.

Sample files are attached for Excel 97-2003 and Excel 2007/10 users to follow through the examples.

**PENDULUM SIDE PROJECTS**

The Pendulum project consists of 16 Pendulums. Each Pendulum requires 4 Named Formulas, meaning that the projects needs 64 Named Formulas for the 16 Pendulum, as well as adding 16 Series to the chart.

(The above animated GIF is a very poor representation of the smooth scrolling achieved in the Excel animation)

I thought it would be a great idea to see if these jobs could be automated and hopefully save some time during the process.

The result was 2 simple VBA routines which will be described below:

**NAMED FORMULAS**

Each Pendulum in the project was based around 4 Named Formulas

**Named Formula for each Pendulum**

**p1Len** =’1′!$B$9 The Length is stored on the worksheet.

**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 only difference between the formulas for Pendulum 1 and Pendulum 2 etc is the replacement of the names of p1 with p2 in the various Named Formulas and of the associated formulas.

On a worksheet **Named Formulas** a number of formulas were written which display the Named Formulas as required above. Then a small VBA routine was written which loads the Named Formulas.** **

**How**

On the Named Formulas worksheet, I have added two columns of formulas for the various Named Formulas required.

For the Pendulum Length Named Formulas

For the Pendulum Angular Position Named Formulas

For the Pendulum X Position Named Formulas

For the Pendulum Y Position Named Formulas

When the formula above are copied down they adjust for the various pendulums numbered 1 to 16 based on the row numbers.

I then setup a VBA routine, **Load_Named_Ranges**, shown below which loads the Named Formulas.

**To Use**

Select some or all of the required Named Formulas from the Name Column. That is the code will only load the **Selected** Named Formulas, allowing the user to load 1 or 2 Named Formulas, for testing purposes, or all the Named Formulas if you choose.

Then Execute the **Load_Named_Ranges** subroutine either using the Big Red Button or directly within the VBA Editor.

The following will load Named Formulas **p3Len **to** p7Len**.

The **Load_Named_Ranges** subroutine is shown below:** **

*Sub Load_Named_Ranges()*

*Dim c As Range*

*For Each c In Selection*

*ActiveWorkbook.Names.Add Name:=c.Text, RefersTo:=c.Offset(, 1).Text*

*Next*

*End Sub*

**What does the code do?**

The code:

1. Defines the start and name of the subroutine,

*Sub Load_Named_Ranges()*

2. Defines a variable c as a Range object,

*Dim c As Range*

3. It then loops through each cell in the selection and assigns it to the variable ‘c’;

*For Each c In Selection*

4. It then adds a new Named Formula,extracting the Name from the Text Value of ‘c’ and extracts the formula from the cell directly to the right of cell ‘c’;

*ActiveWorkbook.Names.Add Name:=c.Text, RefersTo:=c.Offset(, 1).Text*

The Name and Formula (RefersTo) both use the Text of the cell, which is what is displayed.

5. It then loops through each cell in the selection until it has done them all;

*Next*

6. Defines the end of the subroutine;

*End Sub*

**Lets Test It**

To test the subroutine we will first delete all the Named Formulas beginning with “p”

Goto the Formula Ribbon Bar and select Name Manager

Select all the Named Formulas that begin with “P” and press the delete button

Accept any warnings

Try and Run the Pendulum’s

Nothing happens as there are no formulas

**Ensure the Pendulum are turned off, as the code is still running behind the scenes.**

Now Goto the Named Formulas Page

Select all the Named Formula Names in Name Column; B3:B66

Click the Load Named Formulas, button

Go back to Page **1** and try and run the Pendulums now.

**ADD CHART SERIES**

The second sub-project was the addition of 16 Chart series to the Chart, 1 for each Pendulum.

Using the logic of the Named Formulas VBA code above, the 16 Chart Series Names, X Values and Y values were developed using formulas on the **Add Cht Series** worksheet and then loading into a chart using a simple VBA routine.

The **Add_Cht_Series **subroutine is in the** Add Cht Series **sheet object in the VBA editor.** **

**How**

On the **Add Cht Series** worksheet, I have added three columns of formulas for the various Named Formulas required.

For the Pendulum Name, X Range and Y Range.

When these formulas are copied down they adjust for the various pendulums numbered 1 to 16.

I have then setup a VBA routine, **Add_Chart_Series**, shown below which loads the Named Formulas.

To use Select some or all of the required Chart Series from the Pendulum Name column.

Then Execute the **Add_Chart_Series** subroutine using the big red button.

The **Add_Chart_Series** subroutine is shown below:

*Sub Add_Cht_Series()*

*Dim sNumb As Integer*

*Dim c As Range*

*Worksheets(“1”).ChartObjects(“Chart 5”).Activate*

*For Each c In Worksheets(“Add Cht Series”).Range(“B19:b20”)*

*sNumb = ActiveChart.SeriesCollection.Count + 1*

*ActiveChart.SeriesCollection.NewSeries*

*ActiveChart.SeriesCollection(sNumb).Name = c.Text*

*ActiveChart.SeriesCollection(sNumb).XValues = c.Offset(, 1).Text*

*ActiveChart.SeriesCollection(sNumb).Values = c.Offset(, 2).Text*

*Next*

*End Sub*

** **

**What does the code do?**

The code:

1. Defines the start and name of the subroutine,

*Sub Add_Cht_Series()*

2. Defines a variable sNumb as an integer, and a variable c as a Range object

*Dim sNumb As Integer*

*Dim c As Range*

3. It then activates the Chart containing the pendulum

*Worksheets(“1”).ChartObjects(“Chart 5”).Activate*

4. It then loops through each cell in the Range defined by the Range, in this case *B19:B20 *and assigns it to the variable ‘c’; You can adjust the Range to suit.

*For Each c In Worksheets(“Add Cht Series”).Range(“B19:B20”)*

5. It then counts how many existing series are in the chart and sets the next Series Number sNumb to that value + 1.

*sNumb = ActiveChart.SeriesCollection.Count + 1*

6. The next 4 lines add a new series to the chart and setup the new series Name, X Value and Y Values. The Name, X Value and Y Values are retrieved from the Text of the cell c and the adjacent two cells using a Range Offset modifier

*ActiveChart.SeriesCollection.NewSeries*

*ActiveChart.SeriesCollection(sNumb).Name = c.Text*

*ActiveChart.SeriesCollection(sNumb).XValues = c.Offset(, 1).Text*

*ActiveChart.SeriesCollection(sNumb).Values = c.Offset(, 2).Text*

7. It then loops through each cell in the selection until it has done them all;

*Next*

8. Define the end of the subroutine;

*End Sub*

**Lets Test It**

To test the subroutine we will first delete a few of the Chart Series

Select the Chart

Select two Pendulums noting the series Number of the Bobs eg: 9 & **10**

Goto the Add Chart Series Worksheet

Note the Range Corresponding to the 2 missing Pendulum B11:B12

Goto the VBA Editor

Adjust the Line

*For Each c In Worksheets(“Add Cht Series”).Range(“B11:B12”)*

With your cursor in the Subroutine press F5 once only

Go back to Page 1 and you should now have 2 New Pendulum

Run the Pendulums now.

You will have to manually set the shape of the Bobs to a Circle and size 15 and re-arrange the order of the series to ensure they are in order, but you can practice that manually.

**SUMMARY**

The post has shown how using some very simple VBA and a bit of lateral thinking to put together some simple tools to simplify 2 common and repetitive tasks.

In the Named Formulas case, the code took less than 2 minutes for me to write and then another 5 minutes to do the formulas for the Named Formulas. I didn’t try but I am sure it would have taken a good 20+ minutes to enter 64 Named Formulas.

Writing this post took much longer than doing the whole Pendulum Project.

Two examples during my working career, where VBA code has been used to save massive amounts of time and money:

In the first case I wrote some code to combine data from several hundred workbooks with varying numbers of sheets up to 30 and differing quantities of data on each sheet, a task that could have taken weeks manually with the included opportunity for errors to be introduced, into a subroutine which took 30 minutes to run and gave a printout of the results including what files, sheets and rows of data were included in the import.

In a second case a Number of Workbooks, a Word template and some VBA code was used to replace a person whose sole job was to manage that data. This job saved the company $50k+ per annum and the task was given to a clerical person who could now do the task in their spare time.

** **

**LINKS**

Huis Excel Hero Pendulum: http://www.excelhero.com/blog/

Pendulum Physics: http://hyperphysics.phy-astr.gsu.edu/hbase/pend.html

Newton Excel Bach: http://newtonexcelbach.wordpress.com/2011/05/25/dancing-pendulums-2/

** **

**What could your simplify by using automation within**** Excel** **?**

**Excel**

What could you simplify or speedup using Excel automation?

Let us know in the comments below:

## 19 Responses to “Automating Repetitive Tasks”

Something I'd love to automate is adding a new line, on several tables at once, with new dates in the first column. I'm not there yet, with VBA, though.

Thanks

Coolest post EVER

mind blowing! great job!

look forward to learn some VBA basics in future posts.

So cool!!

I have been using VBA to simplify many of my daily repetitive tasks. Each day, I export a large spreadsheet (from an even larger database) containing a couple hundred products from a couple dozen companies, with various data tagged to each product (about 40 columns of data). I use VBA to quickly apply filters to the data and produce a separate worksheet for each company that lists all their products and releveant associated data. I then use VBA and a couple of dashboard techniques from here to display (simply) the position of each product in its respective lifecycle in a flowchart-style graph. Manually, this process used to take a couple of hours each day (and the flowchart graph didn't even exist). Once I got the code running, it now takes about 15 seconds to run completely, and it gives me instantaneous situational awareness into each product I track. Very nice when the boss asks for some obscure fact that would normally take 10 or 20 minutes to manually research and I can have the answer to him in seconds while he is still on the phone.

Did it simplify things for me? Yes. Automate? Not really; I still have to manually push the buttons that tell my code to run. Nothing I do compares to this post! This was amazing; I did not know Excel was capable of something like this. I still don't quite understand it all, but I plan to study it until I do. I guess I am a long way from being "awesome" yet!

Thanks for walking us through this technique!

Hui or Chandoo can we see a special post about Named Formulas. How they work and why use them. Documentation on the web is short about them.

Crikey! So flippin' cool! Chandoo always has the best Excel tricks!

taking away from the cool topic and general great work... this is one of the best written articles on here and many other blogs. Y? Whilst it gives full explanations I can skip over the bits I know easily.

on the animation: one negative! it's not 3 dimensional. the highest series always sits on top and thus you don't quite get a true 3d spinning effect.

So is there a way that you can promote/demote a series order using somekind of named formula or function or VBA trick!

@All

Many thanx for the kind words

I had a lot of fun making this project and am very proud of how it turned out.

I really appreciate the kind words.

@Stephen

Thanx for your comments, but the model and the real example isn't 3D.

It is a series of 2D stacked planes.

The way Excel Charts keep series in front/behind other series accurately models what happens in reality. Check out http://www.youtube.com/watch?v=yVkdfJ9PkRQ

@HUI

Now I understand the story, I see it works just as it should. However, it has got me thinking about the order of series. I produce a stacked column chart which needs to be stacked largest to smallest (always). The only way I've found to do it is to have a seperate series for each column! Any ideas?

Great!!

[...] Last week I published a post on constructing an Animated Pendulum at ExcelHero.Com with a sister post here at Chandoo.org on using simple VBA to Automate Repetitive Tasks. [...]

Hi chandoo,

Really impressing.. Great work

Please tel can we move emails in lotus notes which contains same words in subject to a particular folder by using macro.

For ex: DA-125648 is mention in subject line this contain word is in 50 emails i want to select those emails & move in some folder.

Thanks in advance

Dear Chandoo,

I want to say sorry for posting comment on wrong post

i have many excel workbooks in my folder,in each

workbook has several rows contains specific word & i want to pick rows containing that specific word using macro.

i know you can make it.

i request you to please teach me how to do that

Thanks in Advance

shree

Mind Blowing...!!!

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

Hi I'm rather new to Macro's and have set up a rather large spreadsheet for month end, but the data rows change each month and I have got the Macro to add rows if the raw data requires it but cannot seem to make it take the rows out when not required?

Thanks Mo

Me again actually the Macro is only working to the initial row that it followed me on so if there are more rows its not moving the formulas down pass that row damm so maybe I need to do more homework and sadly Downer will not pay for me to do one of your courses Thanks Mo