Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improving the Discussion Boards | Excel Discussion (Misc queries) | |||
Improving Recalculation Speed for Complex Links | Excel Discussion (Misc queries) | |||
Improving formula | Excel Worksheet Functions | |||
Improving use of Worksheets | Excel Worksheet Functions | |||
need help in improving this macro | Excel Programming |