Need A formula in VBA ,Similar to NETWORKDAYS in excel

[jophy]

I would like to get only working days after excluding satuday and sunda
and holidays. Holidays will be there in the range of sheet

There is one formula in xls (NETWORKDAYS) , It would be appreciated i
you can make it similar one. The format of NETWORKDAYS is
NETWORKDAYS(STARTDATE,ENDDATE,HOLIDAY-RANGE)

Regards
Joph

 

[Ron Rosenfeld]

I would like to get only working days after excluding satuday and sunday
and holidays. Holidays will be there in the range of sheet

There is one formula in xls (NETWORKDAYS) , It would be appreciated if
you can make it similar one. The format of NETWORKDAYS is
NETWORKDAYS(STARTDATE,ENDDATE,HOLIDAY-RANGE)

Regards
Jophy

Well, you could use NETWORKDAYS in VBA by referencing the Analysis Tool Pak.


--ron

 

[jophy]

Ron,
Thanks for youimmediate reply. I have tried to use networkday
in VB but it is giving error as "Not supported this function" eve
after installing add-in .. it would great if you can ellaborate on thi
,how to use ,what would be the syntax in VB to use networkdays??

 

[Norman Harker]

Hi Jophy!

Here's a useless UDF that uses it:

Function DaysDiff(StartDay As Date, EndDay As Date, Hols As Range)
DaysDiff = networkdays(StartDay, EndDay, Hols)
End Function

I can then use:

=daysdiff(A1,B1,MyHolidays)

But before you can use the Analysis ToolPak functions in VBA you need
to do two things:

1. In Excel as opposed to the VBE you must Install Analysis ToolPak -
VBA using the Tools > Addins route
2. For the VBA project you need to use:
Tools > References
Put checks against ATPVBAEN.XLA and FUNCRES.XLA

You'll then see these listed under References in the Project Explorer
window.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Ron Rosenfeld]

Ron,
Thanks for youimmediate reply. I have tried to use networkdays
in VB but it is giving error as "Not supported this function" even
after installing add-in .. it would great if you can ellaborate on this
,how to use ,what would be the syntax in VB to use networkdays???

In the VB editor, you select, from the main menu bar: Tools/References and
then select atpvbaen.xls from the list. That is the analysis toolpak.

Once you have done this, then, in the module, you can use networkdays as any
other built-in vb function.

You can tell you did it correctly if the arguments pop up when you insert the
function.

For example:

Nwd = NETWORKDAYS(START_DATE, END_DATE, HOLIDAYS)


--ron

 

[jophy]

Thanks Verymcu

 

[Dana DeLouis]

Just be aware of the huge bug Microsoft left in the atpvbaen.xls. This is
for Excel xp, and I'm afraid, Excel 2003. When one has a vba library
reference to "Tools/References and then atpvbaen.xls, any calls to a
function writes debug statements to the immediate window. (More importantly,
also custom functions). This renders your own Debug.Print statements
useless, as they will be scrolled out of view. Additionally, this will slow
your programs down by a factor of between 20-40.

A simple program like this..

nwDays = Networkdays(#1/1/2004#, Date)

writes the following to the immediate window:

[GetMacroRegId] 'NETWORKDAYS' <
[GetMacroRegId] 'NETWORKDAYS' -> '823263288' >

Just thought I would mention this as it is very confusing the first time you
see it. It's not you. It's the program. This may not be a real problem
with just 1 call, but on a large engineering sheet, this is a problem. It
is my understanding that Microsoft may be looking into this. Let's hope!
:>)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

 

[Norman Harker]

Hi Dana!

I've reported the bug and have received a confirmation that Microsoft
are (at last!) looking into it.

It's been around since Excel 2002 and hopefully the next patch or
service Release will fix it as it appears to be only a case of
removing their debug statements.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Just be aware of the huge bug Microsoft left in the atpvbaen.xls. This is
for Excel xp, and I'm afraid, Excel 2003. When one has a vba library
reference to "Tools/References and then atpvbaen.xls, any calls to a
function writes debug statements to the immediate window. (More importantly,
also custom functions). This renders your own Debug.Print statements
useless, as they will be scrolled out of view. Additionally, this will slow
your programs down by a factor of between 20-40.

A simple program like this..

nwDays = Networkdays(#1/1/2004#, Date)

writes the following to the immediate window:

[GetMacroRegId] 'NETWORKDAYS' <
[GetMacroRegId] 'NETWORKDAYS' -> '823263288' >

Just thought I would mention this as it is very confusing the first time you
see it. It's not you. It's the program. This may not be a real problem
with just 1 call, but on a large engineering sheet, this is a problem. It
is my understanding that Microsoft may be looking into this. Let's hope!
:>)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =

Thanks Verymcuh

Message posted

 

[edvsauer]

Just use any function from the surface by "Application.WorksheetFunction.FnName()"
It works like this I guess (Is the result ok?):
Sub test()
Dim starting As Long, ending As Long 'A date is allways number
Dim holydays(1 To 5) As Long 'those 5 too...
Dim workdays As Double 'function returns double.
starting = #12/22/2008# 'start of period
ending = Date 'end of period
holydays(1) = #8/23/2009# 'workdays you are off for holyday or anything special...
holydays(2) = #8/24/2009#
holydays(3) = #8/25/2009#
holydays(4) = #8/26/2009#
workdays = Application.WorksheetFunction.NetworkDays(starting, ending, holydays)
MsgBox workdays 'The result
End Sub