In most cases I've investigated, the "bottleneck" is in moving data back and forth between XL's
data space and VBA's data space, with writing to the worksheet taking about 6 times as long as
reading. The other major speed issues are screen updating, automatic calculation, and events.

VBA isn't always slower: I've written a substitute for the XIRR function that in fact is faster.
Of course XIRR isn't built-in -- it's in the ATP...


On Sat, 11 Oct 2003 10:44:32 +0100, "Bob Phillips" wrote:

Steven,

You shouldn't really be surprised. Don't forget that the worksheet functions
will be compiled code which will be much more efficient than VBA, the MS
programmers will know all about the inner mechanics of Excel and will be
able to take advantage of them, they undoubtedly have access to some hidden
functionality that MS does not expose to the world, and finally our VBA is
written by mere mortals, with the inefficiencies we bring to play. So all in
all, Excel functions have it all going for them.

Regards

Bob

"Steven Cheng" wrote in message
...
Thanks Bob & Tom for the tip. I guess I will have to
rethink this some more. I am quite surprise that the VBA
would generally be slower though.
-----Original Message-----
Steve,

Generally speaking, as long as the formulae are
optimised, VBA will always
be much slower than built-in functions.

You can improve the speed of formulae if expressions are
repeated by storing
the repeated expression in a separate cell, e.g.

=IF(SUM(A1:A100)0,SUM(A1:A100,"")

store =SUM(A1:A100) in a separate cell, A999 say, and
use

=IF(A9990,A999,"")

meaning it only gets resolved once.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Steven Cheng" wrote in message
...
I am using a series of sumproducts that filter a table
of
data based on several criteria (e.g. if field1=X AND
field2=Y, etc...) and it would seem to be slowing the
file
in terms of the recalculation speed. I have turned the
spreadsheet to manual calculation as the result. Would
writing the code in VBA to do the same thing that I am
doing with sumproduct be a performance improvement?