On 12/16/2009 10:42 PM, Jay wrote:

This is GREAT! Thank you. The "Evaluate" method is EXACTLY what I was
hoping for. Thanks so much,
this will help me solve a really challenging problem!

As a side note.. If it gets too complicated, here's just another
technique. I like to use "_" to identify short lived names.
Not any better. Just one way to overcome string complexity (for me).

Sub Demo()
Dim Ans

With ActiveWorkbook
.Names.Add "X", "--(Wave=2)"
.Names.Add "_R1", Range("A1:A10")
.Names.Add "_R2", Range("B1:B10")

Ans = [SUMPRODUCT(X,_R1,_R2)]

.Names("X").Delete
.Names("_R1").Delete
.Names("_R2").Delete
End With
End Sub

= = = = = = = = = = = = = = =
HTH :)
Dana DeLouis


Regards,

Jay

"Joe User" wrote:

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 -----

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?