Tech choice

MM asked why would you use xlls?

2 big reasons

  • if you have significant C/C++ resources you want to expose to Excel
  • if performance is important

Here is my tech choice thinking (from codematic) (very roughly):

[c= interactive, command style stuff, f = worksheet functions]

  • For quick and dirty: VBA (c and f)
  • for workbook specific: VBA (c and f) or XLM (way faster than VBA in many cases) (f mainly)
  • for application level: VBA (c) or xll (f)
  • For best performance: xll in C (f mainly)
  • for protecting intellectual property: VB6 or xll (c and f)
  • For future proofing (??*): .net (c and f if performance is not important)

The thing with custom worksheet functions (I struggle to call them UDFs (Used Defined Functions) when considering xlls as that is so clearly *NOT* a ‘user’ technology) is that you often end up with 100’s of thousands of them. So a tech that can save a thousandth of a second per function could save many minutes of calculation time.

It seems to me almost counter intuitive, people think of C as executing fast, which it does, but VBA is pretty fast too. So for a long complex function that only gets called a handful of times VBA may be a better bet than C considering dev time and test time etc. But where you have a simple 5 or 10 line function that is used thousands of times then the xll route may offer compelling advantages.

Another point worth mentioning is that most serious BI tools use xlls as their Excel interface. Essbase, Oracle, Cartesis, Analysis Services are the main ones I’ve worked with. Some use xll UDFs directly to the DB, some use xll commands, but they all benefit from the performance of the C API over the COM interface. One BI vendor MD even told me they originally wrote their Excel client in VB6 (COM add-in), but had to recruit a bunch of C/C++ devs to convert the whole thing to use the xll interface because their performance was so far behind the competition.

*question marks because surely at some point the Excel/.net story will evolve to consign this C stuff to the dustbin of time (but I have been saying that since 2002, or so, and its in no nearer in 2007 than it was in 2002.). VSTO is improving all the time, but thats more command stuff than UDFs. How far it evolves will impact how ‘future-proof’ anything written with current tech might prove to be.

For me, in 2008 its VBA if possible as thats quick easy and robust (comparatively!), or XLM or xll if performance demands it.

Thats how I see it anyway – what do you think?

cheers

Simon

Advertisements

8 Responses to “Tech choice”

  1. Rob Bruce Says:

    I’m interested in the use of the C api as a backdoor route into Excel for .NET stuff (a la ExcelDNA – http://exceldna.typepad.com/blog/2006/01/introducing_exc.html). As far as I can see, this is the only way we are going sensibly to get .NET working with Excel without paying the interop penalty.

    Anyone done any work on this?

  2. Marcus Says:

    “save a thousandth of a second per function could save many minutes of calculation time”

    Absolutely. I’ve worked on a project migrating an Operational Risk Monte Carlo model from Excel/Access. Given the usual budget and time constraints (and a regulator breathing down our neck) the target platform was VB6, SQL Server and Informatica (based on the premise that a quick migration is a good migration).

    While VB6 made migrating the code from VBA a simple task there was zero (okay, negligible) performance gain. Compiled VB6 codes appears to offer no real performance gains over interpreted VBA –can anyone else verify/debunk this? With 30,000 X 100,000 simulations, the model took 23 hours to run. Note that it was the repetition which was the main overhead, not the complexity of the calculations.

    The plan is now to migrate the calculation engine from VB6 to C++ (which still surprised me as much of the functionality could be purchased off the shelf).

    Cheers – Marcus

  3. Simon Says:

    Rob, Mike S was speaking very highly of ExcelDNA. I keep meaning to try it out, I think I’ve even downloaded it a few times.

    I’ve given up a bit on .net as there was no real step forward in 2007.

  4. Simon Says:

    Marcus
    Why not just use Crystal Ball? thats fully updated for 2007 too, (owned by Oracle now).

    VB6 v VBA I’m with you, no testing I have ever done has put VB6 ahead. VBA is not interpreted (not after the first run anyway), its compiled – seemingly into something very similar to VB6 bytecode.

    Some people reckon VB6 is faster, it might be if the code is ultra complex, it would seem all the stuff I have done is too simple to see the difference.

    I too have a VB6 to C/C++ migration project on, I bet not many people foresaw things would move that way!

  5. Charles Says:

    I agree with Simon.

    I did finally manage to find some UDFs where VB6 ran 15-20% faster than VBA, but it was hard work.

    VBA UDF performance can suffer from the VBE header update bug which can only be fully bypassed in Manual calculation mode.

    Both VBA and VB6 calculation times are very sensitive to the way data is transferred to and from Excel. When this is done in an optimum way the performance difference between VBA and C can be reduced, but still substantial, factor of 2, or more for very heavy calculations or string handling.

    Of course often the most important thing is developing a good algorithm, rather than the implementation technology.

    .Net performance is currently so bad that its a non-starter (interop marshalling layer), unless you use Excel DNA or Managed XLL which are reported to be much faster because they are using the C api under the covers.

    @Marcus,
    Both @Risk and Crystal Ball are updated for Excel 2007.
    a frequent cause of slowness in VB montecarlo stuff (and some finance functions) is the use of Excel functions like Normsinv which are extremely slow (particularly in XL 2003 and XL2007). Ian Smith has written some VBA functions that use a superior algorithm that runs much faster and is more accurate.
    http://members.aol.com/ianandjmsmith/examples.xls

  6. Marcus Says:

    “Why not just use Crystal Ball?”
    We were limited to the existing technology base.

    “Excel functions like Normsinv”
    Ironically enough, the Excel/Access functions were never a drag on performance as they were used. The business had contracted a third party organisation to write a function library in – wait for it – Fortran.

  7. gobansaor Says:

    I’ve used ExcelDNA as a quick and easy way to exploit .NET functionality within Excel. It’s simple to use and seems to be fast but I haven’t bench-marked it as such. It even enabled me to use JavaScript (well JScript actually) as an Excel “scripting” language! ( http://blog.gobansaor.com/2007/10/04/javascript-as-an-excel-scripting-language-via-exceldna/ ).

    Tom

  8. MacroMan Says:

    Will ExcelDNA in C# replace xlls in C/C++? C/C++ much faster? Wondering if I should invest time in C++.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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


%d bloggers like this: