Excel – COM v XLL

Harlan asks:

“..from the USER’S perspective, what are the benefits of COM add-ins vs XLL add-ins? My big gripe against XLL add-in text functions is that they can only return up to 255 characters. If COM add-in text functions can return 32767 characters, that’d convince me of their value. If not, why should I prefer COM to XLL add-ins?”

What do you think? Note the USER bit.

Heres my view:

Yes a COM dll can return a long string (to VBA) working with long strings is a bit of an adventure in Excel cells (and text boxes), even with VBA. XLLs in Excel 12 can also return long strings finally, (and cells can display more than 1k characters).

As a developer I think you can pretty much get the same functionality using either approach, but generally the xll way will be more painful, more likely to crash Excel or leak memory, and run either faster or waay faster. So the USER POV is maybe COM add-ins can be cheaper, or more quickly developed, more reliable (?), but maybe run a bit slower. For a VBA user POV COM is something any VB dev understands. To use xll functions from VBA you need to either use Declares to the xll or Application.run, Excel devs won’t sweat this but pure VB folks might need some hints. UI is tons easier in COM, anyone remember XLM dialogs?

There are also a few gaps in xll functionality (its still based on the C API from Excel 97 – so most of that new stuff isn’t available, or at least not documented). Mind you there are few gaps in the COM object model too. xlls are easy to deploy, no registry access required. The xll stuff got a big refresh in Excel 2007, and is still the recommended add-in approach. (XLLs are the add-in technology of choice for Excel)

Summary: from a users POV direct differences are minor. From a Dev POV xlls are generally more effort, maybe this is why so few people are still doing them. In fact who, apart from City type financial institutions, and SW vendors gets xlls written? (Its a popular search term to get to codematic, but I’ve only had the odd sniff of work.)

I’m thinking VB6 for COM btw, but I guess the same applies to .net.

Thats my view, what do you think?

cheers

Simon

Advertisements

7 Responses to “Excel – COM v XLL”

  1. Nick Hebb Says:

    I guess I’ve always thought of XLL’s used for solely for writing custom worksheet functions. Is it feasible to write them for full-scale add-ins, complete with UI’s?

  2. Ross Says:

    >>Is it feasible to write them for full-scale add-ins, complete with UI’s?

    Yes I believe it is, in fact you “can” do anything you can do with VB in a XLL

    Maybe .xll is better if you have 97 or 95!

    the DNA project suggests that using a xll to marshal .Net code might be quicker than using the com interop interface – I still cant really get my head around how to shim .Net code (maybe because I’ve not really tried!)

    [http://exceldna.typepad.com/]

    But apart form speed and easy of deployment I can’t think of any reasons to use .xll over com – or even a win 32 dll?

    One small consideration to the customer might be that if they get a Excel C API .xll built then there scope for people to come a maintain it is quite small. If they get one done in Com, the world and his dog and tinker with her? – is that a bus I see before me :-)

  3. Simon Says:

    xlls are mainly used for wsf, partly because the native Excel C API UI stuff is a bit kippery. But you can drop into MFC to get a decent UI (or even start up COM). You can probably do even better in Delphi. I’m going to do more xll posts so we can pick them apart then.
    ExcelDNA looks good, its on my list of things to try.
    Getting range.value in VBA is nearly 10 times slower than using the C API.
    an .xll == (.dll (win32) + Excel register functions)
    ie .xll ~ .dll + REGISTER() macros
    If your .xll stuff will only be called from VBA code you could almost just use a .dll, almost…

  4. Marcus Says:

    I’m a bit similar to Nick and have pidgeon-holed XLL’s in the maths function library category. Probably as I’ve seen them used in Financial Services. Anything that involves a pretty GUI I would have relegated to a XLA or COM add-in.

    Cheers – Marcus

  5. Nick Hebb Says:

    In retrospect that was a silly question since you could drop down into Petzold-land and do pure WINAPI UI’s or MFC as suggested.

    I currently use VB6 for COM add-ins, but as the viability of VB6 fades I’m looking for a better alternative than .NET. It feels like Microsoft has left a gaping void, and the best option going forward maybe Delphi. But that’s another topic entirely…

  6. Simon Says:

    Nick
    There are some very good Delphi components and libraries around. I’d be interested to hear if the COM stuff is comparable to VB. I think we need some more discussions around VB6’s fate. I had written it off, but I’m now thinking its got a good few years left in it. I’m currently developing COM add-ins with it. Like you I have an eye out for an alternative, I’ll probably migrate it to C++/MFC. I don’t see .net as a viable alternative for what I want – unless it gets a linker maybe (xenocode and salamader maybe?).
    cheers Simon

  7. Jim Johnson Says:

    I used an xll for my add-in, partly because I expected it to be faster (not an empirical decision) and partly because I didn’t have VB6 to hand. I mucked around with writing a COM library in C++ but got further, quicker with the simpler xll, which met my needs adequately. I used WTL for the GUI, which works very well, I think; certainly a big improvement over vba forms.

    I found the xll stuff to be manageable, although marshalling all of the xlopers takes some fun out of it. There are several areas that the C API doesn’t cover, atleast pre version 12, so I guess it’s worth understanding these before proceeding.

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: