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

David,

Evaluate it

?evaluate("SumProduct(2,3)")

--

HTH

RP

"David" wrote in message
...
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"
--
David

?Application.WorksheetFunction.Sumproduct(array(2) ,array(3))
6

--
Regards,
Tom Ogilvy

"David" wrote in message
...
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"
--
David

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


"Tom Ogilvy" wrote:

?Application.WorksheetFunction.Sumproduct(array(2) ,array(3))
6

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

HTH

RP

"David" wrote in message
...
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


"Tom Ogilvy" wrote:

?Application.WorksheetFunction.Sumproduct(array(2) ,array(3))
6

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.

--
Regards,
Tom Ogilvy

"David" wrote in message
...
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


"Tom Ogilvy" wrote:

?Application.WorksheetFunction.Sumproduct(array(2) ,array(3))
6

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(Tr ue,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?
--
David

"Tom Ogilvy" wrote:

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.

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

--
Regards,
Tom Ogilvy



"David" wrote in message
...
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(Tr ue,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?
--
David

"Tom Ogilvy" wrote:

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.

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"David" wrote in message
...
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(Tr ue,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?
--
David

"Tom Ogilvy" wrote:

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,
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
--
David


"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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