Passing Discontinuous Range (Array) of Values to User-Defined Function

[Louis]

What I am trying to do seems simple, but I haven't figured out how to
do it. I need to add the contents of the cells in one array, subject
to the values in another array. Normally I would utilize one of the
SUM functions, such as SUM, SUMIF, or SUMPRODUCT. However, they do
not work when the elements of the arrays are on multiple sheets in the
Excel workbook. For example:

Array 1: Sheet1:Sheet5!A4 (which contains the values: 1, 0, 0, 1,
0)
Array 2: Sheet1:Sheet5!B4 (which contains the values: 150, 324,
846, 254, 98)

I need to compute the sum of the items in Array 2 whose
corresponding items in Array 1 are equal to 1. In the example above,
the value I would expect to obtain is 404, which is equal to 150 + 254
(the only items in Array 2 whose corresponding items in Array 1 are
equal to 1).

I have tried the following VBA code (and several other
permutations) to no avail. And I don't understand why the result of
the UBound function is zero. Can anyone help me with this?

Function ConditionalSum(testArray, ParamArray argumentArray() As
Variant) As Double
Dim currentRange As Range
Dim currentArea As Range
Dim i As Integer
Dim col As Integer
Dim row As Integer
Dim rowCount As Integer
Dim colCount As Integer
Dim areaCount As Integer
Dim sum As Double

sum = 0#
MsgBox ("UBound = " & UBound(argumentArray))
For i = 1 To UBound(argumentArray)
Set currentRange = argumentArray(i)
Set testRange = testArray(i)

rowCount = currentRange.Rows.Count
colCount = currentRange.Columns.Count

For col = 1 To colCount
For row = 1 To rowCount
If testRange(row, col) = 1 Then
sum = sum + currentRange(row, col)
End If
Next row
Next col
Next i

ConditionalSum = sum

End Function

[Charles Williams]

Hi Louis,

VBA does not understand multi-sheet ranges: your UDF is not going to work.

You would have have to use character arguments and decode them.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

[Harlan Grove]

"Charles Williams" <Ch*****@DecisionModels.com> wrote...

VBA does not understand multi-sheet ranges: your UDF is not going to work.

You would have have to use character arguments and decode them.

Impractical. OP would need to write a lightweight range reference parser
since there's nothing in VBA that can accomodate converting the text

"'foo:bar'!X99"

into anything useful other than 'foo'!X99 and 'bar'!X99 separately. But that
gives an alternative approach: pass 3D references to UDFs using two
arguments rather than one: top-upper-left and bottom-lower-right cells. So
to replace the built-in function (the following call doesn't work)

=SUMIF(foo:bar!X99:Y200,crit)

with a UDF call, consider calling syntax like

=SUMIF3D(foo!X99,bar!Y200,crit)

The advantage of this is that you can use (2D) range references as arguments
rather than having to construct 3D references as strings in the UDF call
argument then immediately deconstruct them in the UDF.