Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function & Application.volatile
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function & Application.volatile
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function & Application.volatile
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 "Chip Pearson" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Volatile not working | Excel Programming | |||
Application.Volatile | Excel Programming | |||
application.volatile activation | Excel Programming | |||
Need for Application.Volatile? | Excel Programming | |||
Application.Volatile messing up other function | Excel Programming |