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.
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
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:
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.
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.
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