Slow Execution of User-Defined Function

[David]

I'm new to VBA scripting in Excel and just wrote my first
user-defined function. The function below works fine but
is slow. It takes about 30 seconds to calculate the sum of
the absolute value of a range consisting of 300 rows in a
single column (i.e. 300 cells). Is there something
fundamental about user-defined functions that makes them
terribly slow or am I not doing something that is killing
my performance? The Excel built-in worksheet functions are
fast!

I'm running Excel 97 SR-2.

The function is referenced in a worksheet cell
as, "=amplitude(A1-style-ref:A1-style-ref)".

Thanks, Thanks, Thanks!
(e-mail address removed)
'**********************************************************
Function amplitude(myRange)
' Returns the average of the absolute value of cells
in "myRange".

Dim iRow, iCol
Dim firstCol, lastCol
Dim firstRow, lastRow
Dim sum, myCount
Dim mySheetName

firstCol = myRange.Column
lastCol = myRange.Columns(myRange.Columns.Count).Column
firstRow = myRange.Row
lastRow = myRange.Rows(myRange.Rows.Count).Row
mySheetName = Application.Caller.Parent.Name

sum = 0
iCol = firstCol
Do
iRow = firstRow
Do
sum = sum + Abs(Worksheets(mySheetName).Cells(iRow,
iCol))
iRow = iRow + 1
Loop While iRow <= lastRow

iCol = iCol + 1
Loop While iCol <= lastCol

myCount = myRange.Count
amplitude = sum / myCount
End Function

 

[Frank Kabel]

Hi David
a question upfront: Why are you not using worksheet functions do do
this. e.g. to get the sum of the absolutes for a range use the array
formula (entered with CTRL+SHIFT+ENTER):
=SUM(ABS(range))
or the non array formula
=SUMPRODUCT(ABS(range))

and to get the average use
=SUMPRODUCT(ABS(range))/SUMPRODUCT(--(ISNUMBER(range)))

But if you still want to use a UDF you can either call the approbiate
worksheet functions from within (e.g.
application.worksheetfunction.sumproduct) or you may change your loop
to the following (for a range of 1000 cells calculates instantless):

------
Function amplitude(myRange As Range) As Double
' Returns the average of the absolute value of cells in "myRange".
Dim cell As Range
Dim sum As Double
Dim myCount
Dim wks As Worksheet
sum = 0
myCount = 0

For Each cell In myRange
If cell.Value <> "" Then
sum = sum + Abs(cell.Value)
myCount = myCount + 1
End If
Next
amplitude = sum / myCount
End Function

 

[Tom Ogilvy]

Looping through cells can be slower.

Unless this is just something you are doing to practice, you could use

=Average(Abs(A1-style-ref))

entered with Ctrl+Shift+Enter rather than just enter since this is an array
formula.


see if this is faster

Function AmplitudeTom(myRange)
' Returns the average of the absolute value of cells
' in "myRange".
Dim varr As Variant
varr = myRange.Value
For i = 1 To UBound(varr, 1)
For j = 1 To UBound(varr, 2)
If IsNumeric(varr(i, j)) Then
tot = tot + Abs(varr(i, j))
cnt = cnt + 1
End If
Next
Next
If cnt > 0 Then
AmplitudeTom = tot / cnt
Else
AmplitudeTom = 0
End If
End Function


It "seemed" slightly faster, but both your routine and mine were pretty much
instantaneous working on 10283 cells in a single column. Does your sheet
have a lot of volatile functions in it like Rand or Now?

 

[David]

OK, right, I should be using an array formula in this
case. For more general problems, the ability to define
functions is valuable to me, so I investigated further.
Keeping in mind your suggestion that other things on the
worksheet could affect the speed of the user-defined
function, I found out the following facts.
1. My range cells contain formulae that reference back
through several levels of "antecedent" cells. If I do a
copy and "paste special - values" and operate on cells
with only data rather than formula it doesn't seem to
matter how the "amplitude" calculation is done, it's
always fast (1-2 seconds).
2. Using the array formula it's fast (1-2 seconds) even
operating on the cells with formulae.
3. Using amplitudeTom on the cells with formulae, the
calculation time is reduced from 30 seconds for my
original implementation to 4 seconds!! Aha!
Based on this I conclude the following.
1. Array formulae are the way to go where applicable.
2. In user defined functions, it's more efficient to first
read the data into a local array (like you did) rather
than to make successive accesses to cells back on the
source worksheet (like I did).
3. The fact that everything works fast on cells that don't
contain formulae doesn't help me much. That's because,
when precedent cells change, it's too cumbersome to
require the copying of the new data to cells that are the
input data to a user function. It's insightful, but I
can't use this approach.

Thanks for the instruction Tom. Please correct me if any
of my thoughts above are wrong.
- Regards, David -

 

[David]

Frank,

I agree that an array formula is the right solution to
this problem. I need the power of user-defined functions
so I investigated further. See my response to the post by
Tom for details of what I learned. Using your function in
place of mine reduced the calculation time from 30 seconds
to 12 seconds.

In general it seems I'd like to access the cells using row
and column indices and I'm not sure how to do that with
your approach.

Thanks so much for your kind and effective help!
- Regards, David -

 

[Frank Kabel]

Hi David
Tom uses a different approach (and it seems to be faster by the way for
your specific needs). So there is no 'combination' of both approaches
.
But you may try the following for your function:
add the line
Application.Calculation = xlCalculationManual
at the beginning of your function and the line
Application.Calculation = xlCalculationAutomatic
at the end of your function and test again. This may help (not tested
though)