Excel Hero and Animated Charts

October 22nd, 2010 by datapig Leave a reply »

Daniel Ferry is the purveyor of one of the more interesting Excel Blogs out there (ExcelHero.com). His blog has the curious distinction of being the only one with an unexplicable rooster on the banner:
Excelhero

If you’ve never visted, you must. Daniel has a knack for passing on advanced Excel techniques through

beautifully assembled Excel charts and examples.

 

For a guy like me (deep in the dark dank heart of the corporate world) it’s difficult to apply many of the wonderful things Daniel does on a daily basis. However, Daniel has come up with a very cool technique to animate business charts.

ChartAnimation

In Daniel’s original workbook, he relies heavily on named ranges and does require a good bit of setup. I took the liberty of taking his idea and creating a my own method for animating charts.

Too lazy to read on? I’ve got a working example you can download here.

Given that I like to have something more turnkey than his original example, I created this code cartridge, which anyone can plug into a workbook and it just goes.

Simply copy this code and paste it into a new module:

  1. Function AnimateChart(OldDataSet As String, NewDataSet As String)
  2. Dim NewData As Variant
  3. Dim OldData As Variant
  4. Dim AnimationArray As Variant
  5. Dim OldPoint As Long
  6. Dim NewPoint As Long
  7. Dim x As Integer
  8. Dim i As Integer
  9. Dim p As Double
  10.  
  11. NewData = ActiveSheet.Range(NewDataSet).Value
  12. OldData = ActiveSheet.Range(OldDataSet).Value
  13. AnimationArray = ActiveSheet.Range(NewDataSet).Value
  14.  
  15. For i = 1 To 15
  16. p = 100 / 100 / 15 * i
  17. For x = 1 To WorksheetFunction.Count(NewData)
  18. OldPoint = OldData(1, x)
  19. NewPoint = NewData(1, x)
  20. AnimationArray(1, x) = OldPoint - (OldPoint - NewPoint) * p
  21. Next x
  22. Range(OldDataSet).Value = AnimationArray
  23. DoEvents
  24. Next i
  25. End Function

Then just create a macro (which you can assign to a combobox) to call the function as needed:

  1. Sub Chart1Change()
  2. Const OldData As String = "B27:M27"  '<<Change this Range
  3. Const NewData As String = "B28:M28"  '<<Change this Range
  4. Call AnimateChart(OldData, NewData)
  5. End Sub

 

 

And for those of you who still have some training budget left, I would highly encourage you to consider attending Daniel’s

Excel Hero Academy

Only two days left to register!!

Advertisement

8 comments

  1. Gregory says:

    Nice piece of code. I liked the part about putting the data label position inside the bar if greater than 90% of the max scale. Sweet!

  2. Erin says:

    Daniel’s rooster makes sense to me. He’s looking for the beautiful and delicious seeds buried in Excel so he can give us these wondrous graphics and the tools to grow our own.

  3. Steve says:

    Its good to see this original work broken down for those of us who are new to programming in VBA/Excel. However, in the original file the bar transitions were slower – I think referred to as “Easing” – is there a way to slow the transitions here in your file ?

  4. datapig says:

    Steve: To get a slower transition, simply change the 15 to something greater (like 25).

  5. Jezza says:

    What codes do I add to make the graph animation show decimal numbers, rather then whole integers? Thanks.

  6. Shayam says:

    I have everything down except for the “data for chart” (Row 27 on your spreadsheet). How did you get these numbers to change without any formulas?? Any help would be greatly apperciated.

  7. I have noticed that credit restoration activity ought to be
    conducted with techniques. If not, chances are you’ll find yourself causing harm to your rating. In order to reach your goals in fixing your credit score you have to always make sure that from this time you pay your entire monthly costs promptly prior to their slated date. It is significant given that by never accomplishing this, all other measures that you will take to improve your credit ranking will not be useful. Thanks for expressing your thoughts.

  8. Lakshmisha says:

    What codes do I add to make the graph animation show decimal numbers, rather then whole integers

Leave a Reply