Sumproduct in VB

[Jay]

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?

 

[Joe User]

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

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?

 

[Dougaj4]

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

 

[Jay]

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

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

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?

 

[Jay]

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

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
.

 

[Dougaj4]

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

I want to be able to use the sumproduct function in code to calculatesome
fairly complex formulas.  Within the sumproduct function I'm tryingto use a
selection critera.  The range "Wave" is a named range.
This works fine in a regular worksheet function.  It multiplies thecells 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.

 

[Jay]

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

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

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.

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

 

[Dana DeLouis]

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

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

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?