User Defined Function Error

[nepotist]

I a few user defined function that I am using in my spreadsheet and they all work fine. For some reason when I reopen the workbook a few cells have error in them , When I double click and hit enter they are fixed.
I was wondering How do I prevent this from happening. I have check all my functions argument they are values which should return a value and not error "#VALUE". Can someone please help me figure this out.
Thank you

[macropod]

If you want help, you should post the errant UDFs and any auto macros that run when the workbook is opened. Without that, any 'advice' would be mere speculation.

[nepotist]

I have plenty of Fucntions, but here is one that is painful to fix



Function wtavg(X As Range, Y As Range)

Dim i As Integer
Dim Product As Double
Dim Sum As Double
Dim UpValue As Double
Dim DnValue As Double

UpValue = 0
DnValue = 0

For i = 1 To X.Count Step 1

Product = (X.Cells(i, 1) * Y.Cells(i, 1))

UpValue = UpValue + Product

'MsgBox (UpValue)
Sum = Y.Cells(i, 1)

DnValue = DnValue + Sum

wtavg = UpValue / DnValue
Next i

End Function

Function Service_Volume(Area As Range, Class As Range, DU As Range, O_T As Range, LeftTurn As Range, RightTurn As Range, Lanes As Range, LOS_Standard As Range, TPeriod As Range, Lookup_Rn As Range) As Long


'Yperiod is the analysis Term (E Existing,M Mid-Term,L Long-Term)

'Dim Tperiod As String
Dim F_T As String
Dim Column_Lookup As Integer

Application.Calculation = xlCalculationManual



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


F_T = Facility(Area, Class, DU, O_T, LeftTurn, RightTurn, Lanes)

'Check for LOS standard with respect to the analysis term

'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


End Function


Function Facility(Area As Range, Class As Range, DU As Range, O_T As Range, ByRef LeftTurn As Range, RightTurn As Range, Lanes As Range) As String
' MACRO used to determine the facility Code to lookup Service Volumes of the roadwaysegment

Dim Fac_Type As String ' Facility type parameter
Dim L As String

If (DU = "D" And LeftTurn.Value = "0L") Then
L = "WL"
Else
L = LeftTurn.Value
End If


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



If Fac_Type = "FW" Then
Facility = Area & "_" & Fac_Type & "_" & Lanes & "L_" & RightTurn
Else
Facility = Area & "_" & Fac_Type & "_" & O_T & "_" & Lanes & "L_" & DU & "_" & L & "_" & RightTurn
End If


End Function

Function LOS(Area As Range, Class As Range, DU As Range, O_T As Range, LeftTurn As Range, RightTurn As Range, Lanes As Range, Volume As Range, TPeriod As String, Lookup_Rn As Range) As String

'Yperiod is the analysis Term (E,M,L)
'Rn is the volume of the analysis year *********


Dim F_T As String

F_T = Facility(Area, Class, DU, O_T, LeftTurn, RightTurn, Lanes)

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

Select Case TPeriod

Case "DAILY":

If (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 4, False)) Then
LOS = "A"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 5, False)) Then
LOS = "B"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 6, False)) Then
LOS = "C"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 7, False)) Then
LOS = "D"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 8, False)) Then
LOS = "E"
Else
LOS = "F"
End If


Case "PEAK HOUR TWO-WAY":

If (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 10, False)) Then
LOS = "A"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 11, False)) Then
LOS = "B"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 12, False)) Then
LOS = "C"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 13, False)) Then
LOS = "D"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 14, False)) Then
LOS = "E"
Else
LOS = "F"
End If

Case "PEAK HOUR PEAK DIRECTION":

If (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 16, False)) Then
LOS = "A"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 17, False)) Then
LOS = "B"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 18, False)) Then
LOS = "C"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 19, False)) Then
LOS = "D"
ElseIf (Volume <= WorksheetFunction.VLookup(F_T, Lookup_Rn, 20, False)) Then
LOS = "E"
Else
LOS = "F"
End If

End Select
Exit Function

End Function
Function Future_K(Area As Range, Class As Range, LookUp_Range As Range) As Single


Dim Lstring As String
Dim Fac_Type As String

Fac_Type = Facility_Type(Area, Class)

Lstring = Area & "_" & Fac_Type

Future_K = WorksheetFunction.VLookup(Lstring, LookUp_Range, 2, False)



End Function

Function Future_D(Area As Range, Class As Range, LookUp_Range As Range) As Single


Dim Lstring As String
Dim Fac_Type As String
Fac_Type = Facility_Type(Area, Class)

Lstring = Area & "_" & Fac_Type

Future_D = WorksheetFunction.VLookup(Lstring, LookUp_Range, 3, False)


End Function

Function Facility_Type(Area As Range, Class As Range)

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

End Function



Here are all my UDF functions that I am using. I am more frustrated with the "wtavg" because these value/function is used for intermediate rows. while the other functions are used in all the row, I can easily get read of the errors when I change the value in the main cell use a reference for all the UDF except "wtavg".

Thank you

[Bob Phillips]

The workbook would be more helpful.

[nepotist]

Xld, It is hard to format the workbook to send it to you guys. There are about 17 sheets interrelated.
I have tried to use the openworkbook event and Application.calculateFull. It fix all the errors and I then deleted this event and closed and reopend my workbook a few time there were no errors. I wonder what I did to get this fixed, As I deleted the workbookopen event.

[Bob Phillips]

Can you not create a cut-down version that shows the problem?

[nepotist]

Unfortunately No xld :( . Though this issue is resolved could you think of a a reason that would likely cause the problem.