raw speed for excel worksheet functions? - xll -or com addin?

[John Doe]

Hi,

A question for those of you who have experimented with this.
I want to implement some heavy duty mathematical processing which is
accessible from an excel front end.

As I understand it my options are:

1. Pure VBA functions (very slow but compatible with all versions of
excel from excel 97 upwards)
2. Pure C/C++ dll with wrapper functions in VBA (much faster but still
relatively slow for large worksheets with many cell formulae - again
available in all versions of excel 97 upwards)
3. A COM dll with wrapper functions in VBA (same as 2 above except
allows use in other projects such as a VB project - eughhh - I know -
I don;t see that as a plus either)
4. A COM dll which can be seen straight from worksheet level (I think
excel 2002 supports this? - need guidance on this though...)
5. Good old fashioned xlls in pure C/C++

I've sort of discounted options 1 and 2. What I'm really left with is
a core set of DLLs with an interface of an XLL or a COM DLL.

What I really want to know is do people find COM slower in terms of
real speed - especially if anyone has experimented with option 4
compared to 5.

And are Xlls deprecated in favour of COM?

(My gut preference is XLLs - I just don't trust COM for speed; Xll
coding is a bit dirty but not in a way that seems to kill speed)

(and btw has anyone had problems with compiling their Xlls with Visual
Studio .NET 2003 and its treatment of string literals?)

Any suggestions/guidelines/criticism would be much appreciated.

Thanks in advance,

M.

 

[Rob Bovey]

Hi John,

And are Xlls deprecated in favour of COM?

I think the folks at MS would like them to be, but nobody else is paying
attention.

(My gut preference is XLLs - I just don't trust COM for speed; Xll
coding is a bit dirty but not in a way that seems to kill speed)

I personally haven't done any performance comparisons between XLLs and
Automation Add-ins (which are only supported in Excel 2002 and higher), but
Automation Add-ins are at best equal to XLLs and very likely slower due to
the overhead imposed by COM. Whether the difference is enough to matter for
any given application would require testing, which I've never done because
everything I write has to be backward compatible with Excel 2000 and/or
Excel 97.

(and btw has anyone had problems with compiling their Xlls with Visual
Studio .NET 2003 and its treatment of string literals?)

The method for byte-counting strings in the function table that was
demonstrated in the Excel SDK was never valid, It slipped by because a quirk
in the MS VC compiler prior to 2003 placed string literals in writeable
memory. This is no longer the case in 2003, so your function table simply
needs to be a 3D char array, e.g.:

#define NUM_FUNCTIONS 5
#define NUM_REGISTER_ARGS 11
#define MAX_LENGTH 255

static char FunctionTable[NUM_FUNCTIONS][NUM_REGISTER_ARGS][MAX_LENGTH] =
{
// function table initializer.
};

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *