Looping by Month: Custom Functions

This is the second in a series of articles about looping code one month at a time.

Writing a loop that iterates one month at a time is a subtly tricky problem. The difficulty isn't so much in writing the code itself. The challenge is to write the code in such a way that your intent is clear to the reader.

Using custom functions to loop by month

The second approach we'll explore is using a couple of custom functions to facilitate looping by month.

MonthVal()
MValPart()

The MonthVal() Function

The first function, MonthVal(), simply converts a date into a long integer that represents the number of months from a base year and month. This is analogous to how VBA stores dates internally.

In VBA, dates are stored as the number of days since December 30, 1899. This is what allows the following code to work:

Dim ThisDay As Date
For ThisDay = #12/30/2020# To #1/3/2021#
    Debug.Print ThisDay
Next ThisDay

We can write a simple function that does the same thing with months. Our function will convert a date to the number of months from some base year and month. Here's the function, along with some doc tests:

'Module-level constant
Private Const BaseYear As Integer = 1900

'Returns the number of months since BaseYear
'>>> MonthVal(#1/15/1900#)
' 1
'>>> MonthVal(#12/31/1900#)
' 12
'>>> MonthVal(#3/16/2014#)
' 1371
Function MonthVal(AsOfDate As Date) As Long
    MonthVal = (Year(AsOfDate) - BaseYear) * MonthsInYear + _
               (Month(AsOfDate))
End Function

Looping by month with MonthVal()

With this function, we can now loop by month for a given start date and end date:

Dim ThisMonth As Long
For ThisMonth = MonthVal(#11/1/2020#) To MonthVal(#2/1/2021#)
    Debug.Print ThisMonth
Next ThisMonth

If we run the above code, we get the following output:

That's not particularly useful. What we need is a way to reverse engineer that number to get back to the month and year whence we came.

But what should such a function return? The first day of the month that the value represents? The last day? The month and year?

How about, "All of the above"?

The MValPart() Function

This function takes a month value as calculated by the MonthVal function and returns a custom type. Here's the definition of the custom type:

Public Type dtMonthValParts
    Year As Integer
    FiscalYear As Integer
    Period As Integer
    Month As Integer
    MonthStart As Date
    MonthEnd As Date
    LastBusDay As Date
    LastBusOrSatDay As Date
End Type

You could easily expand or contract the above custom type to suit your own particular tastes.

Here's the definition of the MValPart() function:

' Returns date information for a MonthVal as created above.
Function MValPart(MonthVal As Long) As dtMonthValParts
    With MValPart
        .Year = (MonthVal - 1) \ MonthsInYear + BaseYear
        .Month = ((MonthVal - 1) Mod MonthsInYear) + 1
        .MonthStart = DateSerial(.Year, .Month, 1)
        .MonthEnd = DateSerial(.Year, .Month + 1, 0)
    End With
End Function

Looping by month with MValPart()

Let's rewrite our loop using the additional MValPart() function:

Dim ThisMonth As Long
For ThisMonth = MonthVal(#11/1/2020#) To MonthVal(#2/1/2021#)
    With MValPart(ThisMonth)
        Debug.Print .MonthStart, .Month, .Year, .MonthEnd
    End With
Next ThisMonth

Here's what that output looks like:

11/1/2020      11            2020         11/30/2020 
12/1/2020      12            2020         12/31/2020 
1/1/2021       1             2021         1/31/2021 
2/1/2021       2             2021         2/28/2021

Now that's something we can actually use!

Image by Alexandra from Pixabay