Solo712
Registered User.
- Local time
- Today, 05:04
- Joined
- Oct 19, 2012
- Messages
- 838
Hi all,
like Michael, I have a homemade function which I would like folks to evaluate. Sometimes, when I finish my work I feel reasonably certain it is clean code. Sometimes I don't. This would be one instance when I have an inkling it could be done better.
The need for this function seems obvious. Very often in scheduling monthly events the formula says "1st Monday", "2nd Thursday", or "Last Sunday" in the month. I am working presently with Condo managers who set up their meetings with the Board of Directors in that fashion. I looked for such a function but did not find anything. So, I created one from scratch. It works ok but I am curious to see if there is a better solution out there.
The NthWeekday function has three arguments 1) string that formulates the weekday argument, 2) month, 3) year. It returns date.
The first argument expression expects "1st", "2nd" 3rd" "4th" or "Last" concatenated with the name of the weekday (only first three letters are checked i.e. "Thupsday" or "Frivolous" works for Thursday and Friday respectively!). So eg., to find out the 3rd Thursday of this month the arguments would be : NthWeekday("3rdThursday", Month(Date), Year(Date)) and last Sunday NthWeekday("LastSunday", Month(Date), Year(Date)).
Perhaps there is a better way to handle the makeshift condition the occasional 5th occurrence of the weekday in the month. But I could not come up with anything that I liked so I "plugged" the problem with the "Last" label which does not care if it is 4th or 5th occurence. At any rate: I would like to see if there are some other solutions out there. Thanks in advance.
Best,
Jiri
like Michael, I have a homemade function which I would like folks to evaluate. Sometimes, when I finish my work I feel reasonably certain it is clean code. Sometimes I don't. This would be one instance when I have an inkling it could be done better.
The need for this function seems obvious. Very often in scheduling monthly events the formula says "1st Monday", "2nd Thursday", or "Last Sunday" in the month. I am working presently with Condo managers who set up their meetings with the Board of Directors in that fashion. I looked for such a function but did not find anything. So, I created one from scratch. It works ok but I am curious to see if there is a better solution out there.
The NthWeekday function has three arguments 1) string that formulates the weekday argument, 2) month, 3) year. It returns date.
The first argument expression expects "1st", "2nd" 3rd" "4th" or "Last" concatenated with the name of the weekday (only first three letters are checked i.e. "Thupsday" or "Frivolous" works for Thursday and Friday respectively!). So eg., to find out the 3rd Thursday of this month the arguments would be : NthWeekday("3rdThursday", Month(Date), Year(Date)) and last Sunday NthWeekday("LastSunday", Month(Date), Year(Date)).
Perhaps there is a better way to handle the makeshift condition the occasional 5th occurrence of the weekday in the month. But I could not come up with anything that I liked so I "plugged" the problem with the "Last" label which does not care if it is 4th or 5th occurence. At any rate: I would like to see if there are some other solutions out there. Thanks in advance.
Code:
Public Function NthWeekDay(NthWD As String, Wmo As Integer, Wyr As Integer) As Date
Dim tdate As Date, firstorlastday As Integer, generic As Integer, ourwkday As Integer, tb As Integer
Dim wname As String, wpos As Integer
If InStr(UCase(NthWD), "LAST") Then
tb = 0
wname = Mid(NthWD, 5)
tdate = DateAdd("m", 1, DateSerial(Wyr, Wmo, 1)) - 1
ElseIf IsNumeric(Left(NthWD, 1)) Then
tb = CInt(Left(NthWD, 1))
wname = Mid(NthWD, 4)
tdate = DateSerial(Wyr, Wmo, 1)
Else
tb = -1
End If
If tb > -1 And tb < 5 Then
wpos = InStr("SUNMONTUEWEDTHUFRISAT", UCase(Left(wname, 3)))
firstorlastday = Weekday(tdate)
If wpos <> 0 Then
generic = 1 + (wpos - 1) / 3
If tb > 0 Then
If generic < firstorlastday Then generic = generic + 7
ourwkday = generic - firstorlastday + 1
ourwkday = ourwkday + (tb - 1) * 7
tdate = DateSerial(Year(tdate), Month(tdate), ourwkday)
Else
If generic > firstorlastday Then firstorlastday = firstorlastday + 7
ourwkday = Day(tdate) - firstorlastday + generic
tdate = DateSerial(Year(tdate), Month(tdate), ourwkday)
End If
Else
tdate = 0
End If
Else
tdate = 0
End If
NthWeekDay = tdate
End FunctionBest,
Jiri
if not those who you've asked to take a look at it for you 
It has happened to me on a number of occasions that I could not figure out later why I made such and such a comment to a code that was simple to read and had purpose other than the comment indicated. 
