A common problem I find is that the very useful D* worksheet functions
require two rows in order to specify their ctiteria. This is fine
until you want to put on on every line in a big report.

For years I've put up with this, but recently decided to write my own
funciton in VBA with a nicer criteria specifiction model (one which
uses at most one line).

In order to provide a rich set of test operators (and also to keep
consistency with the D* functions) I have written my function to
actually use worksheetfunction.countif() to do the actual comparison.

My function merrily goes down the input range using countif to select
cells, then I do with them what I will and return a result.

So far, so good.

Except for very large datasets it all goes a bit slowly.

Now - to the question:

One standard way to get VBA functiuons which operate on a large dataset
to run quiker is to assign the range to a variant (array) at first, and
then use the array. This cuts out he continual spreadsheet conversion.

BUT

worksheetfunction.countif() requires a range as its input parameter.

Anybody got any clever ideas?

My rule of thumb says its usually faster to either use a range variable and
never transfer the data to VBA at all, or transfer to VBA and do all the
processing on the transferred array.

But you can always do both: if you define the input parameter as a range you
can then reference it as a range object.
Then you can also assign the range object to a variant variable containing
an array to get the data when you want to process it as an array.

It would probably be faster if you rewrote your use of COUNTIF in VBA to
work against the array and avoided worksheet functions.


regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Peter" wrote in message
ups.com...
A common problem I find is that the very useful D* worksheet functions
require two rows in order to specify their ctiteria. This is fine
until you want to put on on every line in a big report.

For years I've put up with this, but recently decided to write my own
funciton in VBA with a nicer criteria specifiction model (one which
uses at most one line).

In order to provide a rich set of test operators (and also to keep
consistency with the D* functions) I have written my function to
actually use worksheetfunction.countif() to do the actual comparison.

My function merrily goes down the input range using countif to select
cells, then I do with them what I will and return a result.

So far, so good.

Except for very large datasets it all goes a bit slowly.

Now - to the question:

One standard way to get VBA functions which operate on a large dataset
to run quiker is to assign the range to a variant (array) at first, and
then use the array. This cuts out he continual spreadsheet conversion.

BUT

worksheetfunction.countif() requires a range as its input parameter.

Anybody got any clever ideas?