using the WorkDay function in VBA

[lwchapman]

I have studied the MS help file for the "workday" function. However, I am
still mystified as to how one codes this function and the proper syntax.

I would like to prevent a procedure from executing if it is a weekend or
holiday.

I use Excel in Office 2007.

All suggestions or comments are very welcomed, Larry

P.S. I thank both Jim Rech and Peter T for their valuable help in a
previous question of mine regarding a "time" frame. Both suggestions worked
perfectly. I used Jim's because it was so succinct.

 

[JMB]

Since workday does not appear to be a member of the worksheetfunction class
in vba (XL 2000 anyway), I had to use Evaluate to get it working. Also, you
have to create a list of dates that are holidays. In my example it is on a
sheet called Data in A1:A4.

Sub test()
Dim lngToday As Long
Dim rngHolidays As Range

lngToday = CLng(Date)
Set rngHolidays = Sheets("Data").Range("A1:A4")

If CLng(Evaluate("Workday(" & lngToday - 1 & ", 1," & _
rngHolidays.Address(, , , True) & ")")) = lngToday Then
MsgBox "It is a workday"
Else
MsgBox "It is not a workday"
End If

End Sub


Note that workday requires the analysis toolpak to be installed. You could
ensure it is installed with:
AddIns("Analysis ToolPak").Installed = True

 

[Ron Rosenfeld]

I have studied the MS help file for the "workday" function. However, I am
still mystified as to how one codes this function and the proper syntax.

I would like to prevent a procedure from executing if it is a weekend or
holiday.

I use Excel in Office 2007.

All suggestions or comments are very welcomed, Larry

P.S. I thank both Jim Rech and Peter T for their valuable help in a
previous question of mine regarding a "time" frame. Both suggestions worked
perfectly. I used Jim's because it was so succinct.

Pre 2007, WORKDAY is in the Analysis Tool Pak. To use it in VBA, set a
reference (Tools/References) to atpvbaen.xls

If, for some reason, that is not possible or allowed, you could use this
substitute:

================================================
Function WD(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing) As Date

'Workday function without Analysis Toolpak

Dim i As Long
Dim TempDate As Date
Dim c As Range
Dim Stp As Integer

Stp = Sgn(NumDays)
TempDate = StartDate
For i = Stp To NumDays Step Stp
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSaturday Then _
TempDate = TempDate + Stp - (Stp > 0)
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then
Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False
If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False Then
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSaturday Then _
TempDate = TempDate + Stp - (Stp > 0)
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i

WD = TempDate
End Function
=======================================================
--ron