Calculating Relevance with a User Defined Function

Here’s the code to properly calculate the relevance of a todo list item:

Function Relevance(ByRef pStatus As String, ByRef pdLastUpdate As Date, ByRef pdDue As Date) As String
'calculates the relevance of an item based on the parameters
'possible results:
' - old (done and hasn't been touched for x days - constant ncUnchangedForDays)
' - future (open and due after the next y days - constant ncDueInDays
'           or someday)
' - current (open and due in the next y days - constant ncDueInDays
'           or closed with the last x days - constant ncUnchangedForDays)
Const ncDueInDays = 7
Const ncUnchangedForDays = 3

Select Case pStatus
   Case ToDo.scStatusClosed
       If pdLastUpdate < Now() - ncUnchangedForDays Then
           Relevance = scRelevanceOld
       Else
           Relevance = scRelevanceCurrent
       End If
   Case ToDo.scStatusSomeday
       Relevance = scRelevanceFuture
   Case Else 'at least "open"
       If pdDue < Now() + ncDueInDays Then
           Relevance = scRelevanceCurrent
       Else
           Relevance = scRelevanceFuture
       End If
End Select
End Function

This started as a simple Excel “If” formula, but that quickly got too complicated and very tricky to understand, so I switched to a UDF (user defined function). In order for the UDF to be available, it has to be defined in a module. Then you have the regular function wizard available:

It’s important to properly name the parameters, I have not yet found out a way to provide more detailed help to the wizard or to properly format the parameter values.

The code used the following constants to define status and relevance values:

Public Const scStatusOpen = "open"
Public Const scStatusClosed = "done"
Public Const scStatusWaiting = "waiting"
Public Const scStatusSomeday = "someday"

Public Const scRelevanceCurrent = "current"
Public Const scRelevanceOld = "old"
Public Const scRelevanceFuture = "future"

This makes it easier to change the language of the sheet (like I had to do for this blog post), and it also makes it easier to think about what needs to be done if you want to add some more detailed status or relevance values. Next: Updating the AutoFilter.