Over at Daily-Dose-of-Excel mrt has posted an interesting VBA version of a Matlab program that used an Iterated Transfer Function to plot a graph resembling a fern. The original Matlab code came from Dr Tom Wood, and Michael’s VBA version works just fine, but I took the liberty of making a few changes to suit my own tastes:
- Michael’s code was pre-set to generate 32,000 points, which is the chart limit in pre-2007 versions of Excel. I have changed this to an input from the spreadsheet, and in Excel 2010 it will happily plot over 1 million points.
- The fern shape is governed by a four 2×2 matrices which were hard coded. I also changed these to be read from the spreadsheet, so that it was easy to experiment with different parameters.
- I added a check to see if the chart sheet existed, and if so to re-use it.
- The algorithm involves a large number of matrix multiplications. These are handled in the original code by using the Application.Worksheetfunction method. This tends to be slower than keeping the code in VBA (especially in Excel 2007), so I added a short sub to handle the matrix multiplication.
- Finally I wanted to check if there was a significant speed difference between operating entirely with Variant arrays or by copying the variant data input from the spreadsheet into double arrays, so I set up two different versions of the code and added to buttons to the spreadsheet, with calculation time reported underneath (actual time is considerably longer, because generating the chart takes over half the time).
The resulting spreadsheet, including full open source code, can be downloaded from Fern Chart.zip. Note that I have changed the number of points to 1000 to keep the file size down. Around 30,000 points will work with pre 2007 Excel, and also gives the most pleasing results.
Here’s what the input screen looks like:
And four different ferns generated with the FernM4(2,2) parameter varied between 0.8 and 1.05