Improving speed with VBA
 

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?

Improving speed with VBA
 

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?

Improving speed with VBA
 

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

--
Regards,
Tom Ogilvy


"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?

Improving speed with VBA
 

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?


.

Improving speed with VBA
 

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?


.

Improving speed with VBA
 

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.

--
Regards,
Tom Ogilvy

Bob Phillips wrote in message
...
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?


.

Improving speed with VBA
 

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?

Improving speed with VBA
 

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=MP...4beed4898abb1%
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

In article ,
says...
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?


.