I have a UDF that has three inputs, the function works and calculates correctly. However, upon reopening the file, all I see are #value in all the cells that use this formula. If I double click the cell to edit it and just hit enter, the cell and only that cell updates correctly. If I then drag that cell over the rest of the cells using that function, they all update.
I then added application.volatile to the UDF and they all update after I double click and hit enter. However, once I change one of the referenced ranges, they all go back to #value until I double click on one of the cells and hit enter.
Function score(player1, hole, Optional player2 As String)
Application.Volatile
Dim playDate As String
Dim score1 As Integer
Dim score2 As Integer
Dim playerA, playerB As Range
Call checkSub(player1)
If player2 = "" Then
score = 0
Exit Function
End If
Call checkSub(player2)
'Select Table =to players name
Set playerA = Range(player1)
Set playerB = Range(player2)
playDate = ActiveSheet.Range("B2").Value
score1 = Application.WorksheetFunction.VLookup(playDate, playerA, 25 + hole)
score2 = Application.WorksheetFunction.VLookup(playDate, playerB, 25 + hole)
If score1 < score2 Then
score = 1
ElseIf score1 = score2 Then
score = 0.5
Else
score = 0
End If
End Function
Sub checkSub(ByRef player)
Dim subs As String
subs = Application.WorksheetFunction.VLookup(player, ActiveSheet.Range("A5:B17"), 2, False)
If subs <> "" Then
player = subs
End If
End Sub
Bookmarks