"Brian" wrote in message
...
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

Two things are immediately obvious to me

1) Assign the values of Ref.Rows.Count and Ref.Columns.Count to variables
and use the variables in the for loops
2) The declaration Dim x, y As Long will declare one of the variables as
long, the other as variant. I can't remember if it's the first or the
second. Anyway, you should use this kind of declaration

Dim x As Long
Dim y As Long

You end up with something like this

Function TableSum(ByVal RowValue, ByVal ColumnValue, Ref As Range) As Double
Dim x As Long
Dim y As Long
Dim RowCount As Long
Dom ColCount As long

RowCount = Ref.Rows.Count
ColCount = Ref.Columns.Count

For y = 2 To RowCount
If Ref(y, 1) = RowValue Then
For x = 2 To ColCount
If Ref(1, x) = ColumnValue Then
TableSum = TableSum + Ref(y, x)
End If
Next x
End If
Next y
End Function

Best Regards,
Fredrik

"Fredrik Wahlgren" wrote in message
...

"Brian" wrote in message
...

snip[

Two things are immediately obvious to me

2) The declaration Dim x, y As Long will declare one of the variables as
long, the other as variant. I can't remember if it's the first or the
second. Anyway, you should use this kind of declaration

x is variant