Replacement for Dsum - calling countif from aVBA
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?
|