"Jay" wrote:
[Jay wants the equivalent of the following Excel formula in VBA:]
=sumproduct(--(Wave=2),$A$1:$A$100,$B$1:$B$10)
[....]
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)

I believe you have to use Evaluate. Something like (untested):

mytest = Evaluate("SUMPRODUCT(--(Wave=2)," & _
rng1.Address & "," & rng2.Address & ")"


Would it be possible to have X as a string?

Yes if you use Evaluate, which may be obvious to you now. If you pass
"--(Wave=2)" to a string parameter called sCond, then:

mytest = Evaluate("SUMPRODUCT(" & sCond & "," & _
rng1.Address & "," & rng2.Address & ")"


----- original message -----

"Jay" wrote in message
...
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?