Sumproduct in VB
 

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?

Sumproduct in VB
 

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

Sumproduct in VB
 

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

Sumproduct in VB
 

Joe,

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!

Regards,

Jay

"Joe User" wrote:

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

Sumproduct in VB
 

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
.

Sumproduct in VB
 

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.

Sumproduct in VB
 

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

Sumproduct in VB
 

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?