+ Reply to Thread
Results 1 to 4 of 4

VBA version of "Networkdays"?

  1. #1
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    87

    VBA version of "Networkdays"?

    Hi folks,

    I'm looping through an array, making various checks of the data, and for this particular part I'm looking at a date, and finding out how many days before another date it was (not including weekends).

    I'm wondering if I'm forced to use "application.worksheetfucntion.networkdays", or if there is a vba equivilent?

    I'm led to believe that doing this across several thousand rows of data is likely to slow the code...

  2. #2
    Registered User
    Join Date
    02-05-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    365
    Posts
    87

    Re: VBA version of "Networkdays"?

    Actually, I think i have a solution in this case. The "48 hours previous" date in this report is static, and I've simply set it as a variable once, at the start of the code, then I can do a compare. However, my question stands, is there a way to work this out in code, without having to reference the worksheet functions each time?

    future reports might not work with a static date like in this case so it would be handy.

  3. #3
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: VBA version of "Networkdays"?

    This UDF MyNetworkdays() perform 15x faster than Excel builtin NetworkDays() function :
    (0.14 seconds vs 2.29 seconds)

    Please Login or Register  to view this content.
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA version of "Networkdays"?

    On my 64 bit Dell XPS with an i7 10th generation chip I can detect no discernible difference in speed.

    I'm therefore surprised since it's always been my understanding that the slowest part of any VBA functionality is the reading and writing stuff from the Excel App to VBA and then back to Excel.

    I'm also surprised since if this is true it means the designers of the Networkdays function have not been as efficient in using the interface to the underlying machine code, which is presumably the same for the Excel function and VBA.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 02-11-2021, 12:52 PM
  2. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] Copy "csv-excel-sheet" to "xlsm-excel-workbook" in Excel2013 version.
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-03-2013, 10:26 AM
  5. Replies: 18
    Last Post: 09-06-2005, 07:05 PM
  6. Modify the official None-working days in the "NETWORKDAYS" Functio
    By Zewer in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1