How Many Days in That Month?

Although there’s no built-in function to determine the number of days in a specified month, it’s not a difficult task. There are many ways to accomplish this task. You could create a Select Case statement and, knowing the month and the year, look up the length of the month. This requires, of course, knowing the year, because leap years affect February’s length.

An alternative (and it’s the correct alternative, we believe) is to let VBA do as much of any calculation as possible. Because you can subtract one date value from another to determine the number of days between the dates, you can use the DateSerial function to find the first day in the specified month and the first day in the next month and then subtract the first value from the second.

The dhDaysInMonth function, in Listing 2.14, performs the necessary calculations. You send it a date, and it calculates the number of days in the month represented by that date. In this function, as in many others, if you don’t pass a date at all, the function assumes you want to use the current date and finds the number of days in the current month.

Listing 2.14: Calculate the Days in a Given Month

Function dhDaysInMonth(Optional dtmDate As Date = 0) As Integer
    ' Return the number of days in the specified month.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhDaysInMonth = DateSerial(Year(dtmDate), _
     Month(dtmDate) + 1, 1) - _
     DateSerial(Year(dtmDate), Month(dtmDate), 1)
End Function

Although this tip applies to many functions in this chapter, it is key to this particular function. VBA accepts dates in many formats, as you’ve seen. One that we haven’t mentioned is the #mm/yy# format. That is, you can pass just a month and year as a date, and VBA will assume you mean the first of that month. With the dhDaysInMonth function, it’s useful to be able to just send in the month and year portion if you don’t care to handle the day portion as well.

© 1997 by SYBEX Inc. All rights reserved.