Add-in Express Review

In amongst all my other busy-ness I have been reviewing a few Excel developer tools, or in some cases Office developer tools.

Addin Express is definitely more of a general Office development tool than Excel specific, but it does create xlls which was my main motivation for the tool reviews.

Addin Express (which I will call ADX from now on) has been recommended to me by several of my Office developer buddies, so I was keen to try it out. For anyone who was at the UK Excel developer conf in London in July you might remember that I demoed creating an RTD function using ADX and it is very simple.

As I said my main initial interest was xlls, and so thats where I’ll start.

ADX has a novel drag and drop approach to xlls, but before we get to that, when you create an xll project you get to choose which language from VB, C#, C++, J# (remember J++??), or Delphi. And on the first dialog is where you get the first hint that an ADX xll is not like an XLL+ xll for example, it will need a proper install (setup project).

Next you get a further hint that this xll isn’t just a native dll with some Excel specific exported functions:

There is some serious COM/.net plumbing magic going on in the background. The big point to note here and one of the biggest appeals of ADX (IMO) to an add-in developer is the ‘version-neutral interop assemblies’ choice. That gets you the ADX IA’s which allow your .net code to work with any sensible version of Office. I’ll talk more about this later because, we would expect an xll to work with every version of Excel since 97 anyway.

Click finish and you drop into code view,

double click on the XLLModule.cs in the solution explorer, and drop pane appears where you can drag UDFs

For a hand coder like me its all a bit novel, but for a more normal, visual developer I think it would be far more second nature.

When you open the toolbox (there’s a clue – I never use it, as I never do UI development).

Here you will see a region titled ‘define your UDFs in this section’, which to be fair sounds like a pretty good place to define a function. Inside is a sample function you can uncomment or just copy the principles from. Its pretty easy, none of the C++ data type pain, use standard .net data types and ADX will do all the marshalling for you.

Its a static class so you can either define all your functions as static now or wait for the compiler to tell you to later .

public static double addSome(double d1, double d2)

{

return d1 + d2;

}

That’s C#, without wanting to start a fight (much ;-)), I think VB.net is a lost cause. Nothing wrong with the technology, I just think its future is somewhat less certain than C# or C++.

Anyway from an ADX pov we now need to get this function visible in Excel correctly, and for that they use some drag and drop GUI loveliness. Double click or right click the XLLModule.cs to view its designer surface, then right click that and ‘add an Excel function category’.

You now have a button like thing whose properties you can set as if it were a button, mine is called clever functions. This is the function category you will see if you click the Fx in Excel, so its worth doing something notable.

To this category you then assign your functions, this is a good hierarchical approach to building out the functionality of a non trivial add-in as you may want to categorise your functions into usage groups to make it easier for user to find and use your functionality.

When you right click and select Edit functions you get to fill in this dialog:


You add you description then assign the functionName (from those functions you already coded in the XLLModule class) that you are talking about. If you have a help file then you can connect the function to it here. Hidden and Volatile are best left as false.

You can repeat this process until you have all the functions you want to expose to Excel in the left hand pane of the dialog above. The next step is to explain the detail of the parameter of all these functions. The order you do it is not important, but you right click on a function in the left hand pane and choose Add Function parameter.

You have now defined a function that takes 2 doubles and returns a double. The current implementation in the old C# just adds them but we can change that later without going through all this interface stuff.

When you are happy with your functions and want to try them in Excel you need to build and register the add-in. Build, Fair enough create some binary thingymabob, register? Well, these ADX xlls, as I mentioned before are not ‘pure’ xlls, they need some registry magic to work correctly.

Click Build>>Register ADX Project to set up the registry side of things, from now on you just need to rebuild as normal.

To debug you will need to set Excel as the start up program

When Excel starts your xll is loaded automatically, no security warnings, no searching in \debug to load it manually, and all the functions are ready and available.

(Is your add-in list that clear??)

Actually when you look in the add-ins list in 2007 (assuming you are (un?) lucky enough to working in ‘Excel Vista’, you will see the add-in is actually a dll, although ADX exposes it as if it were an xll/xla(m) one. IE its in Tools>Add-ins rather than COM add-ins.

So the key question for xll johnnies

Performance?

In my very limited testing I reckon that an ADX xll is about 3 times faster than VBA, which is pretty reasonable. It was close to 5 times slower than my test raw C xll which isn’t so brilliant.

I will add reviews of the other feature as I get chance to investigate. I can’t leave though without a little moan about the ‘getting a copy of Addin express’ process. I downloaded the trial, with the plan of installing it and reviewing it at the airport. I was somewhat fed up to find that you can’t install it without a key that you need to download. Without a wifi connection that meant I had to buy a mountain bike magazine to fill the hour wait instead. I’m not sure what chance I’ll get to look at other features as I wrote version one of this review several months ago, and I think my trial will have expired by now.

Summary/conclusions

An ADX xll is a bit more effort to deploy than a native code one, and the performance is not as impressive. But it does offer easy access to the full COM OM, unlike native code xlls targeting the C API only.

The choice is yours though really, ADX is easy to work with, version independent, targets all add-in technologies and all Office products, shows a significant performance improvement over VBA, and is reasonably priced. And offers improved security of VBA, which seems to be a fairly common question I get asked at the moment.

I think its a very valuable tool that every serious Office developer should have in their toolbox. You can download the docs here, they don’t really do a trial version, but you could try asking if you are serious.

I know lots of you are already using Add-in express, what are your views/experiences?

cheers

Simon

Advertisements

15 Responses to “Add-in Express Review”

  1. Charles Williams Says:

    My experience was that the “Trial” consisted of buying the ADX product and then asking for my money back with reasons.
    In my tests performance via the XLL interface was ok but not as good as XLDNA or XLL+ (my test was biased towards data transfer of of a largeish range of variant data), and the COM/Automation interface was noticeably slower.
    But I agree that its all nicely packaged and version-neutral interop is a major +, although COM Interop performance is not good.
    For those of you who listened to my undecided technology pitch at the London XL Developers Conference I am now proceeding down the XLL+ C_++ path (ADX too slow, XLDNA concern about interop etc, VB6 dead for 64-bit)

  2. Eric Legault (VP Developer Evangelism, Add-in Express) Says:

    Excellent write up Simon! Regarding the trial (this is also addressed to Charles Williams), we don’t have trial versions available for download. However, we do NOT require reasons for cancelling the order via our 30-day unconditional money back guarantee. Although sometimes we do ask, in the interests of providing better custom service and product experience, of course.

    You can always request a refund directly without our intevention:
    http://www.add-in-express.com/purchase/refund.php

    I will follow up shortly regarding the performance issues you both discussed.

  3. dougaj4 Says:

    Simon – thanks for the reveiw. I’m also planning to do a reveiw, when I have time to do a proper job of it.

    So far (with very limited testing) I have found the package works well, but I found the documantation poor. TMUA (too many unexplained abbreviations), hard to follow tutorials, and what I saw on the screen was often very different to the screenshots in the book. All this may be because of unfamiliarity with VB.Net, but aren’t these pakages aimed at people looking for a painless transition from VBA to .Net?

    On the plus side I found the e-mail support excellent.

    How did you find the documentation?

  4. Marcus from London Says:

    Great review, Simon.

    Have you (or anyone else) played around with managed COM add-ins. My main interest is finding a sound replacement to VB6 capable of the whole shebang.

    Eric, thanks for the input. I was initially hesitant with no trial version available but will give it a try.

  5. Marcus from London Says:

    “…I never do UI development…”

    I think that answers my question, Simon :P

  6. Sergey Grischenko (a member of the Add-in Express team) Says:

    Hi Charles,

    I am a member of the team that developed the Add-in Express tool. I carried out some testing of our XLL and I can’t confirm your results.
    In my tests, Add-in Express XLL is 1.16 time slower than a similar function written in native C++. What version of Add-in Express did you use for testing? Can you please send me the code of your test projects?
    Probably I will able to correct something in our code to speed up Add-in Express?

  7. Dennis Wallentin Says:

    Hi Folks,
    I’m one of those die-hard power user of Add-in Express’s tool and up to this date I have never had any issues with shipped solutions to clients.

    It’s a real RAD-tool and deployment is done very smoothly.

    The speed of XLLs may be of importance in theory but not that much in practical. I don’t work with any financial institutes but so far no clients have raised any issues/questions regarding speed and performance.

    Kind regards,
    Dennis

  8. Charles Williams Says:

    Hi Sergey,

    I asked about this in the ADDIn Express forums: see this thread which contains the code

    http://www.add-in-express.com/forum/read.php?FID=5&TID=5586&MID=27674#message27674

  9. Sergey Grischenko (a member of the Add-in Express team) Says:

    Hi Charles.

    Thank you for the link. But the code you provided in the topic is the function written for an Excel Automation add-in.
    The performance of such functions is very slow as these functions make calls to the COM model of Excel directly.
    XLL uses Excel Worksheet API as opposed to Excel Automation add-ins. This API doesn’t use COM, which makes faster calculations possible.
    To use Excel Worksheet API in Add-in Express, you need to create a new XLL add-in using the ADX XLL Add-in wizard.

  10. Charles Williams Says:

    Hi Sergey,

    I tried both the ADX Automation and XLL interfaces.

    The ADX XLL interface took 100 millisecs
    the ADX Automation interface took 170 millisecs

    VB6 Automation took 66 millisecs
    C++ XLL took 37 millisecs

    If you have better VB.Net code for the XLL Interface I would love to see it (although I no longer have ADX installed to do any testing).

  11. Sergey Grischenko (a member of the Add-in Express team) Says:

    Hi Charles.

    Thank you for the answer. I tested your function and got the following results.
    C++ XLL: 546 – 608 ticks
    ADX XLL: 872 – 890 ticks (if the array is double[,])
    1011 – 1030 tick (if the array is object[,])

  12. Charles Williams Says:

    Hi Sergey,

    Thanks for testing, your results look somewhat better than mine;

    Doubles is faster, but since I need to develop general purpose functions that can accept any kind of data I unfortunately think I need to use Object.

  13. Mattias Says:

    Hi Charles,

    When I look your performance discussion with Add-in Express, the problem is the marshaling of data. Yes, that always takes time.

    However, does this time really matter? I would assume that your XLL does more complex calculation, so that this overhead should be minor compared to the total time.

    Going down the C++ path will cost you a lot of extra development time instead compared to managed languages like VB6, C#, VB.NET, unless you already know C++. The end-result will be that your productivity suffers.

  14. Charles Williams Says:

    Hi Matthias,

    I don’t work in the city so I do not develop UDFs that do extremely complex calculations. Even if I did then C++ would be the performance standard to aim at.
    My focus is on general purpose UDFs/Addins that manipulate data, and therefore the time to transfer ranges of data from Excel to the UDF/Addin is usually the dominant factor.
    Using a tool like XLL+ its fairly easy and productive to code UDF stuff in C++.
    But for general purpose Addins containing an EUI I mostly code in other languages

  15. bervukas Says:

    Hi Simon,

    I’ve also reviewed ADX, and in my post I describe a few new product features not covered in your article:
    http://www.bernardvukas.com/add-in-express-for-office-and-net-review/

    I hope you enjoy it!

    –Bernard

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: