Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mimic a watermark | Excel Discussion (Misc queries) | |||
Mimic keystrokes? | Excel Programming | |||
mimic goalseek | Excel Programming | |||
Code to mimic vlookup | Excel Programming | |||
Mimic Undo actions | Excel Programming |