Custom Function & Application.volatile

[Clare]

Hi, I'm new to Excel vba. Could someone help to answer my questions on custom
functions and Application.Volatile ?

I hava custom function which looks something like below. It uses
theApplication.Volatile statement. My question is: With this code, if this
function are in two workbooks with identical structure (i.e. SAME worksheet
names, SAME columns for Grade, Criteria , but different values), are the
calculations safely isolated to each workbook ?

Function GRADECALC(rngGrade, strCriteria, rngScore)

Application.Volatile

Dim Results, GradeCount, ScoreSum
Dim rngR As Range
Dim rngS As Range

Set rngR = Range(rngGrade, rngGrade.End(xlDown))
Set rngS = Range(rngScore, rngScore.End(xlDown))

ScoreSum = Application.SumIf(rngR, strCriteria, rngS)

GradeCount = Application.CountIf(rngR, strCriteria)


If GradeCount = 0 Then
Results = "No Grade " & strCriteria & " found."
Else


If (GradeCount > 50) Then

Results = (ScoreSum / GradeCount) * 0.75

Else

Results = ScoreSum / GradeCount

End If

End If

GRADECALC = Results


End Function

Another thing, I saw a post by Niek Otten that says "if not all your data
comes into the function via the argument list, cells may still be calculated
in the wrong order".

Would this be applicable to the custom function above ? I have some doubts,
because the function gets the last cell and sets the ranges within the
function itself....

Thanks to anyone who can set me straight on this..
I'm using Excel 2003 on Win XP Pro btw.

Rgds,
Clare

 

[Chip Pearson]

Clare,

User defined functions (UDF) calculate in isolation. The function is called
by Excel and runs to completion to return the result (unless the function is
called recursively -- see
http://www.cpearson.com/excel/RecursiveProgramming.aspx for an explanation
of recursive programming). Each call to a UDF executes completely
independently of any other call, so unless you are using Static variables in
the UDF all the variables in the UDF are reset to their default for each
call to the function. You don't need to worry about one call to a UDF
"colliding" with any other call.

Application.Volatile indicates whether the UDF is to be called *every* time
a calculation is made, regardless of whether the UDF really needs to be
calculated. A True value specifies that the UDF is to be calculated on every
calculation. A value of False indicates that the UDF should be calculated
only when one of its precedent cells is changed. Using a True value for
Application.Volatile can cause performance degradation since the function
is calculated even if it doesn't need to be calculated.

Another thing, I saw a post by Niek Otten that says "if not all your data
comes into the function via the argument list, cells may still be
calculated
in the wrong order".

Would this be applicable to the custom function above ? I have some
doubts,
because the function gets the last cell and sets the ranges within the
function itself....

You don't need to worry this. Since the variables are declared within the
procedure, they are destroyed when the UDF returns and are reset to the
default values on the next call to the UDF. What Niek was referring is the
practice of using an explicit range reference within the UDF code. For
example,

Function FFF() As Double
FFF = Range("A1").Value * 2
End Function

This is a very badly design function. It depends on A1 and should be
calculated whenever A1 is changed. However, Excel has no idea that this
function uses A1, and will not recalculate the function if A1 changes. The
better function would be

Function FFF(WhatCell As Range) As Double
FFF = WhatCell.Value * 2
End Function

In this function, the formula that calls it would pass A1 to the UDF. Excel
will recognize that this cell uses A1 and will therefore recalc the UDF when
A1 is changed.

You might take a quick look at
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx for some additional
information about writing UDFs in VBA.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Clare]

Hi Chip,

Thanks for the explanation & resource link.

For my own use the number of rows is arbitrary from one workbook to the
next, so I wrote this function to take the first row for Grade & Score as
arguments while the last rows are obtained within the function itself. I
noticed that when a cell value is changed for Grade in a cell somewhere
between the first and last row, the custom function won't recalculate itself
without Application.volatile. This makes sense after reading your explanation
since the function arguments i.e. the first row, didn't change, so a
recalculation would not be triggered........

I'm wondering if there is a way to get around this without using
Application.volatile ?

Thank you for your help!

Rgds,
Clare