# Looping by Month: Custom Functions

By using a couple of custom functions, we can write very readable loops that iterate one month at a time.

This is the second 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 custom functions to loop by month

The second approach we'll explore is using a couple of custom functions to facilitate looping by month.

• MonthVal()
• MValPart()

## The MonthVal() Function

The first function, MonthVal(), simply converts a date into a long integer that represents the number of months from a base year and month.  This is analogous to how VBA stores dates internally.

In VBA, dates are stored as the number of days since December 30, 1899.  This is what allows the following code to work:

``````Dim ThisDay As Date
For ThisDay = #12/30/2020# To #1/3/2021#
Debug.Print ThisDay
Next ThisDay``````

We can write a simple function that does the same thing with months.  Our function will convert a date to the number of months from some base year and month.  Here's the function, along with some doc tests:

``````'Module-level constant
Private Const BaseYear As Integer = 1900

'Returns the number of months since BaseYear
'>>> MonthVal(#1/15/1900#)
' 1
'>>> MonthVal(#12/31/1900#)
' 12
'>>> MonthVal(#3/16/2014#)
' 1371
Function MonthVal(AsOfDate As Date) As Long
MonthVal = (Year(AsOfDate) - BaseYear) * MonthsInYear + _
(Month(AsOfDate))
End Function``````

### Looping by month with MonthVal()

With this function, we can now loop by month for a given start date and end date:

``````Dim ThisMonth As Long
For ThisMonth = MonthVal(#11/1/2020#) To MonthVal(#2/1/2021#)
Debug.Print ThisMonth
Next ThisMonth``````

If we run the above code, we get the following output:

`````` 1451
1452
1453
1454 ``````

That's not particularly useful.  What we need is a way to reverse engineer that number to get back to the month and year whence we came.

But what should such a function return?  The first day of the month that the value represents?  The last day?  The month and year?

How about, "All of the above"?

## The MValPart() Function

This function takes a month value as calculated by the MonthVal function and returns a custom type.  Here's the definition of the custom type:

``````Public Type dtMonthValParts
Year As Integer
FiscalYear As Integer
Period As Integer
Month As Integer
MonthStart As Date
MonthEnd As Date
LastBusDay As Date
LastBusOrSatDay As Date
End Type``````

You could easily expand or contract the above custom type to suit your own particular tastes.

Here's the definition of the MValPart() function:

``````' Returns date information for a MonthVal as created above.
Function MValPart(MonthVal As Long) As dtMonthValParts
With MValPart
.Year = (MonthVal - 1) \ MonthsInYear + BaseYear
.Month = ((MonthVal - 1) Mod MonthsInYear) + 1
.MonthStart = DateSerial(.Year, .Month, 1)
.MonthEnd = DateSerial(.Year, .Month + 1, 0)
End With
End Function``````

### Looping by month with MValPart()

Let's rewrite our loop using the additional MValPart() function:

``````Dim ThisMonth As Long
For ThisMonth = MonthVal(#11/1/2020#) To MonthVal(#2/1/2021#)
With MValPart(ThisMonth)
Debug.Print .MonthStart, .Month, .Year, .MonthEnd
End With
Next ThisMonth``````

Here's what that output looks like:

``````11/1/2020      11            2020         11/30/2020
12/1/2020      12            2020         12/31/2020
1/1/2021       1             2021         1/31/2021
2/1/2021       2             2021         2/28/2021 ``````

Now that's something we can actually use!

Image by Alexandra  from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0