Problem using VBA for the Networkdays function

G

Guest

I am using VBA in MS project to enter information into Excel 2003. It
creates an instance and enters various dates. However if I put the
Newworkdays function into a cell (e.g. xlApp.range("D6").value =
"=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I get the
#NAME? error message. The Add-ins for the Analysis toolpak have been ticked.


I tried running code to turn the analysis toolpak off and then on
programatically but still the same error message

Any ideas how this can be fixed?

Many thanks

James
 
N

Niek Otten

Hi James,

Range("D6").FormulaR1C1 = "=networkdays(rc[-3],rc[-2])"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am using VBA in MS project to enter information into Excel 2003. It
| creates an instance and enters various dates. However if I put the
| Newworkdays function into a cell (e.g. xlApp.range("D6").value =
| "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I get the
| #NAME? error message. The Add-ins for the Analysis toolpak have been ticked.
|
|
| I tried running code to turn the analysis toolpak off and then on
| programatically but still the same error message
|
| Any ideas how this can be fixed?
|
| Many thanks
|
| James
 
G

Guest

Sorry Niek doesn't work. In my experience .value and .formular1c1 work the
same

James
 
N

Niek Otten

Be aware that your formula is not referring to D4 and D5, but to A6 and B6. Are any of them #NAME?
I assume that you have Analysis Toolpak ticked in your worksheet Add-ins menu, not (just) VBA

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Sorry Niek doesn't work. In my experience .value and .formular1c1 work the
| same
|
| James
|
| "(e-mail address removed)" wrote:
|
| > I am using VBA in MS project to enter information into Excel 2003. It
| > creates an instance and enters various dates. However if I put the
| > Newworkdays function into a cell (e.g. xlApp.range("D6").value =
| > "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I get the
| > #NAME? error message. The Add-ins for the Analysis toolpak have been ticked.
| >
| >
| > I tried running code to turn the analysis toolpak off and then on
| > programatically but still the same error message
| >
| > Any ideas how this can be fixed?
| >
| > Many thanks
| >
| > James
 
G

Guest

it is D4 and D5. More importantly the function name is not in capitals
implying that XL can't see the function name. The Analysis toolpak has been
ticked in the Add-ins

Try it creating a simple macro in Word - I can give you the code if you want
 
B

Bob Phillips

James,

When you use Excel via automation, the addins do not get loaded, so you get
the error.

Force the load like so


xlApp.AddIns("Analysis ToolPak").Installed = False
xlApp.AddIns("Analysis ToolPak").Installed = True
xlApp.Range("D6").Value = "=networkdays(rc[-3],rc[-2])"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

[email protected] said:
it is D4 and D5. More importantly the function name is not in capitals
implying that XL can't see the function name. The Analysis toolpak has been
ticked in the Add-ins

Try it creating a simple macro in Word - I can give you the code if you want


Niek Otten said:
Be aware that your formula is not referring to D4 and D5, but to A6 and B6. Are any of them #NAME?
I assume that you have Analysis Toolpak ticked in your worksheet Add-ins menu, not (just) VBA

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Sorry Niek doesn't work. In my experience .value and .formular1c1 work the
| same
|
| James
|
| "(e-mail address removed)" wrote:
|
| > I am using VBA in MS project to enter information into Excel 2003. It
| > creates an instance and enters various dates. However if I put the
| > Newworkdays function into a cell (e.g. xlApp.range("D6").value =
| > "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I get the
| > #NAME? error message. The Add-ins for the Analysis toolpak have been ticked.
| >
| >
| > I tried running code to turn the analysis toolpak off and then on
| > programatically but still the same error message
| >
| > Any ideas how this can be fixed?
| >
| > Many thanks
| >
| > James
Click to expand...
Click to expand...
 
N

Niek Otten

Hi James,

And after you've sorted that out, check the cell references in your formula in the worksheet; it IS A6 and B6 (Columns -3 and -2,
not Rows)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| James,
|
| When you use Excel via automation, the addins do not get loaded, so you get
| the error.
|
| Force the load like so
|
|
| xlApp.AddIns("Analysis ToolPak").Installed = False
| xlApp.AddIns("Analysis ToolPak").Installed = True
| xlApp.Range("D6").Value = "=networkdays(rc[-3],rc[-2])"
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "(e-mail address removed)" <[email protected]>
| wrote in message | > it is D4 and D5. More importantly the function name is not in capitals
| > implying that XL can't see the function name. The Analysis toolpak has
| been
| > ticked in the Add-ins
| >
| > Try it creating a simple macro in Word - I can give you the code if you
| want
| >
| >
| > "Niek Otten" wrote:
| >
| > > Be aware that your formula is not referring to D4 and D5, but to A6 and
| B6. Are any of them #NAME?
| > > I assume that you have Analysis Toolpak ticked in your worksheet Add-ins
| menu, not (just) VBA
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > > "(e-mail address removed)" <[email protected]>
| wrote in message
| > > | > > | Sorry Niek doesn't work. In my experience .value and .formular1c1
| work the
| > > | same
| > > |
| > > | James
| > > |
| > > | "(e-mail address removed)" wrote:
| > > |
| > > | > I am using VBA in MS project to enter information into Excel 2003.
| It
| > > | > creates an instance and enters various dates. However if I put the
| > > | > Newworkdays function into a cell (e.g. xlApp.range("D6").value =
| > > | > "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I
| get the
| > > | > #NAME? error message. The Add-ins for the Analysis toolpak have
| been ticked.
| > > | >
| > > | >
| > > | > I tried running code to turn the analysis toolpak off and then on
| > > | > programatically but still the same error message
| > > | >
| > > | > Any ideas how this can be fixed?
| > > | >
| > > | > Many thanks
| > > | >
| > > | > James
| > >
| > >
| > >
|
|
 
B

Bob Phillips

I agree with Niek, it came out as A6,B6 in all my tests.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Niek Otten said:
Hi James,

And after you've sorted that out, check the cell references in your
Click to expand...
formula in the worksheet; it IS A6 and B6 (Columns -3 and -2,
not Rows)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| James,
|
| When you use Excel via automation, the addins do not get loaded, so you get
| the error.
|
| Force the load like so
|
|
| xlApp.AddIns("Analysis ToolPak").Installed = False
| xlApp.AddIns("Analysis ToolPak").Installed = True
| xlApp.Range("D6").Value = "=networkdays(rc[-3],rc[-2])"
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "(e-mail address removed)" <[email protected]>
| wrote in message | > it is D4 and D5. More importantly the function name is not in capitals
| > implying that XL can't see the function name. The Analysis toolpak has
| been
| > ticked in the Add-ins
| >
| > Try it creating a simple macro in Word - I can give you the code if you
| want
| >
| >
| > "Niek Otten" wrote:
| >
| > > Be aware that your formula is not referring to D4 and D5, but to A6 and
| B6. Are any of them #NAME?
| > > I assume that you have Analysis Toolpak ticked in your worksheet Add-ins
| menu, not (just) VBA
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > > "(e-mail address removed)"
Click to expand...
| wrote in message
| > > | > > | Sorry Niek doesn't work. In my experience .value and .formular1c1
| work the
| > > | same
| > > |
| > > | James
| > > |
| > > | "(e-mail address removed)" wrote:
| > > |
| > > | > I am using VBA in MS project to enter information into Excel 2003.
| It
| > > | > creates an instance and enters various dates. However if I put the
| > > | > Newworkdays function into a cell (e.g. xlApp.range("D6").value =
| > > | > "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I
| get the
| > > | > #NAME? error message. The Add-ins for the Analysis toolpak have
| been ticked.
| > > | >
| > > | >
| > > | > I tried running code to turn the analysis toolpak off and then on
| > > | > programatically but still the same error message
| > > | >
| > > | > Any ideas how this can be fixed?
| > > | >
| > > | > Many thanks
| > > | >
| > > | > James
| > >
| > >
| > >
|
|
Click to expand...
 
G

Guest

Bob

The original formula that I had has square brackets which means that it's a
relative to the D6. In this case A6 and B6.
xlApp.range("D6").value ="=networkdays(rc[-3],rc[-2])"

I already tried adding code to clear the addins and then restart them again
but no use.

I think that when you open a new instance of Excel from another MS App the
Addins do not get loaded. In this case I am using MS Project to load Excel.
I have just noticed that when I type in MyApp.worksheetfunction then press
the '.' button for a list of methods/property parameters (?) it doesn't list
networkdays.

Does this make sense and if so can you help?

Many thanks

James


Bob Phillips said:
I agree with Niek, it came out as A6,B6 in all my tests.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Niek Otten said:
Hi James,

And after you've sorted that out, check the cell references in your
Click to expand...
formula in the worksheet; it IS A6 and B6 (Columns -3 and -2,
not Rows)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| James,
|
| When you use Excel via automation, the addins do not get loaded, so you get
| the error.
|
| Force the load like so
|
|
| xlApp.AddIns("Analysis ToolPak").Installed = False
| xlApp.AddIns("Analysis ToolPak").Installed = True
| xlApp.Range("D6").Value = "=networkdays(rc[-3],rc[-2])"
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "(e-mail address removed)" <[email protected]>
| wrote in message | > it is D4 and D5. More importantly the function name is not in capitals
| > implying that XL can't see the function name. The Analysis toolpak has
| been
| > ticked in the Add-ins
| >
| > Try it creating a simple macro in Word - I can give you the code if you
| want
| >
| >
| > "Niek Otten" wrote:
| >
| > > Be aware that your formula is not referring to D4 and D5, but to A6 and
| B6. Are any of them #NAME?
| > > I assume that you have Analysis Toolpak ticked in your worksheet Add-ins
| menu, not (just) VBA
| > >
| > > --
| > > Kind regards,
| > >
| > > Niek Otten
| > > Microsoft MVP - Excel
| > >
| > > "(e-mail address removed)"
Click to expand...
| wrote in message
| > > | > > | Sorry Niek doesn't work. In my experience .value and .formular1c1
| work the
| > > | same
| > > |
| > > | James
| > > |
| > > | "(e-mail address removed)" wrote:
| > > |
| > > | > I am using VBA in MS project to enter information into Excel 2003.
| It
| > > | > creates an instance and enters various dates. However if I put the
| > > | > Newworkdays function into a cell (e.g. xlApp.range("D6").value =
| > > | > "=networkdays(rc[-3],rc[-2])" where D4 and D5 are recognised dates I
| get the
| > > | > #NAME? error message. The Add-ins for the Analysis toolpak have
| been ticked.
| > > | >
| > > | >
| > > | > I tried running code to turn the analysis toolpak off and then on
| > > | > programatically but still the same error message
| > > | >
| > > | > Any ideas how this can be fixed?
| > > | >
| > > | > Many thanks
| > > | >
| > > | > James
| > >
| > >
| > >
|
|
Click to expand...
Click to expand...
 
B

Bob Phillips

[email protected] said:
I think that when you open a new instance of Excel from another MS App the
Addins do not get loaded.
Click to expand...

That ius exactly waht I said, which is why I suggested forcing the load. I
was able to reproduce your error, not from MSProject but from automation,
and by installing the addin, it worked fine.
In this case I am using MS Project to load Excel.
I have just noticed that when I type in MyApp.worksheetfunction then press
the '.' button for a list of methods/property parameters (?) it doesn't list
networkdays.
Click to expand...

Networkdays is not a worksheetfunction, it is part of the Analysis Toolpak,
so you won't see it, even from within excl
 
G

Guest

Bob

I thought I had already closed and added the Add-ins but I somehow screwed
it up. Problem fixed. Sorry for being a arsey on the last email - long day!

Cheers

James
 
B

Bob Phillips

Well, at least you are sorted now.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

using a function in an addin in my vba code 2
NETWORKDAYS alternative, for use without Analysis ToolPak 8
NETWORKDAYS() not avaialbe after reopening 2
Networkdays Function 11
how to use workdays or networkdays in VBA 3
Excel VBA - using yearfrac function in VBA 2
networkdays 5
Verify Analysis ToolPak with VBA 11

Top