Trying to Mimic an Array Function in VBA
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 |
Trying to Mimic an Array Function in VBA
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 "Brian Cass" wrote: 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 |
All times are GMT +1. The time now is 01:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com