Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
- in sumproduct | Excel Worksheet Functions | |||
How to use RIGHT() in sumproduct | Excel Worksheet Functions | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions |