Consulting

Results 1 to 3 of 3

Thread: Solved: using a worksheet function in vba

  1. #1

    Solved: using a worksheet function in vba

    hello,

    I am trying to get the number of week days between two date.
    it seems I am using application.networkdays wrongly.


    can someone tell me what's wrong with this tiny bit of code please?

    [vba]

    Call Constants

    Dim Start_Date As Variant, End_Date As Variant
    Dim Total_Days As Double

    Dim i As Integer

    Set Start_Date = wsRC_Simple.Cells(19, 1)
    Set End_Date = wsRC_Simple.Cells(19, 2)

    'Set objexcel = CreateObject("Excel.Application")

    Total_Days = Application.networkdays(Start_Date, End_Date)

    For i = 1 To Total_Days
    wsOutput.Cells(i, 1) = i
    Next[/vba]

  2. #2
    ok, I'll auto reply to myself. maybe it will help some beginners too someday

    networkdays is an Analysis Toolpack Function.
    you need to select the ATP in Excel / Addins
    THEN: make a REFERENCE in your vba code to ATPBAEN.xls (go to "Tools/ References")

    after that it is possible to call the function as a normal excel function

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You can also use Application.Run

    [vba]

    Total_days = Application.Run("'atpvbaen.xla'!Networkdays", Start_date, End_date)
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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