+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
In my humble opinion this is one of the most interesting advanced Excel charting projects I've ever done. I call it celtic muse, as it stirs my Scottish soul.
Click this image for a Flash Movie recorded from celtic muse.
If you enjoyed solarsong, I think you'll be fascinated by this. It uses the Windows DirectShow API to play a haunting celtic strain. The show is very much enhanced by the soundtrack so please be sure to download the workbook!
My inspiration for celtic muse was the "1,000 Blocks" sculpture by Curtis Steiner of the Seattle Art Museum. Using 1,000 identical walnut blocks he has created an almost unbelievable number of truly inspired tiled images. Each image uses all 1,000 blocks arranged in a grid 20 tall by 50 wide. Each block looks like this:
I thought it would be interesting to recreate his Flash presentation using Excel 2007. I'm calling this type of chart an Excel Tile Chart as each element of the image is one of the six faces of the above block. This technique could be very useful for displaying mathematical tessellations.
The advanced chart is quite a mashup of technologies. It stitches together a Flash movie (for the spinning sample block), the DirectShow API for playing the MP3, some other Windows APIs for extracting the embedded MP3 OLEObject Package from the workbook so it can be played, an advanced Javascript timer running in a web browser control to direct the timing of the slide show and to keep the music and the slides in sync after a pause/resume cycle, a fair portion of VBA to stitch it all together, and a demonstration of how to use the INDEX worksheet function to enable dynamic charting.
The chart itself is an XY (Scatter) type.
The presentation includes 78 images, which means 78,000 tiles had to be encoded to work with the chart. I'd like to thank my wife and my 10-year old daughter for helping with that task!
I'd also like to thank Robert Mundigl from Clearly and Simply. Robert was kind enough to review the celtic muse workbook prior to publishing and offered some excellent suggestions for improving it that I had not thought of. Thank you very much Robert.
Since this project makes heavy use of various Windows APIs I'm confident it will only work on Windows based computers. The workbook itself is 6.8 MB, almost all of which is the ebedded binary data for the beautiful MP3 soundtrack. I promise it's well worth the download.
Here is the file.
--- UPDATE ---
It seems that the Flash integration in this project may not work for some, especially if you have a 64 bit version of Windows. Adobe is working on a version of Flash for 64 bits, but it is not ready yet. So, here is a version of celtic muse with all traces of Flash removed. Just in case you were wondering, Flash was only used to display that spinning cube that you can also see on this blog post (if you are running a 32 bit browser!). And that only happened whey you clicked on the question mark button. Here's the Flashless version:
And here's a list of other animated charts on Excel Hero:
- 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) - Kaleidoscope Motion (Optical Illusion)
- Curry's Paradox (Optical Illusion)
- The Haze Illusion (Optical Illusion)
- The Koffka Ring (Optical Illusion)
If you liked this article, please share it!
Daniel
I'm getting a Member or Data Member not found error from the
Sheet1.ShockwaveFlash1.LoadMovie 0, SWF_URL
line in Private Sub Workbook_Open()
@Hui -
I sent you a PM. But I suspect you do not have Flash on that computer. I guess I should check for it in the VBA code. Can you confirm?
Regards,
Daniel Ferry
excelhero.com
Hi Daniel
My Excel keeps on bombing out the minute I enable content..any suggestions? (and I do have Flash et al. installed)
Adam
I am fairly convinced it is Flash not working properly on 64 Bit machines in my case.
I have tried 2 x Win 7 (64 bit) machines one with Office 2007 and 1 with 2010.
It isn't working on either, yet both machines pass the Adobe flash/shockwave browser tests.
I have confirmed that I have installed the full version not lite version of flash.
Wow. This is really impressive, Daniel. I can't begin to understand the full scope of the processes that made this thing work, but it certainly inspired me. Rest assured that I'll be digging deeper to see what knowledge I can harvest from the amazing project. Thank you so much for sharing, and for allowing us to look "under the hood." I'm constantly amazed by the versatility of Excel, and I'm convinced that the limitations are in our head, and creative folks like you are there to open the rest of our minds.
P.S. Given my statements in the previous post, I have to believe it's possible to smoothly animate the transition between "charts." Excel may not be the appropriate tool for this, but I'd be curious if it is capable. Could you set it to cycle through various values at small increments of time until it reaches the appropriate value? What sort of underlying data would be required? How complex would the code requirement be? I suspect that it would take a lot of computing power to make anything look smooth, and maybe out of the realm of possibility for todays technology. Anyways, I'm thinking more in terms of practical data analysis charts (bar charts, line charts, etc) rather than the art that you've just produced. Do you have any thoughts on this? Again - this is amazing.
@Adam -
Please see the --- UPDATE --- at the bottom of this post for a new version that may solve your problem. Please let me know how you make out.
Regards,
Daniel Ferry
excelhero.com
@Hui -
Please try the updated version that is completely Flashless and see if that works for you.
Regards,
Daniel Ferry
excelhero.com
@Tom Quist -
Animating the series of Excel charts is possible. But the quality of the animation (meaning smoothness) is entirely dependent on the number of points in every series in the chart. If you do not have a lot of points it will be quite good. As a rule of thumb a few dozen will work well, a few thousand - not so much. A few hundred - maybe.
If you have a sample chart you would like me to animate I'be be happy to show you how. Please PM it to me.
Regards,
Daniel Ferry
excelhero.com
Daniel,
Thanks for the invitation. I sent you a little something I put together this morning. I actually surprised myself with how it turned out. Certainly not perfect, but it did the trick.
Thanks again,
Tom
Daniel...how do you find the time to create this magic?
Unreal.
@Jeff -
Family involvement!
Regards,
Daniel Ferry
excelhero.com
Daniel
The Flashless version did the trick
Celtic Muse is Excellent
Hui...
@Hui -
Awesome. Glad you were finally able to watch it. Maybe one day soon Adobe will get the 64-bit version of Flash working properly!
Regards,
Daniel Ferry
excelhero.com
Missed out on the Google/Pac Man game also :-(
try http://google.com/pacman
Daniel,
I, too, am getting an error just after enabling the Macro Security alert.
It is:
"Run-time error '52':
Bad file name or number"
Yes - 64-bit Vista
Yes - Office 2007
Yes - these pass Adobe flash/shockwave
Yes - have tried 'NoFlash.xlsb'
Any thoughts?
Charly
Daniel...I'm finaly getting the time to poke around this a little bit more. Very cool.
Question for you, I see you've used a large array of formulas with IF statements. THis got me thinking whether this was a specific design choice, following on from one of your previous posts on the use of IF where you showed some alternatives. (I learnt a lot from that post).
Specifically, I see that cells in the array that starts in D21 on the Meta sheet has =IF(INDEX($B$3:$AY$22,$A31,$B31)=D$30,$A31,NA())
I was wondering if you used IF statements here because of the fact that you couldn't use say =(INDEX($B$3:$AY$22,$A31,$B31)=D$30)@$A31+INDEX($B$3:$AY$22,$A31,$B31)>D$30)*NA()
...because even if the 2nd part (i.e.>D$30) was false, 0 times NA returns NA, meaning the formula would return NA no matter what the value of D$30. Or put another way, if you 'touch' an NA you 'become' an NA
If so, then assuming a choose function would be quicker to evaluate than an IF statement (and I have no idea here) then you could use this:
=CHOOSE((INDEX($B$3:$AY$22,$A31,$B31)=D$30)*1+(INDEX($B$3:$AY$22,$A31,$B31)>D$30)*2,(INDEX($B$3:$AY$22,$A31,$B31)=D$30)*$A31,NA())
Then again, maybe you used IF because it was more convenient and easier to follow?
Note that cell V31 could also be changed from... =IF(ISNA($C31),NA(),IF(RAND()>0.5,$C31,NA()))
to ...=$C31*0+CHOOSE((RAND()>0.5)*1+1,NA(),$C31)
...in the case that CHOOSE was more desirable than IF
Love to know your thoughts.
Regards
Jeff
@Jeff Weir -
I'm encouraged that you are digging into the workings.
I am not philosophically opposed to the IF() function. It's just that I feel it is used too much and often when there are better choices. However, when it comes to throwing an #N/A to force data points to hide themselves on a chart, I think that IF() is an excellent choice. For some reason, Excel is able to conditionally (with the IF() function) output an #N/A at lightning speed.
Over the years, I've tried CHOOSE() as an alternative, and unfortunately it cannot compete in terms of speed, in my experience.
Another technique that I sometimes use for charting conditional points is to specify the value and then divide by the condition. This results in the value you want charted if the condition is true and #DIV/0! error if the condition is FALSE. This works because the Excel charting engine ignores any points valued at an error, not just #N/A values. But once again, this technique is not as quick as the IF() and #N/A pairing.
All of the info that I've shared on IF() is valid. But like everything, there are exceptions. Dynamic charting with #N/A is an exception in my opinion.
Regards,
Daniel Ferry
excelhero.com
Cool...I didn't know that.
I guess another way you could do this would be to have the expressions you want excel to ignore evauluate to a number at the extremes of your results (for instance, negative one in this particular example), then use format axis/axis options/minimum/fixed = zero so that these are excluded from your chart. Wouldn't work for chart types that connect points with a line though.
I noticed a bug in Excel 2007 recently whereby
1) if your graph points to a range stored on the spreadsheet, then errors they don’t show up in a graph (which is what I expected) BUT
2) if your graph points to a range stored in a named range (i.e. the array is constructed by a formula or data entered into Name Manager) then errors are plotted as zeros.That is, if you go to name manager, and input say ={100,#N/A,#N/A} and point your graph series to this named range, then excel actually plots this as {100,0,0}. But if you either input 100, =na(), =na() into three cells on the spreadsheet, excel only plots the first point.
Very annoying! This means that if your axis includes zero then you’re going to get a point plotted at zero, even though the formula evaluates to #N/A. This can really slow the performance of a dynamic chart down if suddenly you've got an extra couple of hundred points plotting.
@Jeff Weir -
Yes, some of my Optical Illusions set unwanted numbers to negative values instead of #N/A. But honestly, I think the error is faster, and as you noted it does not work for discontinuities.
And yes, the Excel 2007 bug you describe is really a pain. Often what I do to get around it is name an unused cell, "null". It's important that this cell will never have a value. Then in my formulas that construct the Named Formula that will be charted, I reference "null" instead of na(). This works.
As a real-world example, examine the Excel as a Presentation Platform (Number Spiral) chart.
Regards,
Daniel Ferry
excelhero.com
Hi Daniel. THanks for the tip re using a named range called null. Very good idea. I love this free learning! No doubt I'll learn a lot more as I get more time to take these charts apart.
On another note, any chance you can implement comment notification on this forum? At the momemnt the only way I can see if there's any action in the comments of a post that interests me is if I click through occasionally to check for updates. I like the comment subscription option that Chandoo and Jon Peltier use...I learn the most from comments that other people are asking.
Cheers
Jeff
That's modern art! really beautiful!
Thanks Quran, but I was really after Viagra. Do you happen to sell this also?
Hi Daniel. Me again. I've been trying your NULL method (i.e. cell without a value named null referenced in a formula) but i'm finding Excel 2007 is still plotting the formula. Is it possible that your NULL method only works where just one data point is being plotted, and not when that null point would be included in a longer range?