Plotting Ferns

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  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

This entry was posted in Arrays, Charts, Drawing, Excel, Maths, Newton, VBA and tagged , , , , . Bookmark the permalink.

5 Responses to Plotting Ferns

  1. Michael says:

    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.



  2. Michael says:

    Doug –

    I’ve heard from Dr Wood. He mentioned that this is a Barnsley 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. 😉



  3. dougaj4 says:

    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.


  4. Jeff Weir says:

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


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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s