Programming Efficiency and Days in a Month

Mike Gunderloy

The reason you can’t find any documentation is that there isn’t any such function built into Visual Basic for Applications. But don’t worry: There are plenty of other date functions built into VBA, and it’s pretty easy to create a routine to return the information that you want.

The following function is passed a month number, a year, and a day number to return the number of times that day occurs in the month. The days are numbered the same way as in VBA (Sunday = 1, Monday = 2, and so forth). This lets me use VBA’s built-in constants in my code (vbSunday, vbMonday, and so on). To find the number of Mondays in March 1999, you’d call the routine like this:

  intMondayCount = WDinMonth1(3, 1999, vbMonday)

Here’s the code:

Function WDinMonth1(intMonth As Integer, _
   intYear As Integer, intDay As Integer) _
Dim intDays As Integer
Dim intDaysInMonth As Integer
Dim intResult As Integer

'Find the number of days in the month
intDaysInMonth = _
   DaysInMonth(DateSerial(intYear, intMonth, 1))

For intDays = 1 To intDaysInMonth
'Check every day to see if it's the one requested
  If WeekDay( _
    DateSerial(intYear, intMonth, intDays) _
    , vbSunday) = intDay Then
      intResult = intResult + 1
  End If
Next intDays

WDinMonth1 = intResult

End Function

Function DaysInMonth(dteInput As Date) As Integer
'Find the number of days in the month
Dim intDays As Integer

' Add one month, subtract dates to find difference. 
intDays = DateSerial(Year(dteInput), _
            Month(dteInput) + 1, Day(dteInput)) _
        - DateSerial(Year(dteInput), _
            Month(dteInput), Day(dteInput))

DaysInMonth = intDays

End Function

There are two functions here, and they’re both pretty simple. DaysInMonth calculates the number of days in a given month by using the built-in date functions. This function takes advantage of the fact that VBA treats the 13th month in a year as the first month in the following year, so it works even for the month of December. The DateSerial function, which you might not have used, returns a variant of type Date given a year, month, and day. All the DaysInMonth function does is subtract the first of a month from the first of the following month to determine how many days there are in the month.

The other function, WDinMonth1, uses a brute-force approach to determining how many of a given day are in a given month. Armed with the number of days in the month, it uses DateSerial to generate every day in the month. The built-in WeekDay function determines how many of those days are the specified day of the week. The routine keeps track of this number in the intResult variable, which is later returned as the value of the function.

Well, there are other approaches. You can perform the task using nothing but native VBA date arithmetic functions, for instance. Here’s a second solution that uses a lot less code:

Function WDinMonth2(intMonth As Integer, _
  intYear As Integer, intDay As Integer) _
  As Integer

WDinMonth2 = DateDiff("ww", _
     DateSerial(intYear, intMonth, 1) - 1, _
     DateSerial(intYear, intMonth + 1, 1) - 1, _
     intDay)
    
End Function

As you can see, you can use the built-in DateDiff function to come up with the desired number. However, in most circumstances, I don’t think this is a better answer to the original problem. The problem is that this code is much less maintainable than the original brute-force solution.

Suppose you were a maintenance programmer whose job it is to keep this application up-to-date. Which version of the WDinMonth function would you rather try to maintain? Personally, I’d rather look at the first one, where the flow of code shows you exactly what’s going on, than be faced with the second one, which uses a mysterious calculation. The VBA Help file, by the way, wouldn’t be a lot of help in understanding the second version. Here’s part of what it says about DateDiff:

"When interval is Weekday (‘w’), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week (‘ww’), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn’t count date1, even if it does fall on a Sunday . . . The firstdayofweek argument affects calculations that use the ‘w’ and ‘ww’ interval symbols."

I especially like the use of "affects" in the final sentence of the Help. Sure, changing the inputs affects the answer, but wouldn’t you like to know how it affects them? I wrote the WDinMonth1 function first and used it to test WDinMonth2. I knew that WDinMonth1 was giving me the right answer because the code was so, well, obvious. When WDinMonth2 disagreed with WDinMonth1, I knew the more "elegant" solution was wrong.

If you’ve been working with VBA or Access Basic for any length of time, you’ve probably had it drilled into you that you need to include an error handler in every single function. Frankly, I think that particular rule deserves to be broken at times. There are two questions to ask when you’re writing an error handler: What can go wrong, and what can you do about it?

Take another look at WDinMonth1. What do you suppose could go wrong? You can overflow the inputs, but that’s about it. You can’t mess WDinMonth1 up by feeding in negative years or huge days, because the built-in date functions are already coded to deal with that sort of nonsense (whether they should be so coded is another question entirely).

As to the second question, what could you do about an error if one occurred? Perhaps once in a millennium you might run out of RAM when calling this function, but in that case, there’s not much you can do with an error trap anyhow. As things stand now, the error condition will be raised to the calling function. If you think about what you want to do with an error in a low-level utility function like this one, returning it to the calling program is probably the best possible answer. So, why not go with the default? If you don’t write an error trap, you can’t make any coding mistakes in it.

Fine with me, but how much time are you saving? It’s easy to spend time optimizing parts of an application that don’t really matter. And you can’t, for instance, assume that the version with the least code will run the fastest. The only way to know for sure whether you’re spending your optimization time wisely is to test the results–say, with this function:

Declare Function timeGetTime Lib "winmm.dll" () _
    As Long
Function TestTimes()
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim intResult As Integer
Dim lngTime As Long
    
lngTime = timeGetTime
For intYear = 1989 To 1999
  For intMonth = 1 To 12
   For intDay = 1 To 7
     intResult = _
       WDinMonth1(intYear, intMonth, intDay)
   Next intDay
  Next intMonth
Next intYear
Debug.Print "Version1 " & timeGetTime - lngTime
    
lngTime = timeGetTime
For intYear = 1998 To 1999
  For intMonth = 1 To 12
    For intDay = 1 To 7
      intResult = _
         WDinMonth2(intYear, intMonth, intDay)
    Next intDay
  Next intMonth
Next intYear
Debug.Print "Version2 " & timeGetTime - lngTime
    
End Function

This function uses the timeGetTime Windows API function, which has a resolution of about a millisecond, to compare the time taken by the two versions on 924 iterations of the initial question. These iterations are distributed smoothly around months and days, just in case one version or the other is biased in a particular situation.

The results of running TestTimes on my test machine are pretty dramatic. Version 1 (brute force) takes 220 milliseconds. Version 2 (elegance) takes 10 milliseconds. A 22 to 1 advantage! Surely, this justifies using the harder-to-maintain version, doesn’t it?

No, I don’t think so. Look at those results another way: For every 4,500 calls to WDinMonth, you save about one second using the second version instead of the first. If saving that second is really important to you, you’re coding in the wrong language anyhow, since VBA itself isn’t especially fast. If you need to squeeze single seconds out of that many iterations, you should switch to C++, or perhaps raw assembler.

How many times must this program execute before this routine is called 4,500 times? Will you be saving that second over the course of a year of normal usage? And what’s the value of saving a second of computer time? If this routine adds an extra minute to the time spent maintaining this application, your savings will be eaten up. Actually, your one-second saving was probably eaten up by the extra time it took me to create the "more efficient" version.

In general, the good developer is the one who thinks about these issues. It might not have looked like there was much to the original question (how many Mondays are there in March, anyway?), but like any other question, you can use it as a springboard to consider the quality of your own coding. When faced with this sort of problem, always ask yourself these four questions (notice that the optimization question comes last):

Your applications will benefit from this sort of careful thought.

Download SA9911AA.exe