Sumproduct in VB

J

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

Joe User

Jay said:
=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 said:
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?
 
D

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
 
J

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

Joe User said:
Jay said:
=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 said:
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?
 
J

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

Dougaj4 said:
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
.
 
D

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



Dougaj4 said:
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.
 
J

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

Dougaj4 said:
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 said:
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.
.
 
D

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

Joe User said:
Jay said:
=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 said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top