WorksheetFunction.Sumproduct(syntax...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for any advice you may give
Can anyone please advise on the syntax for the arguments in the above
function?
I've tried: ?Application.WorksheetFunction.Sumproduct(2,3) .. expecting to
result with 6 but get the error message: "Unable to get the property of the
worksheet function class"
 
Tom & Bob,
Thanks for your help. I'm still floundering though ...
I'm trying to populate a Crosstab summary of a database using VBA arrays and
then transferring the result back to the worksheet. Please have a look at the
code below for me. The WorksheetFunction.Sumproduct arguments seem to be the
sticking point:

Sub PopulateCrossTab()
'Count ocurences of Colour and Price Code _
combinations in database
Dim Field_ColourData As Variant
Dim Field_PriceCodeData As Variant
Dim CrosstabRowHeader As Variant
Dim CrosstabColHeader As Variant
Dim TempArray() As Variant

'Load arrays
Field_ColourData = Range("B12:B23")
Field_PriceCodeData = Range("C12:C23")
CrosstabRowHeader = _
Range("Crosstab").Resize(, 1).Offset(, -1)
CrosstabColHeader = _
Range("Crosstab").Resize(1).Offset(-1)

'Use Sumproduct to calculate Crosstab values _
and store in TempArray
ReDim TempArray(1 To UBound(CrosstabRowHeader, 1), _
1 To UBound(CrosstabColHeader, 2))
For r = 1 To UBound(CrosstabRowHeader, 1)
For c = 1 To UBound(CrosstabColHeader, 2)
TempArray(r, c) = _
WorksheetFunction.SumProduct(--(CrosstabColHeader(c) =
Field_ColourData), _
--(CrosstabRowHeader(r) = Field_PriceCodeData))
Next
Next

'Transfer TempArray to worksheet
Range("Crosstab") = TempArray
End Sub
 
David,

I don't see where CrosstabRowHeader and CrosstabColHeader become arrays, as
far as I can see they are ranges.

You didn't do what either Tom or I suggested, so how would you expect it to
work. If you use Evaluate, that should work, but you will need to use Range
strings, and if the value to be tested against is a string, you will need to
add quotes around the variable in the evaluate statement.
 
Sumproduct, when used in VBA is looking for array arguments. You are trying
to build arrays, but that only works in a worksheet. You will need to use
evaluate to use that type of construct or build the arrays before providing
them to sumproduct.
 
Tom & Bob,
Thanks again for your helpful response.
I'm taking your advice and building arrays to be processed by
WorksheetFunction.sumproduct. Currently, the content of the arrays to be
processed is boolian True or False. I'm asking sumproduct to do the
equivalent of:
?Application.WorksheetFunction.Sumproduct(array(True,True),array(True,False))
which results in zero instead of the required result of 1 in this case.
I know I could use an 'if' structure when building the arrays to get 1s & 0s
(instead of True, False) and then get the expected result. For interest
though, is it possible to coerce the boolians to 1 or 0 as with the worksheet
methods?
 
Declare your arrays as Long

Sub TestSumProduct()
Dim Arr1(1 To 10) As Long
Dim arr2(1 To 10) As Long
For i = 1 To 10
If Rnd() > 0.5 Then
Arr1(i) = True
Else
Arr1(i) = False
End If
If Rnd() > 0.5 Then
arr2(i) = True
Else
arr2(i) = Fale
End If
Next
MsgBox WorksheetFunction.SumProduct(Arr1, arr2)
End Sub
 
I should point out that True coerces to -1 in VBA. But since we multiply
two arrays together, this produces positive 1 when both are true. If you
put in 3 arrays, you would need to account for the negative one.
 
Tom,
Thanks again for your advice. My code now works fine, including use of
arrays declared as long (to coerce the boolians). I can't wait to try this
code on big worksheet databases where the normal sumproduct function seems
very slow.
I've included my code below. Any further comments you have to help my
personal improvement would be sincerely appreciated:

Sub PopulateCrossTab()
'Count ocurences of Colour and Price Code _
combinations in database
Dim Field_ColourData As Variant
Dim Field_PriceCodeData As Variant
Dim CrosstabRowHeader As Variant
Dim CrosstabColHeader As Variant
Dim TestArray1() As Long
Dim TestArray2() As Long
Dim TableArray() As Variant

'Load arrays
Field_ColourData = Range("Colour")
Field_PriceCodeData = Range("PriceCode")
CrosstabRowHeader = _
Range("Crosstab").Resize(, 1).Offset(, -1)
CrosstabColHeader = _
Range("Crosstab").Resize(1).Offset(-1)


x = UBound(CrosstabColHeader, 2)
y = UBound(CrosstabRowHeader, 1)
Records = UBound(Field_ColourData, 1)

ReDim TestArray1(1 To Records)
ReDim TestArray2(1 To Records)
ReDim TableArray(1 To y, 1 To x)

For r = 1 To y
For c = 1 To x
'Build test arrays for sumproduct
For i = 1 To Records
TestArray1(i) = _
Field_ColourData(i, 1) = CrosstabColHeader(1, c)
TestArray2(i) = _
Field_PriceCodeData(i, 1) = CrosstabRowHeader(r, 1)
Next
TableArray(r, c) = _
WorksheetFunction.SumProduct(TestArray1, TestArray2)
Next
Next

Range("Crosstab") = TableArray

End Sub
 
transforming Excel array formulas to vba

Hi

I have a huge file with very sophisticated array formulas. My problem is that due to the over 10.000 formulas everything takes forever. Each new cell value or even applying a filter results in 'calculating...'

I wonder if and how I'd be able replace the array formulas with vba.

this is the preparation of the arrays in vba:

Dim rnge As Range
Dim siteArr, hopArr, ICMSArr, ISSArr, PVvalArr, POArr, POValArr, ProjArr As Variant
Set rnge = wsPV.Range(Cells(pvRw1, hopPVc), Cells(pvRwN, hopPVc))
hopArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, ICMSPVc), Cells(pvRwN, ICMSPVc))
ICMSArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, ISSPVc), Cells(pvRwN, ISSPVc))
ISSArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, POPVc), Cells(pvRwN, POPVc))
POArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, POvalPVc), Cells(pvRwN, POvalPVc))
POValArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, PVvalPVc), Cells(pvRwN, PVvalPVc))
PVvalArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, sitePVc), Cells(pvRwN, sitePVc))
siteArr = rnge.Value
Set rnge = wsPV.Range(Cells(pvRw1, ProPVc), Cells(pvRwN, ProPVc))
ProjArr = rnge.Value

Bfter this everything is in nice one dimensional arrays

But now I'd like to find a special condition in various arrays and retrieve a value of a result array.
My worksheet formula was this

{=SUMPRODUCT((project=H6)*(hop=I6)*(site=J6)*(ICMS>0)*(PVval))}

and I'd like it to be done by vba, so I programmed this:

siteAvalPVmM = Application.WorksheetFunction.SumProduct((ProjArr = projct) * (hopArr = hop) * _
(siteArr = siteA) * (ICMSArr > 0) * (PVvalArr))

the second part is even more complex.
Worksheet formula:

{=INDEX(POnb;MATCH(1;(hop=I6)*(site=J6)*(ICMS>0);0))}

and the vba attempt:

POnbA = Application.WorksheetFunction.Index(POArr, Application.WorksheetFunction.Match(1, _
(hopArr = hop) * (siteArr = siteA) * (ICMSArr > 0), 0))

Essentially I'm looking for a unique combination in the named areas (arrays). In the latter example I want to extract the PO number for the match hop-range = hop in I6, site-range = site A and ICMS-range >0

Is there a way to do this in vba? If so could anyone give me a clue?

thanks
FBr
 
Back
Top