Results 1 to 5 of 5

Thread: [RESOLVED] [Excel] WorksheetFunction.NetworkDays in 2003

  1. #1

    Thread Starter
    Frenzied Member agmorgan's Avatar
    Join Date
    Dec 2000
    Location
    Lurking
    Posts
    1,383

    Resolved [RESOLVED] [Excel] WorksheetFunction.NetworkDays in 2003

    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?

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: [Excel] WorksheetFunction.NetworkDays in 2003

    Try this

    Code:
    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Frenzied Member agmorgan's Avatar
    Join Date
    Dec 2000
    Location
    Lurking
    Posts
    1,383

    Re: [Excel] WorksheetFunction.NetworkDays in 2003

    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

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: [RESOLVED] [Excel] WorksheetFunction.NetworkDays in 2003

    kool
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  5. #5
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,652

    Re: [RESOLVED] [Excel] WorksheetFunction.NetworkDays in 2003

    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 ?
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width