worksheetfunction with networkdays

[Bill Roberts]

My simple line of code in a macro is
maxdays=application.worksheetfunction.networkdays(32790,31765,0)
Every variation gives some error code, but the most common is "Error code
438. Object doesn't support this property or method."
Other worksheet functions work just fine, and networkdays works just fine
when I use it to enter the number in a cell. It just won't work in a macro.
TIA

 

[Ron Rosenfeld]

My simple line of code in a macro is
maxdays=application.worksheetfunction.networkdays(32790,31765,0)
Every variation gives some error code, but the most common is "Error code
438. Object doesn't support this property or method."
Other worksheet functions work just fine, and networkdays works just fine
when I use it to enter the number in a cell. It just won't work in a macro.
TIA

Prior to Excel 2007, NetWorkdays was not a worksheet function but was part of
the analysis toolpak.

I have 2007, so can't check it out, but possibly if you set a reference to
atpvbaen.xls you may be able to use that function.

See http://www.cpearson.com/excel/ATP.htm for further info.
--ron

 

[Roger Govier]

Hi Bill

Tools>Addins>check both Analysis Toolpak and Analysis Toolpak - VBA

 

[Bill Roberts]

Sorry I didn't get back sooner, but I guess I didn't check "notify me of
replies"
I am running Excel 2000 (as a part of Office 2000). Both atlvbaen and
funcres are called out as addins and as references, but I can't seem to get
any command to work. Just get that or similar error message. Would you
recommend I just update to Excel2007??

 

[Roger Govier]

Hi Bill

I wouldn't do that - just yet!!!
Have you also checked atpvbaen.xls in Tools>References in the VB Editor?

If, after doing that as well, it doesn't work, try
maxdays = [atpvbaen.xls].networkdays(32790, 31765, 0)

That information came from Chip Pearson's site following the reference that
Ron gave in his posting.
--
Regards
Roger Govier

Sorry I didn't get back sooner, but I guess I didn't check "notify me of
replies"
I am running Excel 2000 (as a part of Office 2000). Both atlvbaen and
funcres are called out as addins and as references, but I can't seem to
get
any command to work. Just get that or similar error message. Would you
recommend I just update to Excel2007??

 

[Bill Roberts]

I am a happy camper. This code works OK (or seems to).
maxdays = 0
(I just pick an empty cell anywhere)
range("p4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(37547,now(),0)"
maxdays = ActiveCell.Value

This code doesn't:
maxdays=application.worksheetfunction.networkdays(37547,31765,0)
Thanks very much for the help. Somehow, checking the box "notify me of
replies" doesn't seem to take????. I have checked both aptvbaen and funcres
everywhere I can find them.
--
Bill Roberts

Hi Bill

I wouldn't do that - just yet!!!
Have you also checked atpvbaen.xls in Tools>References in the VB Editor?

If, after doing that as well, it doesn't work, try
maxdays = [atpvbaen.xls].networkdays(32790, 31765, 0)

That information came from Chip Pearson's site following the reference that
Ron gave in his posting.
--
Regards
Roger Govier

Sorry I didn't get back sooner, but I guess I didn't check "notify me of
replies"
I am running Excel 2000 (as a part of Office 2000). Both atlvbaen and
funcres are called out as addins and as references, but I can't seem to
get
any command to work. Just get that or similar error message. Would you
recommend I just update to Excel2007??

 

[Dave Peterson]

=Networkdays() isn't part of the application worksheet's function (for xl2003
and below).

So you can either add a reference:
Inside the VBE with your project active
Tools|References|check atpvbaen.xls
Then use:
maxdays=networkdays(37547,31765)
or
MsgBox networkdays(DateSerial(2002, 10, 18), DateSerial(1986, 12, 19))
or
msgbox networkdays(date,dateserial(1986,12,19)

=======
If you didn't want the reference, you could use:

MsgBox Application.Run("atpvbaen.xla!networkdays", _
DateSerial(2002, 10, 18), DateSerial(1986, 12, 19))

or

Dim myFormula As String
myFormula = "networkdays(date(2002, 10, 18),Date(1986, 12, 19))"
MsgBox Application.Evaluate(myFormula)

But you still have to have that analysis toolpak-VBA checked in excel.

I am a happy camper. This code works OK (or seems to).
maxdays = 0
(I just pick an empty cell anywhere)
range("p4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(37547,now(),0)"
maxdays = ActiveCell.Value

This code doesn't:
maxdays=application.worksheetfunction.networkdays(37547,31765,0)
Thanks very much for the help. Somehow, checking the box "notify me of
replies" doesn't seem to take????. I have checked both aptvbaen and funcres
everywhere I can find them.
--
Bill Roberts

Hi Bill

I wouldn't do that - just yet!!!
Have you also checked atpvbaen.xls in Tools>References in the VB Editor?

If, after doing that as well, it doesn't work, try
maxdays = [atpvbaen.xls].networkdays(32790, 31765, 0)

That information came from Chip Pearson's site following the reference that
Ron gave in his posting.
--
Regards
Roger Govier

Sorry I didn't get back sooner, but I guess I didn't check "notify me of
replies"
I am running Excel 2000 (as a part of Office 2000). Both atlvbaen and
funcres are called out as addins and as references, but I can't seem to
get
any command to work. Just get that or similar error message. Would you
recommend I just update to Excel2007??
--
Bill Roberts


:

Hi Bill

Tools>Addins>check both Analysis Toolpak and Analysis Toolpak - VBA

--
Regards
Roger Govier

My simple line of code in a macro is
maxdays=application.worksheetfunction.networkdays(32790,31765,0)
Every variation gives some error code, but the most common is "Error
code
438. Object doesn't support this property or method."
Other worksheet functions work just fine, and networkdays works just
fine
when I use it to enter the number in a cell. It just won't work in a
macro.
TIA

 

[Dave Peterson]

Just to correct this statement:

But you still have to have that analysis toolpak-VBA checked in excel.

=============
You need the analysis toolpak-vba loaded for the application.run version and the
tools|reference version.

You need the analysis toolpak (not the VBA) for the application.evaluate
version.

=Networkdays() isn't part of the application worksheet's function (for xl2003
and below).

So you can either add a reference:
Inside the VBE with your project active
Tools|References|check atpvbaen.xls
Then use:
maxdays=networkdays(37547,31765)
or
MsgBox networkdays(DateSerial(2002, 10, 18), DateSerial(1986, 12, 19))
or
msgbox networkdays(date,dateserial(1986,12,19)

=======
If you didn't want the reference, you could use:

MsgBox Application.Run("atpvbaen.xla!networkdays", _
DateSerial(2002, 10, 18), DateSerial(1986, 12, 19))

or

Dim myFormula As String
myFormula = "networkdays(date(2002, 10, 18),Date(1986, 12, 19))"
MsgBox Application.Evaluate(myFormula)

But you still have to have that analysis toolpak-VBA checked in excel.

I am a happy camper. This code works OK (or seems to).
maxdays = 0
(I just pick an empty cell anywhere)
range("p4").Select
ActiveCell.FormulaR1C1 = "=NETWORKDAYS(37547,now(),0)"
maxdays = ActiveCell.Value

This code doesn't:
maxdays=application.worksheetfunction.networkdays(37547,31765,0)
Thanks very much for the help. Somehow, checking the box "notify me of
replies" doesn't seem to take????. I have checked both aptvbaen and funcres
everywhere I can find them.
--
Bill Roberts

Hi Bill

I wouldn't do that - just yet!!!
Have you also checked atpvbaen.xls in Tools>References in the VB Editor?

If, after doing that as well, it doesn't work, try
maxdays = [atpvbaen.xls].networkdays(32790, 31765, 0)

That information came from Chip Pearson's site following the reference that
Ron gave in his posting.
--
Regards
Roger Govier

Sorry I didn't get back sooner, but I guess I didn't check "notify me of
replies"
I am running Excel 2000 (as a part of Office 2000). Both atlvbaen and
funcres are called out as addins and as references, but I can't seem to
get
any command to work. Just get that or similar error message. Would you
recommend I just update to Excel2007??
--
Bill Roberts


:

Hi Bill

Tools>Addins>check both Analysis Toolpak and Analysis Toolpak - VBA

--
Regards
Roger Govier

My simple line of code in a macro is
maxdays=application.worksheetfunction.networkdays(32790,31765,0)
Every variation gives some error code, but the most common is "Error
code
438. Object doesn't support this property or method."
Other worksheet functions work just fine, and networkdays works just
fine
when I use it to enter the number in a cell. It just won't work in a
macro.
TIA