Looping by Month: Custom Functions
By using a couple of custom functions, we can write very readable loops that iterate one month at a time.
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.
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:
1451 1452 1453 1454
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!