Trying to Mimic an Array Function in VBA

[Brian Cass]

Hello,

I've been trying to create a VBA function that emulates one of my favorite
array functions. The function summarizes a data set for all rows where the
first item in the row matches a supplied value and all columns where the
first item in the column matches another supplied value.

I can create a loop that cylces through the data set summarizing the data I
am seeking, but such an approach is very slow. Accordingly, I've been
seeking a faster solution and have turned to the EVALUATE method to mimic an
array function.

Here's what's I've got so far:

Function TableSum(RowValue, ColumnValue, Ref As Range)
RefText = Ref.Address(external:=True)
RowText = RowValue.Address(external:=True)
ColText = ColumnValue.Address(external:=True)
TableSum = Evaluate("SUM((INDEX(" & RefText & ",0,1)=" & RowText &
")*(INDEX(" & RefText & ",1,0)=" & ColText & ")*IF(ISNUMBER(" & RefText &
")," & RefText & ",0))")
End Function

The funciton works perfectly SO LONG AS all the inputs are from the calling
worksheet. Once I reference ranges on other worksheets, the function does
not work.

Any thoughts? Thanks

 

[Joel]

Function TableSum(RowValue, ColumnValue, Ref As Range)
TableSum = 0
for RowCount = 2 to Ref.Rows.Count
if Ref(RowCount,1).Value = RowValue then
for ColCount = 2 to Ref.Columns.Count
if Ref(1,ColCount).Value = ColumnValue then
TableSum = TableSum + Ref(RowCount,ColCount).Value
end if
next ColCount
end if
Next RowCount
end Sub


end function