Looping by Month: DateSerial

This is the first 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 DateSerial()

The first approach we'll explore is using the VBA DateSerial() function to facilitate looping by month.

The simplest case

Let's say we want to output the month and year for each month from January 2021 to March 2021.  Using DateSerial(), we could write the code like this:

Dim ThisMonth As Long
For ThisMonth = 1 To 3
    Debug.Print Format(DateSerial(2021, ThisMonth, 1), "mmmm yyyy")
Next ThisMonth

Multiple years

The above case is simple because we were able to hard-code the year.  But what if the range of months spans multiple years?  

It turns out this isn't that difficult to do because of the following property of the DateSerial() function:

When any argument exceeds the accepted range for that argument, it increments to the next larger unit as appropriate.

For example, let's output the months between November 2020 and February 2021. We can hard-code the year as 2020 and start the month number at 11.  What happens when we call DateSerial(2020, 13, 1)?  It simply returns January 1, 2021.  Taking advantage of this behavior, we can write the following code:

Dim ThisMonth As Long
For ThisMonth = 11 To 14
    Debug.Print Format(DateSerial(2020, ThisMonth, 1), "mmmm yyyy")
Next ThisMonth

Arbitrary starting and ending months

The above cases demonstrate the concepts, but they aren't very practical.  You can't usually get away with hardcoding the starting and ending years and months.  Normally, you will accept the starting and ending months as inputs to a function.

There are two ways you can pass the starting and ending months: by month/year or by date.  For example:

Sub LoopMonths(StartMonth As Byte, StartYear As Integer, _
               EndMonth As Byte, EndYear As Integer)

Sub LoopMonths(StartDate As Date, EndDate As Date)

One could argue that the first approach is more explicit if the day of the month is truly immaterial.  For brevity's sake, though, I almost always go with the second approach.  All my examples in this series will use the second approach of calling the routine by date.

ForMonthsDateSerial()

Here's the final code for a routine uses DateSerial() to loop by month:

Sub ForMonthsDateSerial(StartDate As Date, EndDate As Date)
    Dim StartMonth As Long: StartMonth = Month(StartDate)
    Dim StartYear As Long: StartYear = Year(StartDate)
    
    Dim MonthCount As Long: MonthCount = DateDiff("m", StartDate, EndDate)
    
    Dim ThisMonth As Long
    For ThisMonth = StartMonth To (StartMonth + MonthCount)
        Debug.Print Format(DateSerial(StartYear, ThisMonth, 1), "mmmm yyyy")
    Next ThisMonth
End Sub

Honestly, this turned out more readable than I thought it would.  The code is relatively straightforward and it uses all built-in VBA functions.  There is real value in that, especially if the code will be maintained by a revolving door of programmers.

On the downside, it feels overly verbose.  We had to declare and initialize three variables (StartMonth, StartYear, MonthCount) whose only purpose was to make the looping more readable.  

And lest you're tempted to argue for leaving those variables out to make the code less verbose, here's what that routine would look like:

Sub ForMonthsDateSerialCompact(StartDate As Date, EndDate As Date)
    Dim ThisMonth As Long
    For ThisMonth = Month(StartDate) To (Month(StartDate) + DateDiff("m", StartDate, EndDate))
        Debug.Print Format(DateSerial(Year(StartDate), ThisMonth, 1), "mmmm yyyy")
    Next ThisMonth
End Sub

The above code is functionally equivalent to the original routine, but I find it a nightmare to read.  I'll take excessively verbose over needlessly compact any day.  After all, I'm not some masochistic Perl programmer.

By the way, the differences in these two routines make for a nice mini case study on the advantages of writing self-documenting code.

What's Next

In future articles, I'll explore other approaches to solving the problem of looping by months, including:

• Using a custom MonthVal() function
• Using a Year/Months lookup table

Stay tuned!

Image by Gerd Altmann from Pixabay