0

Summary: all the occurrences of a UDF recalculate when one of them has a source changed.

I have a fairly simple UDF (code below) that calculates the stableford score of a golf round based on a couple of variables. Now I find that the UDF seems to be semi-volatile, in that as soon as I enter data in the data entry range (HoleScores) ALL of my occurrences of the UDF recalculate, even on other sheets. But if I press F9 (or choose to recalculate) they do not recalculate. The desired situation is that only the UDF for which the data is entered recalculates. Can anybody help me achieve that? nb: the HoleScores range is only referenced by one single UDF. All occurrences of the UDF use unique entry ranges. I have tested the recalc with the VBA screen open and closed. I am using Excel 2016

Public Function WACRondeScore(PlayingHandicap, Pars As Range, _
                             StrokeIndexen As Range, HoleScores As Range, _
                             Afgelast As String) As Variant
    
    On Error GoTo FuncFail
       
    Dim Hole As Long
    Dim StablefordPuntenRonde As Long
    Dim StablefordPunten As Long
    
    If PlayingHandicap = "" Then
        WACRondeScore = ""
        Exit Function
    Else
        PlayingHandicap = CLng(PlayingHandicap)
    End If
    
    ' Afgelast
    If Not Afgelast = "" Then
        WACRondeScore = "A"
        Exit Function
    End If
    
    If IsEmptyRange(HoleScores) Then
        WACRondeScore = ""
        Exit Function
    End If
    
    For Hole = 1 To 9
        If IsInteger(HoleScores(1, Hole)) Then
            StablefordPunten = (Pars(1, Hole) + 2 + Int(((PlayingHandicap * 2) - StrokeIndexen(1, Hole) + 18) / 18)) - HoleScores(1, Hole)
            If StablefordPunten < 0 Then StablefordPunten = 0
            StablefordPuntenRonde = StablefordPuntenRonde + StablefordPunten
        End If
    Next Hole
    
    WACRondeScore = StablefordPuntenRonde
    
    Debug.Print "wacRONDESCORE"
    Exit Function

FuncFail:
    WACRondeScore = CVErr(xlErrValue)
                
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Joost
  • 102
  • 8
  • Got some sample data and expected output? I think one of the if may be the root cause because there are too much exit function – Kin Siang May 29 '21 at 08:47
  • You can make your UDF volatile, meaning, everytime the Worksheet /Workbook is changes, your UDF is recalculated. Put this as the first line of your function: `Application.Volatile` – MGP May 29 '21 at 15:14

1 Answers1

0

I think I have found the cause of the recalculation. One of the entry values (PlayingHandicap) seems to be culprit. Don't know why, as yet, but am searching for the bug

Joost
  • 102
  • 8