Excel "Add Ins"

[Ben]

Hi There

I need to add some User Defined Functions to Excel 2003 - the functions are
CPU intensive.

The existing spreadsheet has the functions written in Excel VBA (as an XLA
add in), and they are very slow.

What are my choices ? Is this correct:
(1) Write an XLL in C++
(2) Write a COM Add in in C++
(3) Write a COM Add in in C#
(4) Write an Automation Add In in C#

Has anyone done any comparisons on the performance of XLL's in C++ versus
Automation Add Ins in C# ? I suppose C# is a great deal slower because of
the Interop ?

TIA

 

[Leith Ross]

Hello Ben,

The comparasions probably are available somewhere, but tracking the
down may prove difficult. Since the functions are CPU intensive yo
want threads that are aren't very far removed from machine code. A
object oriented langauge like C++ offers that capability. The downsid
is the creating objects that comply with the Component Object Mode
(COM). COM has over 200 built-in interfaces. Unless you are ver
familiar with constructing COM objects, don't attempt this as a firs
time project. A XLL would be easier to create and the speed tradeof
can be kept to a marginal level. C# would be a good choice if th
workbook were to be web based, but would still be slow even if i
isn't.

Sincerely,
Leith Ros

 

[RB Smissaert]

Putting the code in a VB6 ActiveX dll can make it faster as well and has the
benefit that it is very easy.
The other thing is to look at are the functions themself and see if they can
be speeded up.
Maybe it is worth to post them here.

RBS

 

[Bob Phillips]

I agree with Bart on the ActiveX DLL, it is the easiest to get up an
running, VB being similar enough to VBA to make it straightforward.

As well as looking at the formulae, check whether any intermediate
calculations can be used , rather than repeat long calculations many times.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peter T]

Hi Bob & Bart,

With my particular UDF's I only find a relatively small performance increase
by processing in a VB6 ActiveX dll. Obviously this observation only applies
to my own functions and my method of implementation. FWIW the dll only does
calculations like x = y * 2, albeit fairly intensively. Not object model
things like x = Range("A1") * 2.

Possibly I'm not doing things as efficiently as I might, in particular my
UDF's exist in the first place in a normal addin like this -

UDF's in a normal addin module. First do a few pre-checks on inputs, eg
check if a input is "acceptable", is it an array, etc. Pass inputs to my dll
function to process, then the addin UDF returns result or an array of
results.

Could I do this more efficiently ?

A bit more info - the dll itself is a full app. It uses for other purposes
the same functions exposed to the UDFs. Instancing for dll class referenced
by the addin UDF's is GlobalMultiUse, so the dll functions are called
directly by the addin functions.

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

Haven't looked carefully into what kind of code gets faster and what code
not, but in general I find the speed gain is between very little to a bit
over twice as fast.

Another option to get faster code might be to make a true, normal Windows
dll with PowerBasic.
I got this, but I must admit I didn't find it as easy as it was supposed to
be and I haven't used it yet.
It has been claimed though that certain types of code can be speeded up
enormously.

Laurent Longre's .xll functions are good as well and I have used his array
sorting functions, making array sorts
about twice as fast. There was some drawback though, which I can't remember
now and I am not currently using it in
my commercial app.

RBS

 

[Peter T]

Hi Bart,

Thanks for your comments. PowerBasic looks interesting but I note your
comments about not so easy to learn & use, one day perhaps.

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

It probably is quite easy, but I had the impression before I bought it that
it would be just like VBA or VB and that is not the case.
The IDE is not that great either.

RBS

 

[Ben]

Thanks for your feedback ! After looking at COM I think I will avoid it
initially and go for an XLL.

I will also avoid VSTO as I am stuck using VS 2003 (long story) and anyways,
VSTO looks to me like a technology that is not quite ready. It is
essentially .NET "shoe-horned" into Office, which is COM based.

I will probably go over to VSTO when Office 12 arrives and it is truly a
..NET based product.