May 2010 Archives

There are no lines connecting the smaller dots, but there sure seems to be, like a wave, or a mirage. I love it.

The animation is straight forward and the chart is just an XY (Scatter) type.

Great job again, Hui, and thanks for contributing to the community.

Here's the chart.

 - Breathing Square (Optical Illusion)

 - Enigma (Optical Illusion)

This animated GIF shows the business chart in question which is available for download at the end of this post. Last week one of Excel Hero's readers, Tom Quist, sent me a spreadsheet where he had made an outstanding first effort at the animation of a typical column chart. He wanted to animate between several records - let's call them divisions.

 

His technique worked, but was a little slow. So I showed him how I would do it and I believe he was enthusiastic with the results.

There are some points to note. If you add a shadow to the chart or (God forbid) make the series 3D, the animation will slow down significantly. Ditto for a background image to either the Plot Area or the Chart Area. The animation speed is heavily dependent on the number of data points in the chart. After a few dozen points, animation becomes dicey in Excel 2007. Making the series partially transparent will also slow it down, but only slightly (maybe not even perceptibly given the number of points in the chart). In some circumstances the transparency effect may be worth the tiny penalty.

The technique that I shared with Tom that day was to do a straight linear interpolation between the point values of one division to the next. This works well. I shared with him that it was also possible to implement EASING so the animation has a soft start and end. I did not have time to give him an example, but I hinted at how to do it. The animated business chart that I'm sharing with you today includes the option for easing, and you can see it demonstrated in the GIF loop above.

The example also moves the calculations for the animation off of the sheet and into named formulas. To me this makes sense as there is no reason to clutter the sheet with the calculations required to morph one division into another.

The chart allows you to switch between four divisions, change chart type, enable animation, and enable easing. You can also set the number of discreet steps to include in the morphing transition between divisions, which will be useful if you run the workbook on older versions of Excel. The older charting engine is so much quicker than Excel 2007's that you will want to increase the number of animation steps just to see it!

I've heard that Excel 2010 is supposed to improve charting speed. I'd love to hear how this performs on that platform.

This workbook demonstrates interactive dynamic charting, animated transitions including easing, and charting from named formulas. VBA is used only to loop through the number of animation steps that you specify, and to change the chart type.

An animated transition can add a certain amount of flare to your dashboard, but be careful not to overdo it. If used sparingly it can add a Flash-like level of interest, but remember that in and of itself, it does not add to the information the dashboard conveys.

I recommend studying the named formulas (CTRL-F3) and the VBA (ALT-F11) in the workbook. Nothing is protected and there are no hidden sheets. The knowledge gained can be used for much more than animation.

Here's the animated business chart.

.xls

And here's a list of other animated charts on Excel Hero:

 - Animated Business Chart #2

 - celtic muse

 - solarsong

 - HTML 5 Readiness

 - Real-time Advanced Excel Chart

 - Excel Location Mapping

 - Animated Stacked Chart

 - Excel, A Presentation Platform (Number Spiral)

 - Eurovision 2009

 - Monte Carlo PI

 - Excel Chess Game Viewer

 - Excel NFL Drive Chart

 - LED RSS News Ticker

 - Lilac Chaser (Optical Illusion)

 - Stereokinetic (Optical Illusion)

 - Illusory Contours (Optical Illusion)

 - Breathing Square (Optical Illusion)

 - Enigma (Optical Illusion)

 - Two Sinusoids (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) (Bottom of post)

I was inspired by this useful HTML5 readiness tool. As you move the mouse over the rays the index ray follows the mouse pointer so you know which category is which. It's pretty cool.

 

Crafting this project I learned that there are some chart properties that you can access from the Excel 2007 Format Data Series dialog box that cannot be addressed from VBA; and vice versa.

For lines, you can set the Cap Type under Line Style in the regular interface. You have three choices: Square, Round, and Flat. Under Joint Type, you also have three choices: Round, Bevel, and Miter. Nowhere in the VBA object model can these properties be set. I needed to set the index ray to a Cap Type of Flat in VBA and it cannot be done - so it's round. You would think that you could set it beforehand from the interface - and of course you can - but changing the line color from VBA also changes the Cap Type!

While hunting through the expansive forest of the VBA object model for 2007 charts, I found a couple of interesting objects that are not addressable from dialog boxes - and so 99.99% of Excel 2007 users are likely unaware of them. For chart lines (and some other items) you can set them to glow. You can set the color and the radius of the glow. You can also specify SoftEdges, and I used this on the background darkish ring in this chart. Again, these two interesting settings can only be addressed from VBA.

I don't have Excel 2010 yet. I hope this kind of strange oversight is rectified in the new version.

This chart uses a modified version of Jon Peltier's chart events class, and just like my Eurovision 2009 chart, the interactivity and animation only works when the chart is selected. When you first open the workbook the chart will be selected. But if you click on the worksheet instead of the chart, you will need to click the chart again to reengage the interactivity. Be sure to change the years by clicking on them at the top.

Here's the chart.

This week's Excel optical illusion is called, Lilac Chaser. It is generously brought to us by one of Excel Hero's Australian readers, Hui.

If you stare at the black cross in the center and keep your focus there a very curious illusion transpires. At first you'll notice that where a pink dot disappears a green dot appears. If you keep staring at the black cross, all of the pink dots disappear!

I think Hui did an outstanding job. This is a Radar chart with a little VBA thrown in to animate the dots, and it is well worth looking under the hood to see how it's done.

Michael Bach has a similar illusion in Flash and it is of course outstanding, but ours is in Excel, dammit.

Great job, Hui and thank you for contributing to Excel Hero's growing community!

The workbook is an .xls so it should work in all versions of Excel, but the newer versions render the gradients better and the illusion is stronger as a result.

Here's the chart.

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:

NoFlash.xlsb

And here's a list of other animated charts on Excel Hero:

 - Animated Business Chart

 - Animated Business Chart #2

 - Animated Business Chart #3

 - Smith Chart

 - celtic muse

 - solarsong

 - HTML 5 Readiness

 - Real-time Advanced Excel Chart

 - Excel Location Mapping

 - Animated Stacked Chart

 - Excel, A Presentation Platform (Number Spiral)

 - Eurovision 2009

 - Monte Carlo PI

 - Excel Chess Game Viewer

 - Excel NFL Drive Chart

 - LED RSS News Ticker

 - Lilac Chaser (Optical Illusion)

 - Stereokinetic (Optical Illusion)

 - Illusory Contours (Optical Illusion)

 - Breathing Square (Optical Illusion)

 - Enigma (Optical Illusion)

 - Two Sinusoids (Optical Illusion)

 - Mutually Interfering Shapes (Optical Illusion)

 - Kaleidoscope Motion (Optical Illusion)

 - Curry's Paradox (Optical Illusion)

 - The Haze Illusion (Optical Illusion)

This advanced Excel chart is highly interactive. In addition to all the illusory movement, there's a button that animates the nuts in actuality. It's quite a show. You can also remove or include different groups of nuts to investigate what happens to the illusion. Surprisingly, the effect is even stronger for me with less nuts.

The workbook should behave in all versions of Excel. 

Here it is:

The illusion of expanding movement works best for me when I'm not looking at it directly, and when my eyes are moving, for example when I read the menus at the top of this window.

As in the Hering Illusion a few weeks ago, this is not a chart. The illusion is constructed from inserted star shapes on a workbook. But when it's at its full size in the workbook, this illusion is particularly strong, at least for me.

Here's the workbook:

Here's my attempt at emulating his fine work in Excel:

In case you are having difficulty I made these animated GIFs from screen recordings of the program running each of the three methods on my computer. All three produce the same data scraping results. The swarm is just way faster. And remember these results are from scraping a terribly slow website. Faster websites produce much more stunning results.

In case you haven't downloaded the Multi-threaded VBA simulation workbook yet, an explanation is in order. The gray bar represents the number of records needed to be scraped. The blue bar represents the records that have been requested. The green area represents records returned. The red line represents the speed or rate in terms of records per second returned, which is what the vertical axis scale shows.

I'd like to know if anyone gets over 2.00 records per second from the incredibly slow subject website.

You can read the full article here.

And download the latest workbook here.

So I made a new version that keeps the swarm gratuitously swarming for no other reason than to make it look like they are busy doing something. I've never liked any of the timer options for VBA so I often use Javascript for this, like I have in this updated example. I've tested the workbook in Excel 2002 and 2007 and it seems to work ok. Let me know if you have any problems.

Here's the update with the fully active swarm!

P.S. A minute ago I just about fell out of my chair. I ran the program and the swarm looked like they were on steroids, or maybe they were killer bees! It took me a second to realize that I had the Stereokinetic illusion running at the same time in the same instance of Excel but not in the foreground. Both programs make extensive use of calculation events and the two running at the same time was almost scary. I thought the swarm was about to take their revenge on me...

A few months ago I penned a guest article for Ajay over at Databison, demonstrating a technique I've used many times to simulate multi-threaded VBA. Today I thought I'd share the technique with my readers and include an enhanced example workbook.

 

I should make it clear that this technique is not multi-threaded VBA, as such does not exist. Instead it is a method of orchestrating multiple processes from Excel, leveraging the fact that Windows is a multitasking operating system.

The scenario is scraping a website, an ideal candidate for this demonstration because it can be an asynchronous task. If you follow a traditional approach you might automate Internet Explorer from VBA. This is probably the worst choice because Internet Explorer is big, fat, and slow, but even worse, Excel's VBA processor will sit waiting synchronously for the information from Internet Explorer, virtually killing the Excel user interface while the program is running.

A better technique is to forgo automating Internet Explorer and to use the MSXML2.XMLHTTP OLE object from Microsoft. While not strictly correct, you can think of it as a web browser with no interface. In other words, it's invisible. It's stripped of everything and just quickly retrieves web page text to memory. It can also send data to a server and is optimized for XML. This technique is much faster than the first. But it still ties up Excel synchronously as it scrapes one record at a time.

My preferred approach is to create and launch a swarm of independent agents from VBA. These agents do not run in Excel. They are not VBA. They are in fact VBScript programs that run in the Windows Scripting Host. I think of them as worker bees, because they are expendable. Each has one mission: go to the website of interest; get one record; write it back to the master Excel file that created it; die. A shotgun blast would be another analogy. After the payload has been received by Excel, a new agent is created and dispatched to replace the one that just terminated. This is simpler to manage than trying to reuse an existing agent.

The swarm method works very well and the speed increase is phenomenal, but dependent on a number of factors. The website being scraped is a big factor. In this demo, we'll be scraping www.realestateabc.com, which is really slow. Several years ago before Zillow.com went to a Flash interface, this technique was unbelievably fast there. The operating system is another factor. I can run several dozen agents in a swarm on Windows XP. The same computer can run hundreds of agents in a swarm under Vista. Windows 7 is likely better than Vista, but I don't have it and so I don't know. But the more agents you can run simultaneously, the better the speed increase. I can only assume that Vista has a better OLE subsystem than XP. And of course the speed of your machine and the amount of memory it has will also affect the size of the swarm and its performance.

The example workbook showcases all three of these techniques allowing you to test the speed of each. I've also included an interesting chart to monitor progress and speed. It's best to only run one instance of Excel during the testing. The first time you set the swarm loose, you may need to authorize each VBScript file, depending on your security settings.

Many of my examples on this site do not use VBA, or they use minimal VBA. This example is heavy on VBA, but interestingly most of the work is done by the VBScript agents when using the swarm. Of course when using the other two techniques, VBA is the workhorse.

For an added bit of enlightenment, run the swarm with the Windows Task Manager open to Processes, and the Image Name column sorted Z-A. You'll see all the agent programs as they are spawned, and then near the end of the run, you'll see them disappear.

This workbook is an example of fairly advanced Excel. The idea of Excel launching a swarm of programs that run in a different language and ultimately each write back to the Excel workbook that created them is... out there! Even the chart inclusion that monitors the progress and speed is a sophisticated specimen. I think that most users could learn a tremendous amount by fully studying this example workbook.

Please let me know what you think. If you have any questions on the methodology or the VBA or VBScript coding, please fire away.

Now imagine you had to scrape a hundred thousand records...

Here's the file:

--- UPDATE ---

There's an interesting enhancement to this sample workbook here.