Doug,

So far I've gone the route of using Evaluate and it's working great. I
haven't had time to stack it up against the looping approach but when I have
some more time on my hands I'll try it out and post the findings in terms of
which is faster.

Jay

"Dougaj4" wrote:

On Dec 17, 2:45 pm, Jay wrote:
Doug,

Thanks for this. This makes a lot of sense. If this does in fact turn out
to be faster than the Evaluate approach it's worth considering. The final
calculation is going to involve multiple sumproduct types of calculations
across thousands of rows of data, so I'm not convinced a for...next loop will
be fast, but I might try it both ways to find out.

Thank for your help!

Regards,

Jay



"Dougaj4" wrote:
On Dec 16, 5:33 pm, Jay wrote:
I want to be able to use the sumproduct function in code to calculate some
fairly complex formulas. Within the sumproduct function I'm trying to use a
selection critera. The range "Wave" is a named range.

This works fine in a regular worksheet function. It multiplies the cells in
columns A and B where Wave = 2.

=sumproduct(--(Wave=2),$A$1:$A$100,$B$1:$B$10)

In VB I have the function below, which surprisingly works just fine as
written. For now ignore X...

Function mytest(X as Variant, rng1 as Variant, rng2 as Variant) as double
mytest = Application.WorksheetFunction.SumProduct([--(Wave=2)],
rng1, rng2)
End Function

Does anyone know of a way to pass the --(Wave=2) part of the equation
through the variable X? What I would like to do is...

mytest = Application.WorksheetFunction.SumProduct(X, rng1, rng2)

Would it be possible to have X as a string? Can strings be used as arguments
in a worksheetfunction?

You can replace the Sumproduct with a simple loop, which should be
faster:

Function mytest(X As Variant, rng1 As Variant, rng2 As Variant) As
Double
Dim n As Long, i As Long
If TypeName(X) = "Range" Then X = X.Value2
If TypeName(rng1) = "Range" Then rng1 = rng1.Value2
If TypeName(rng2) = "Range" Then rng2 = rng2.Value2

n = UBound(X)
For i = 1 To n
If X(i, 1) = 2 Then
mytest = mytest + rng1(i, 1) * rng2(i, 1)
End If
Next i
End Function
.- Hide quoted text -

- Show quoted text -

Jay - yes, if you are using a single "evaluate" call on 1000's of rows
then it might well be quicker.

Let us know how you go.
.