> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Looping by Month: Custom Functions
- URL: https://nolongerset.com/looping-by-month-custom-functions/
- Published: 2021-02-23T00:49:26.000Z
- Updated: 2026-05-08T13:06:14.000Z
- Description: By using a couple of custom functions, we can write very readable loops that iterate one month at a time.
- Author: Mike Wolfe
- Tags: Code Library, #Date Functions, #Import 2026-05-20 02:59

This is the second in a [series of articles](https://nolongerset.com/sticky-wicket-looping-by-month/) 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](https://docs.microsoft.com/en-us/office/troubleshoot/access/store-calculate-compare-datetime-data). 

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

```vba
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](https://nolongerset.com/python-inspired-doc-tests-in-vba/):

```vba
'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:

```vba
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:

```vba
 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:

```vba
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:

```vba
' 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:

```vba
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:

```vba
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](https://pixabay.com/users/alexas%5Ffotos-686414/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3109304) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=3109304)*