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