# Looping by Month: Loop Until with DateAdd

Reader Francesco Foti writes in with his own solution to the Looping by Month challenge.

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.

### 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
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 ``````