Functions

FormatYearWeekDay

This function formats a date difference value into years, weeks and days.

Function FormatYearWeekDay(theNumber As Integer) as String
'Formats a date difference value as years, weeks and days
'Assumes 365 days in a year
Dim Yr As String
Dim Wk As String
Dim Dy As String
Dim Neg as Boolean
Yr = ""
Wk = ""
Dy = ""
'Test if the number is zero
If theNumber = 0 Then
FormatYearWeekDay = "0d"
Exit Function
End If
'Test if the number is positive or negative
If theNumber < 0 Then
Neg = True
Else
Neg = False
End If
'Setting the absolute value means we don't have to worry
theNumber = Abs(theNumber)
'Deal with years
If theNumber >= 365 Then
Yr = Int(theNumber / 365) & "y "
theNumber = theNumber Mod 365
End If
'Deal with weeks
If theNumber >= 7 Then
Wk = Int(theNumber / 7) & "w "
theNumber = theNumber Mod 7
End If
'Deal with days
Dy = theNumber & "d"
'Set the function's return value
If Neg Then
FormatYearWeekDay = "-" & Yr & Wk & Dy
Else
FormatYearWeekDay = Yr & Wk & Dy
End If
End Function

IsActiveCellInTableColumn

This function returns TRUE if the active cell is in a specified table column and FALSE otherwise.

Function IsActiveCellInTableColumn(theTable As String, theColumn As String) As Boolean
If Not Intersect(ActiveCell, ActiveSheet.Range(theTable & “[" & theColumn & "]“)) Is Nothing Then
IsActiveCellInTableColumn = True
Else
IsActiveCellInTableColumn = False
End If
End Function