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

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

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

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

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

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