Application.Volatile Method

[nepotist]

Hello,
Below is one of my macros for a projects. As you can see that I am passing two arguments passing through the function. This function uses the row number referenced by the first argument and then pulls values from different columns in that row and then based on the time period of analysis (second argument) it does the respective Vlookup.

When I change the data in the row(not the argument cell) which are used by the function. it wouldn't update the values unless I set Application.volatile = true and which also slows down by the workbook. I have about 12 sheets in the same workbook that use three more functions like this.

After reading a bit about Application.Volatile method, It calculated the entire sheet when ever there is a change in any cell. I figured that should I pass every cell value used in this function as an argument in order to avoid the use of volatile method, which defeats my purpose of writing the macro. Can some one please help me out how I could make this work other than re-writing the macro with all the arguments.



[vba]Function Service_Volume(rn As Range, YPeriod As String) As Long

'Macro written by V.V.
'Yperiod is the analysis Term (E Existing,M Mid-Term,L Long-Term)

Dim TPeriod As String
Dim F_T As String
Dim Lookup_Rn As Range
Dim Column_Lookup As Integer
Dim LOS_Standard As String
Dim i As Integer

On Error GoTo Line1

i = rn.Row

Set Lookup_Rn = Sheets("GSV_Database").Range("C:V")


'temporary refernce to the Time period of analysis needs to be updated

TPeriod = ActiveSheet.Cells(4, 33) ' Time period of analysis

F_T = Facility(rn, YPeriod)

'Check for LOS standard with respect to the analysis term

If (YPeriod = "E") Then
LOS_Standard = Cells(i, 18)
ElseIf (YPeriod = "M") Then
LOS_Standard = Cells(i, 36)
ElseIf (YPeriod = "L") Then
LOS_Standard = Cells(i, 43)
End If

'Service volume lookup with respect to the time period of analysis

Select Case TPeriod

Case "DAILY":

If LOS_Standard = "A" Then
Column_Lookup = 4
ElseIf LOS_Standard = "B" Then
Column_Lookup = 5
ElseIf LOS_Standard = "C" Then
Column_Lookup = 6
ElseIf LOS_Standard = "D" Then
Column_Lookup = 7
ElseIf LOS_Standard = "E" Then
Column_Lookup = 8
End If

Service_Volume = WorksheetFunction.VLookup(F_T, Lookup_Rn, Column_Lookup, False)

Case "PEAK HOUR TWO-WAY":

If LOS_Standard = "A" Then
Column_Lookup = 10
ElseIf LOS_Standard = "B" Then
Column_Lookup = 11
ElseIf LOS_Standard = "C" Then
Column_Lookup = 12
ElseIf LOS_Standard = "D" Then
Column_Lookup = 13
ElseIf LOS_Standard = "E" Then
Column_Lookup = 14
End If

Service_Volume = WorksheetFunction.VLookup(F_T, Lookup_Rn, Column_Lookup, False)

Case "PEAK HOUR PEAK DIRECTION":

If LOS_Standard = "A" Then
Column_Lookup = 16
ElseIf LOS_Standard = "B" Then
Column_Lookup = 17
ElseIf LOS_Standard = "C" Then
Column_Lookup = 18
ElseIf LOS_Standard = "D" Then
Column_Lookup = 19
ElseIf LOS_Standard = "E" Then
Column_Lookup = 20
End If

Service_Volume = WorksheetFunction.VLookup(F_T, Lookup_Rn, Column_Lookup, False)

End Select
Exit Function
Line1:
MsgBox Error.Description
End Function

Function Class(rn As Range) As String
'Marco used to determine the class of roadway segment V.V

Dim Cl As Variant
Dim i As Integer
i = rn.Row
Dim At As String
At = Cells(i, 17) ' Arterial type (Highway, Freeway, Arterial..)
Cl = Cells(i, 22) ' Signal Density of the roadway segment

If (At = "F") Then
Class = "F"
ElseIf (Cl < 2) And Cl <> 0 Then Class = 1
ElseIf (Cl >= 2 And Cl <= 4.5) Then Class = 2
ElseIf Cl = 0 Then
Class = 0
Else: Class = 3
End If

End Function

Function Facility_Type(rn As Range) As String
' MACRO used to determine the facility type of the roadwaysegment V.V


Dim i As Integer
i = rn.Row

Dim Area As String
Area = Cells(i, 16) ' Existing area type , the area type remains constant irrepective of the analysis period
Dim Fac_Type As String
Dim Clas As String
Clas = Class(rn)

If Clas = "F" Then
Fac_Type = "FW"
ElseIf (Area = "UA" Or Area = "TA") And Clas <> 0 Then
Fac_Type = "S2WAC" & Clas
ElseIf (Area = "UA" Or Area = "TA") And Clas = 0 Then
Fac_Type = "UFH"
ElseIf (Area = "RUA" Or Area = "RDA") And Clas <> 0 Then
Fac_Type = "IFH"
ElseIf (Area = "RUA" Or Area = "RDA") And Clas = 0 Then
Fac_Type = "UFH"
End If

Facility_Type = Fac_Type

End Function

Function Facility(rn As Range, Period As String) As String
' MACRO used to determine the facility Code to lookup Service Volumes of the roadwaysegment V.V

Dim i As Integer
Dim Area As String ' Area Type
Dim Fac_Type As String ' Facility type parameter
Dim One_Two As String ' one way two parameter
Dim ELanes As Integer
Dim MLanes As Integer
Dim LLanes As Integer
Dim DU As String ' divided undivided parameter
Dim Left As String ' left turn lanes parameter
Dim right As String ' right turn lanes parameter

i = rn.Row
Fac_Type = Facility_Type(rn)
Area = Cells(i, 16).Value
One_Two = Cells(i, 25).Value

DU = Cells(i, 24).Value

If (DU = "1W") Then
DU = "U"
Else: DU = DU
End If



Select Case Period

Case "E":
'Existing Period of analysis
ELanes = Cells(i, 28).Value 'Existing number of lanes

'Check for One way facility
' If (DU = "1W") Then
' DU = "U"
' End If

'Check for DU with lanes more than two
If ((ELanes >= 2) And One_Two <> "1W" And DU = "D") Then
Left = "WL"
Else
Left = Cells(i, 26).Value 'exisitng left turn lanes information
End If

right = Cells(i, 27).Value ' existing right turn lanes information


If Fac_Type = "FW" Then
Facility = Area & "_" & Fac_Type & "_" & ELanes & "L_" & right
Else
Facility = Area & "_" & Fac_Type & "_" & One_Two & "_" & ELanes & "L_" & DU & "_" & Left & "_" & right

End If

Case "M":

ELanes = Cells(i, 28).Value ' existing lane information
MLanes = Cells(i, 35).Value ' E+C (Mid-Term) lane information

'Check for DU with lanes more than existing conditions
If ((MLanes - ELanes) <> 0 And DU <> "1W" And DU <> "D") Then
DU = "D"
End If

'Check for Left turn parameter if the future number of lanes in more than two and is not one way street, by default the left
'turn lanes should be considered
If ((MLanes >= 2) And One_Two <> "1W" And DU = "D") Then
Left = "WL"
Else
Left = Cells(i, 38).Value ' Future Left Turn bay information
End If

right = Cells(i, 39).Value ' Future Right Turn bay information

'Facility code for E+C conditions
If Fac_Type = "FW" Then
Facility = Area & "_" & Fac_Type & "_" & MLanes & "L_" & right
Else
Facility = Area & "_" & Fac_Type & "_" & One_Two & "_" & MLanes & "L_" & DU & "_" & Left & "_" & right

End If

Case "L":
ELanes = Cells(i, 28).Value ' Existing number of Lanes
LLanes = Cells(i, 42).Value ' Long range number of lanes


'Check for DU with lanes more than existing conditions
If ((LLanes - ELanes) >= 2 And DU <> "1W" And DU <> "D") Then
DU = "D"
End If

If ((LLanes >= 2) And One_Two <> "1W" And DU = "D") Then
Left = "WL"
Else
Left = Cells(i, 45).Value ' Future Left Turn bay information
End If

right = Cells(i, 46).Value ' Future Right Turn bay information


'Facitlity code for Long term
If Fac_Type = "FW" Then
Facility = Area & "_" & Fac_Type & "_" & LLanes & "L_" & right
Else
Facility = Area & "_" & Fac_Type & "_" & One_Two & "_" & LLanes & "L_" & DU & "_" & Left & "_" & right

End If

End Select

End Function
[/vba]

Thank you

[mikerickson]

For this first function, making Lookup_Rn an argument should make volatility unnecessary.
[VBA]Function Service_Volume(rn As Range, YPeriod As String, Lookup_Rn As Range) As Long

'Macro written by V.V.
'Yperiod is the analysis Term (E Existing,M Mid-Term,L Long-Term)

Dim TPeriod As String
Dim F_T As String
' Dim Lookup_Rn As Range: Rem remove
Dim Column_Lookup As Integer
Dim LOS_Standard As String
Dim i As Integer

On Error Goto Line1

i = rn.Row

' Set Lookup_Rn = Sheets("GSV_Database").Range("C:V"): Rem pass as argument instead [/VBA]

I'm also suspicious of [VBA]TPeriod = ActiveSheet.Cells(4, 33)[/VBA]It might raise volatility issues, TPeriod should also be passed as an argument.

[nepotist]

Thank you for responding. I haven't changed the lookup range in the service-volume function but did ass the Tperiod as a arugment. The problem still exists because if you look at the last function posted above it uses about five values from the row passed as argument. I intend to avoid passing these five values as an argument. Though I find it to be the only suggestion (Which I might end up doing).

If I am going this route, I would like the user to know the order of the arguments similar to the way the functions excel comes with.

Could you please help me guide how to come up with the tool tip for my functions. That would the great.

Thank you

[mikerickson]

"I intend to avoid passing these five values as an argument."

That is the wrong way to go if you want to avoid Application.Volatile

Consider the two functions:[VBA]Function oneFtn() As Variant
oneFtn = Sheet1.Range("A1").Value
End Function

Function twoFtn(aRange as Range) As Variant
twoFtn = aRange.Cells(1, 1).Value
End Function[/VBA]

If the user puts the formula =oneFtn() in a cell it will return the value of Sheet1!A1. If the user then changes A1, the UDF will not recalculate (since none of its precedents have changed) and the value will be misleading, unless it is made a volatile function.

On the other hand, the formula =twoFtn(Sheet1!A1) will recalculate properly every time that the contents of A1 is changed. twoFtn does not need to be volatile.

To make a volatile function non-volatile, MORE arguments are needed, not fewer arguments.

[nepotist]

I have started to realize that and making changes to my code. Can you help me figure out the tooltip that will help the user to input the arguments in correct order. I tried to provide description to my function but that doesn't work.

Thank you

[mikerickson]

Descriptive argument names are a good start to informing the user of what an argument is for.

[VBA]Function myFtn(rng As Range, num As Double)[/VBA] is poorly named

[VBA]Function myFtn(range_of_terms As Range, value_to_add as Double)[/VBA] is better.

[nepotist]

Hi, I updated all the functions to avoid the volatile method and works Fine. And as you mentioned all my arguments are well defined which is self explanatory.

One of the UDF has about nine arguments and I would like to have tooltip to pop up when the user is using the UDF to explain the order of the arguments. Some thing like when you use a built in excel function.

Example:
Id the user is using the VLOOKUP function in the cell, after typing =vlookup( the user gets to see the order of arguments like lookup_value,table_array....

I would like to provide similar information to the user who is using my UDF. Hope I explained it well.

I would appreciate is one of you guys can help me with this.

One way I found is for the user to press Ctrl+Shift+A once they type the open parenthesis to see the arguments. I would like to be generated automatically.
Thank you for your patience.

[mikerickson]

I don't know how to do that.

[Kenneth Hobs]

To do the next best thing, I play this Sub and tell the user to use the Fx button if they don't know how to use the UDF.

[VBA]Sub AddDesc_DisRangeCountIf()
Application.MacroOptions Macro:="DisRangeCountIf", _
Description:="Used like built-in function,=CountIf" & vbCrLf _
& "DisRange: Contiguous or Discontiguous range." _
& vbCrLf & "sCriteria: A string for the criteria.", _
Category:="Kens Functions"
End Sub

Function DisRangeCountIf(ByVal DisRange As Range, ByVal sCriteria As String)
Dim dblCount As Double
dblCount = 0
For Each cell In DisRange.Cells
dblCount = dblCount + WorksheetFunction.CountIf(cell, sCriteria)
Next
DisRangeCountIf = dblCount
End Function[/VBA]

If you want to pursue a more robust solution, see: http://www.eng-tips.com/viewthread.cfm?qid=79365&page=9 or http://xcell05.free.fr/english/index.html