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/Writin...ionsInVBA.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" <(E-Mail Removed)> wrote in message
news:60EFB0CE-6EA9-46B7-9246-(E-Mail Removed)...
> 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
>
>
Similar Threads
