Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formulas in VB
Hi,
I have a number of ormulas that are performing rather slowly, calculation of the workbook takes approximately 2 minutes. My question is, if they were re written to calculate in vb would this speed things up? Thanks in advance John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formulas in VB
Almost certainly not. Just the overhead involved in a VB call generally
makes UDF unattractive for functions that can be performed on the worksheet. Take a look here for tips on speeding things up: http://www.decisionmodels.com/optspeed.htm In article , John Contact wrote: Hi, I have a number of ormulas that are performing rather slowly, calculation of the workbook takes approximately 2 minutes. My question is, if they were re written to calculate in vb would this speed things up? Thanks in advance John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formulas in VB
Thanks seems I missed the obvious sumproduct function to replace my arrays
i.e =SUM(IF(DUDListData!$T$2:$T$65536=DUDListData!$Y$1 ,1,0))/COUNTIF(DUDListData!$A$2:$A$65536,"1") "JE McGimpsey" wrote: Almost certainly not. Just the overhead involved in a VB call generally makes UDF unattractive for functions that can be performed on the worksheet. Take a look here for tips on speeding things up: http://www.decisionmodels.com/optspeed.htm In article , John Contact wrote: Hi, I have a number of ormulas that are performing rather slowly, calculation of the workbook takes approximately 2 minutes. My question is, if they were re written to calculate in vb would this speed things up? Thanks in advance John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formulas in VB
do you really need to check the 65k rows? if you can make that range smaller, performance will definitely improve. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam John Contact wrote : Thanks seems I missed the obvious sumproduct function to replace my arrays i.e. =SUM(IF(DUDListData!$T$2:$T$65536=DUDListData!$Y$1 ,1,0))/COUNTIF(DUDLi stData!$A$2:$A$65536,"1") "JE McGimpsey" wrote: Almost certainly not. Just the overhead involved in a VB call generally makes UDF unattractive for functions that can be performed on the worksheet. Take a look here for tips on speeding things up: http://www.decisionmodels.com/optspeed.htm In article , John Contact wrote: Hi, I have a number of ormulas that are performing rather slowly, calculation of the workbook takes approximately 2 minutes. My question is, if they were re written to calculate in vb would this speed things up? Thanks in advance John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formulas in VB
if you want to create a "variable" address let's say the "lookup" range varies between 10000 to 40000 rows AND you have many sumproduct type lookups... your best bet for a "performance" formula: use a cell with a formula that returns the "correct" address of the rangetocheck then in your sumproducts use indirect to get the range. you lose some by using indirect. you win some by having your formula countif! working on the correct nr of rows. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : do you really need to check the 65k rows? if you can make that range smaller, performance will definitely improve. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam John Contact wrote : Thanks seems I missed the obvious sumproduct function to replace my arrays i.e. =SUM(IF(DUDListData!$T$2:$T$65536=DUDListData!$Y$1 ,1,0))/COUNTIF(DUD Li stData!$A$2:$A$65536,"1") "JE McGimpsey" wrote: Almost certainly not. Just the overhead involved in a VB call generally makes UDF unattractive for functions that can be performed on the worksheet. Take a look here for tips on speeding things up: http://www.decisionmodels.com/optspeed.htm In article , John Contact wrote: Hi, I have a number of ormulas that are performing rather slowly, calculation of the workbook takes approximately 2 minutes. My question is, if they were re written to calculate in vb would this speed things up? Thanks in advance John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formulas in VB
Excellent, that worked a treat
"keepITcool" wrote: if you want to create a "variable" address let's say the "lookup" range varies between 10000 to 40000 rows AND you have many sumproduct type lookups... your best bet for a "performance" formula: use a cell with a formula that returns the "correct" address of the rangetocheck then in your sumproducts use indirect to get the range. you lose some by using indirect. you win some by having your formula countif! working on the correct nr of rows. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : do you really need to check the 65k rows? if you can make that range smaller, performance will definitely improve. -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam John Contact wrote : Thanks seems I missed the obvious sumproduct function to replace my arrays i.e. =SUM(IF(DUDListData!$T$2:$T$65536=DUDListData!$Y$1 ,1,0))/COUNTIF(DUD Li stData!$A$2:$A$65536,"1") "JE McGimpsey" wrote: Almost certainly not. Just the overhead involved in a VB call generally makes UDF unattractive for functions that can be performed on the worksheet. Take a look here for tips on speeding things up: http://www.decisionmodels.com/optspeed.htm In article , John Contact wrote: Hi, I have a number of ormulas that are performing rather slowly, calculation of the workbook takes approximately 2 minutes. My question is, if they were re written to calculate in vb would this speed things up? Thanks in advance John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
3d array formulas | Excel Discussion (Misc queries) | |||
Array Formulas | Excel Programming |