View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Brian Brian is offline
external usenet poster
 
Posts: 683
Default VBA Efficiency Question

The function below works perfectly, but it is very slow for large tables. I
can obviously achieve the same result more efficiently with an array formula,
but the syntax of this function is more intuitive and much easier for my
end-users to utilize. Any thoughts on how to speed up this function? Thanks.

Function TableSum(ByVal RowValue, ByVal ColumnValue, Ref As Range) As Double
Dim x, y As Long
For y = 2 To Ref.Rows.Count
If Ref(y, 1) = RowValue Then
For x = 2 To Ref.Columns.Count
If Ref(1, x) = ColumnValue Then
TableSum = TableSum + Ref(y, x)
End If
Next x
End If
Next y
End Function