The Handicap Function

It’s time to write the first formula for my golf league program. I started with a function to compute the handicap because it was at the bottom of my sketch. I knew that this program was going to be primarily UDF’s that filled worksheets. Because of that, I need to be aware of speed throughout the development. I start by establishing an ADO connection to my database and keeping that connection live as long as the workbook is open. This is the contents of my MGlobals module:

Public gadoCon As ADODB.Connection
 
Public Const sQRYPLYRWKSCR As String = “SELECT TblPlayers.Player, TblScores.WeekNum, “ & _
    “[Hole1]+[Hole2]+[Hole3]+[Hole4]+[Hole5]+[Hole6]+[Hole7]+[Hole8]+[Hole9] AS WeekScore, “ & _
    “TblScores.Team FROM TblPlayers INNER JOIN TblScores ON TblPlayers.Player = TblScores.Player”
 
Sub InitGlobals()
   
    Dim sCon As String
   
    sCon = “DSN=MS Access 97 Database;”
    sCon = sCon & “DBQ=C:DickGolfLeagueTenthHole.mdb;”
    sCon = sCon & “DefaultDir=C:DickGolfLeague;DriverId=281;”
    sCon = sCon & “FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”
   
    Set gadoCon = New ADODB.Connection
   
    gadoCon.Open sCon
   
End Sub

This sub is called from the Workbook_Open event to get the connection opening overhead out of the way before the first function is even called.

The logic of the handicap formula is this: Every player has a starting handicap and it’s stored in TblPlayers. The handicap is the average of a player’s most recent three scores times 80%. If there aren’t three scores, the starting handicap is used as one of the scores. A player’s handicap, then, may be the average of his first round and his starting handicap, thus only two “rounds” would be used. The theory behind these rules is that your handicap will reflect how you’re playing now, rather than how you’ve played over a whole season. Here’s the function:

Public Function Handicap(ByVal lPlyr As Long, _
    Optional ByVal lweek As Long = 0) As Long
   
    Dim rsWeekScore As ADODB.Recordset
    Dim sSql As String
    Dim lRecordsProcessed As Long
    Dim dTotalScore As Double
    Dim dAvgScore As Double
    Dim dAvgDiff As Double
    Dim sWeekWhere As String
   
    Const dSCALE As Double = 0.8  ‘only 80% of average scores are used
   Const sWEEKSCORE As String = “WeekScore”
   
    ‘If a week is provided, a where clause will be added to the sql to
   ‘limit the records to only previous weeks
   If lweek > 0 Then
        sWeekWhere = ” AND TblScores.WeekNum < “ & lweek & ” “
    End If
   
    ‘Open the connection if it’s not already
   If gadoCon Is Nothing Then
        InitGlobals
    End If
   
    Set rsWeekScore = New ADODB.Recordset
   
    ‘Limit the records to just the player and optionally the week
   ‘Records are ordered descending by week to use the most current
   ‘scores in the calculation
   sSql = sQRYPLYRWKSCR
    sSql = sSql & ” WHERE (((TblPlayers.Player)=” & lPlyr & “))” & sWeekWhere
    sSql = sSql & “ORDER BY TblScores.WeekNum DESC;”
   
    rsWeekScore.Open sSql, gadoCon
   
    ‘Add up the three most recent scores
   Do While Not rsWeekScore.EOF And lRecordsProcessed < 3
        lRecordsProcessed = lRecordsProcessed + 1
        dTotalScore = dTotalScore + rsWeekScore.Fields(sWEEKSCORE)
        rsWeekScore.MoveNext
    Loop
   
    ‘If there aren’t three scores to add up, include the starting HC
   If lRecordsProcessed < 3 Then
        dAvgScore = (dTotalScore + GetStartingHC(lPlyr)) / (lRecordsProcessed + 1)
    Else
        dAvgScore = dTotalScore / lRecordsProcessed
    End If
   
    Handicap = CLng((dAvgScore – 36) * dSCALE)
   
End Function

I needed to have the optional week argument because I need to compute two different handicaps. The handicap through last week is the handicap you use for this weeks match play. The handicap through this week determines which golfer you play next week (The low handicap golfer from one two-man team plays the low handicap golfer from another).

Each time this function is called I’m creating a new recordset. So much for those speed considerations. I think what I should do is bring in the recordset to a global variable without any where clauses, then iterate through the records picking out what I need. I’m guessing that the additional overhead of looping through the records will be more than offset by savings from not creating a recordset. Time for a little testing.

Posted in Uncategorized

2 thoughts on “The Handicap Function

  1. Let me first explain that I’m not a golfer and know little about the game and that I have little experience of Access.
    I look at the problem in terms of Excel.
    I would set up the handicap table with a column for this week as “Week 0?, last week as “Week -1?, the week before as “Week -2? and the week before that as “Week -3?.
    A new player would then have his handicap placed in Weeks -3 to -1 and this weeks handicap would be the average of the three weeks. X *3 /3 =X.
    Once each week, I would rename the columns (or shift the names right one column) and then delete the first column, so each week a new handicap can be calculated in column “Week 0?.
    That way, there’s no need to take any further account of a new player, as his starting handicap will be shifted off the table and will be deleted after 3 weeks.

    As I said, no interest in golf (“A good walk spoiled”) and not much knowledge of Access, so feel free to disregard this if it’s of no help to you.

    Henry

  2. The description of golf I enjoy is

    “… [the game of] putting a wee ball into a wee hole with tools ill designed for that very purpose.”

    – Jon


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.