Improving speed with VBA

[Steven Cheng]

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?

 

[Bob Phillips]

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(A999>0,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)

 

[Tom Ogilvy]

The alternative would be to use either a dummy column to mark the rows to be
included and use a faster function such as sumif against that, or use DSUM
or similar, which requires a separate criteria range for each formula (if
you are gathering values over a set of criteria values).

 

[Steven Cheng]

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.

 

[Bob Phillips]

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

 

[Tom Ogilvy]

Bob is certainly correct that a VBA function would not be as fast as a
builtin function performing in the same manner.

VBA can be applied selectively however (rather than using a UDF). If you
only need your results updated occasionally or selectively, you could remove
you formulas and run your macro only when you need it.

 

[Myrna Larson]

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

 

[Tushar Mehta]

As Tom and Myrna have pointed out, VBA does have speed advantages under
certain circumstances. So, the general rule should be "in general, VBA
functions are more likely to be slower than XL-native functions."

For another example of when VBA can be faster, see
http://www.google.com/groups?selm=MPG.197480abb4beed4898abb1%
40msnews.microsoft.com
--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions