Looping by Month: Loop Until with DateAdd

This is the fourth 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.

Reader Submission

One of the things I find endlessly fascinating about programming is the myriad solutions to solving the same problem.  The collective knowledge of the internet makes finding these sorts of alternative solutions easier than ever before.

This fourth method of looping by month was submitted by friend of the blog, Francesco Foti (@francescofoti).  He's got a great Access blog of his own over at http://francescofoti.com/.  Be sure to check it out.

Tried your code (btw: Private Const MonthsInYear As Integer = 12). Nice hack on DateSerial to get the last day in month ;-). Just for fun, here's how I would have done it (quick & dirty, native); harder to read ? (and, apparently, no "mod" on month necessary with DateSerial) pic.twitter.com/otfVQK1Ph1

— francesco (@francescofoti) February 24, 2021

Looping by month using DateAdd() and Loop Until

Here's Francesco's code in copy/paste-able format.  I modified it slightly to accept a StartDate and EndDate parameter:

Sub LoopUntilMonths(StartDate As Date, EndDate As Date)
    Dim vThisDate As Variant
    Dim iYear     As Integer
    Dim iMonth    As Integer
    Dim vMonthEnd As Variant
    
    vThisDate = StartDate
    Do
      iYear = Year(vThisDate)
      iMonth = Month(vThisDate)
      vMonthEnd = DateSerial(iYear, iMonth + 1, 0)
      Debug.Print vThisDate, iMonth, iYear, vMonthEnd
      vThisDate = DateAdd("m", 1, vThisDate)
    Loop Until vThisDate > EndDate

End Sub

I was curious to know how the DateAdd() function would handle incrementing by month from a date on the 31st day of the month when the following month had only 30 days.  It turns out that it if the following month does not have enough days, then the function returns whatever the last day of the month is.

An example makes this clearer:

LoopUntilMonths #10/31/2020#, #3/31/2021#
10/31/2020     10            2020         10/31/2020 
11/30/2020     11            2020         11/30/2020 
12/30/2020     12            2020         12/31/2020 
1/30/2021      1             2021         1/31/2021 
2/28/2021      2             2021         2/28/2021 
3/28/2021      3             2021         3/31/2021 

Readability

Whether you find this approach more readable than some of the others may be as much about personal preference and coding style as anything else.  One thing I do really like about this approach is the way that the StartDate and EndDate lines set off the loop.

I think visual cues are incredibly important when it comes to code readability.  When I read the code above, my mind intuitively groups all the code between the line with StartDate and the line with EndDate and registers that as, "This is all the stuff that happens as we move from the start date to the end date."

Thanks again for engaging, Francesco!

Image by It is not permitted to sell my photos with StockAgencies from Pixabay  (editor's note: That's a weird name for a child, but it's probably real)