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:
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.
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:
- Function AnimateChart(OldDataSet As String, NewDataSet As String)
- Dim NewData As Variant
- Dim OldData As Variant
- Dim AnimationArray As Variant
- Dim OldPoint As Long
- Dim NewPoint As Long
- Dim x As Integer
- Dim i As Integer
- Dim p As Double
- NewData = ActiveSheet.Range(NewDataSet).Value
- OldData = ActiveSheet.Range(OldDataSet).Value
- AnimationArray = ActiveSheet.Range(NewDataSet).Value
- For i = 1 To 15
- p = 100 / 100 / 15 * i
- For x = 1 To WorksheetFunction.Count(NewData)
- OldPoint = OldData(1, x)
- NewPoint = NewData(1, x)
- AnimationArray(1, x) = OldPoint - (OldPoint - NewPoint) * p
- Next x
- Range(OldDataSet).Value = AnimationArray
- Next i
- End Function
Then just create a macro (which you can assign to a combobox) to call the function as needed:
- Sub Chart1Change()
- Const OldData As String = "B27:M27" '<<Change this Range
- Const NewData As String = "B28:M28" '<<Change this Range
- Call AnimateChart(OldData, NewData)
- End Sub
And for those of you who still have some training budget left, I would highly encourage you to consider attending Daniel’s
Only two days left to register!!