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:

Input and top of output values for 30,000 points

And four different ferns generated with the FernM4(2,2) parameter varied between 0.8 and 1.05

FernM4(2,2) = 0.8

FernM4(2,2) = 0.85

FernM4(2,2) = 0.9

FernM4(2,2) = 1.05

### Like this:

Like Loading...

*Related*

Hi Doug –

That around 30,000 points gives the most pleasing results really means that those old Excel designers were really good😉

Thanks for noticing, and thinking that it was a topic to expand upon.

…mrt

LikeLike

Doug –

I’ve heard from Dr Wood. He mentioned that this is a Barnsley Fern:

http://en.wikipedia.org/wiki/Barnsley%27s_fern

There are other examples of “mutant” ferns at the link, with a good explanation of the math. Michael Barnes lives in Australia. Maybe you’re neighbors. 😉

…mrt

LikeLike

Thanks Michael – nice easy to follow explanation at the Wikipedia link (unlike the link I posted!). I think I’ll rearrange the input to match the format used by Barnsley. The links down the bottom of the article are good as well.

LikeLike

Any chance you can change this to a silver fern in time for the rugby world cup next year, Doug?

LikeLike

Pingback: Daily Download 8: Drawing in Excel | Newton Excel Bach, not (just) an Excel Blog