[RESOLVED] [Excel] WorksheetFunction.NetworkDays in 2003

[agmorgan]

In 2007 I can use the WorksheetFunction.NetworkDays to find the number of business days between two dates.
However, when the code is run on another PC running 2003 (without the Analysis ToolPak) I get an error.

My users are likely to grumble if I ask them to configure something in Excel before they can run my stuff.
Is there anyway to reference the Analysis Toolpak from VBA if it hasn't been installed?

Alternatively, does anyone have any code that replicates this function so I can bypass the issue?

[Siddharth Rout]

Try this

Function MyNetworkingdays(StartDate As Variant, EndDate As Variant) As Long
    Dim lngCounter As Long, lngCountDays As Long, StandardHolidays()
    Dim ArrMember, boolIsFound As Boolean

    boolIsFound = False

    StartDate = DateValue(StartDate)
    EndDate = DateValue(EndDate)

    '~~> Define Standard holiday list as an array. These are US Federal Holidays
    StandardHolidays = Array("1/1/2007", "1/15/2007", "2/19/2007", _
    "5/28/2007", "7/4/2007", "9/3/2007", "10/8/2007", "11/12/2007", _
    "11/22/2007", "12/25/2007", "1/1/2008", "1/21/2008", "2/18/2008", _
    "5/26/2008", "7/4/2008", "9/1/2008", "10/13/2008", _
    "11/11/2008", "11/27/2008", "12/25/2008")

    For lngCounter = StartDate To EndDate
        If Weekday(lngCounter, vbMonday) < 6 Then
            For Each ArrMember In StandardHolidays
                If Format(ArrMember, "mm/dd/yyyy") = Format(lngCounter, "mm/dd/yyyy") Then
                    boolIsFound = True
                    Exit For
                Else
                    boolIsFound = False
                End If
            Next ArrMember
        
            If boolIsFound = False Then
                lngCountDays = lngCountDays + 1
            End If
        End If
    Next lngCounter
    MyNetworkingdays = lngCountDays
End Function

[agmorgan]

Thanks.
Seems a bit more complete than what I subsequently found on planet source code.
http://www.planet-source-code.com/vb...390&lngWid=-10

[Siddharth Rout]

kool

[NeedSomeAnswers]

The Analysis toolpak will need to be installed to use that function !

Could you not get your sys admin to roll out the change via AD or something ?