“..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?