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