Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 7. June 2010 10:23

Some time ago, I came across ExcelDNA, an open-source library designed to integrate .Net into Excel, via a  post by the Grumpy One, who described it as an interesting way to get Excel to talk to a compiled library. Sounds right down my alley, but I still managed to let 6 months pass until I finally tried it.

This reminded me of another post, by J-Walk this time, where he uses a random walk simulation in VBA to benchmark system performance. Back then, I ran the VBA code, and also the equivalent C# in a console app, out of curiosity: 11.38 seconds, vs. 2.73 seconds. Why not try the same experiment, and see if we can get the best of both worlds and bring some of the C# power into Excel via ExcelDNA?

So I created a Class Library, with the following method, a close equivalent to the VBA benchmark code:

public class Experiment
{
  public static string RandomWalk()
  {
     var stopwatch = new Stopwatch();
     stopwatch.Start();
     var position = 0;
     var random = new Random();
     for (var run = 0; run < 100000000; run++)
     {
        if (random.Next(0, 2) == 0)
        {
           position++;
        }
        else
        {
           position--;
        }
     }
     stopwatch.Stop();
     var elapsed = (double)stopwatch.ElapsedMilliseconds / 1000d;
     return "Position: " + position.ToString() + ", Time: " + elapsed.ToString();
  }
}

The ExcelDNA tutorial is completely straightforward: compile the dll, drop it to a folder with a copy of the xll stub and the dna text file, add a reference to the add-in in Excel, and that’s it – your .Net function is now available from Excel. If what you are used to is VSTO, at that point your jaw is dropping, and you are staring in disbelief.

I then added the following macro, timing the entire call to make sure the overhead was taken into account in comparing the VBA and DNA code:

Sub DnaTimer()
  
  Dim StartTime As Single
  StartTime = Timer
  dnaResult = Application.Run("RandomWalk")
  MsgBox dnaResult & " - " & Timer - StartTime & " seconds"
  
End Sub

Here are the results on my Intel Core 2 Duo 1.80 GHz laptop, with 4 GB Ram.

VBA: 11.96 seconds.

VbaPerformance

DNA: 5.95 seconds, with negligible overhead.

DnaPerformance 

John Walkenbach published the performance distribution of the results reported by his readers; my laptop ranks slightly below the median. In under half an hour, including reading the tutorial, I wrote a user-defined function that was the second fastest of the pack, using a fairly average laptop.

Would I expect this type of result in every situation? No. This example is probably as good as it can get for ExcelDNA, with no interaction with the Excel object model, and mostly computation. I am definitely going to keep playing with ExcelDNA, to see how it fares in other less obviously advantageous situations; but it certainly seems like a great option when you need a few computation-heavy functions, without the overhead of VSTO.

Now for some nitpicking: one thing I’d like to see is support for .Net 4.0. I am probably biased by my recent experiments with the Task Parallel Library, but it seems to me that ExcelDNA is a great fit when the goal is to extend Excel with heavy algorithms in .Net – and the TPL is just great for writing parallel code that leverages multi cores, without having to worry much about managing threads. It could be done without it, but it certainly is way easier using it (I am hearing that there would be some unsupported way to use some of the Parallel Extensions via Reactive Extensions in .Net 3.5; if I get this to work, I’ll post about it later).

I specifically wanted to use parallelism here because of a comment by John Walkenbach, asking whether it is possible to run this under 1 second. Given the results he posted, I doubt it’s feasible in VBA; however, this may be possible using parallelism in .Net – I think it’s a fun challenge, but I am too lazy to try that out without the TPL! Maybe some other person will step up to the plate?

Comments

6/7/2010 7:27:45 PM #

ross

Hi Mathias,

I recently interviwed Govert for the podcasts, I'll post it soon. It was an intresting chat. Dont forget that UDF and VSTO are a little tricky, so xl DNA wins there too (no need to use VBA to get the function into excel).
DNA sure is fast and easy, I'm giving an intrduction talk about it at the UK dev con next month, so if you do get round do the e Parallel Extensions bit let me know I'd love to show it as an example!!!

Thanks
Ross

ross United Kingdom | Reply

6/8/2010 5:14:43 AM #

Mathias

Hi Ross,
I will let you know what I find regarding the parallel extensions. In any case, Govert is apparently planning to add .Net 4.0 support by August or September, so parallelism is around the corner (so to speak).
Out of curiosity, do you have a sense for situations where ExcelDNA is obviously the right choice, and cases where other approaches (VSTO, VBA) are more appropriate?
Looking forward to the podcast!
Mathias

Mathias United States | Reply

6/13/2010 12:30:23 PM #

trackback

Supercharge Excel functions with ExcelDNA and .Net parallelism

Supercharge Excel functions with ExcelDNA and .Net parallelism

Clear Lines Blog | Reply

7/5/2010 4:05:08 AM #

pingback

Pingback from blog.methodsinexcel.co.uk

The MIE Podcasts – Govert van Drimmelen «  Methods In Excel

blog.methodsinexcel.co.uk | Reply

3/18/2011 12:41:01 AM #

Magento

Hai Mathias,

Thanks for this nice info...still no plans of trying this out with TPL?? Would be great to trie....also a lot of work.Smile

Magento Netherlands | Reply

9/5/2011 7:55:02 AM #

trackback

Supercharge Excel functions with ExcelDNA and .Net parallelism

Supercharge Excel functions with ExcelDNA and .Net parallelism

Clear Lines Blog | Reply

12/17/2016 9:00:04 PM #

pingback

Pingback from steroidsforsale.biz

dianabol legal

steroidsforsale.biz | Reply

9/24/2017 1:35:29 PM #

pingback

Pingback from pink4.co

The Excel-DNA | kumbukumbu

pink4.co | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS