#NAME? Error

[Nirmal Singh]

I am using the following code in a COM based application:

excelApp.Cells.Item[2,13]:=EncodeDate(2003,12,8);
excelApp.Cells.Item[2,14]:=EncodeDate(2003,12,10);
excelApp.Cells.Item[2,15]:='=NetWorkDays(M2,N2)';

The first two lines simply insert these dates into cells M2 and N2.
The next line should return the Working days between these dates (in
cell O2).

The procedure executes without any error, but when I open the Excel
Workbook cell O2 contains #NAME?, althogh the formula bar shows the
correct formula. If I then select cell O2 and press ENTER I get the
correct value (3).

I have also tried the following with the same results:

excelApp.Cells.Item[2,15].Select;
excelApp.ActiveCell.Formula:='=NetWorkDays(M2,N2)';

Any suggestions would be appreciated.

Nirmal Singh

[Sharad Naik]

Well "NetWorkDays" is not a excel built-in function so seems
to be your user-defined function.
So where is the function NetWorkDays() located? in an add-in ?
or in the workbook you open?

Where - ever the code for the fuction is located , add line
Application.Volatile at the top in the function code.

Sharad

"Nirmal Singh" <nirmal.singh@towerhamlets.removethisbitgov.uk> wrote in
message news:41dbb5fd.1491046@news.microsoft.com...
>I am using the following code in a COM based application:
>
> excelApp.Cells.Item[2,13]:=EncodeDate(2003,12,8);
> excelApp.Cells.Item[2,14]:=EncodeDate(2003,12,10);
> excelApp.Cells.Item[2,15]:='=NetWorkDays(M2,N2)';
>
> The first two lines simply insert these dates into cells M2 and N2.
> The next line should return the Working days between these dates (in
> cell O2).
>
> The procedure executes without any error, but when I open the Excel
> Workbook cell O2 contains #NAME?, althogh the formula bar shows the
> correct formula. If I then select cell O2 and press ENTER I get the
> correct value (3).
>
> I have also tried the following with the same results:
>
> excelApp.Cells.Item[2,15].Select;
> excelApp.ActiveCell.Formula:='=NetWorkDays(M2,N2)';
>
> Any suggestions would be appreciated.
>
> Nirmal Singh
>
>

[Nirmal Singh]

On Wed, 5 Jan 2005 16:59:08 +0530, "Sharad Naik"
<sharadnaik@nospam-vsnl.net> wrote:

>Well "NetWorkDays" is not a excel built-in function so seems
>to be your user-defined function.
>So where is the function NetWorkDays() located? in an add-in ?
>or in the workbook you open?
>
>Where - ever the code for the fuction is located , add line
>Application.Volatile at the top in the function code.
>
Sharad

The NetWorkDays function is in the Excel Analysis Tool Pack which is
installed on the machines where this application runs.

I've tried Application.Volatile as you suggest and it still doesn't
work.

Nirmal

[Bob Phillips]

excelApp.Cells.Item[2,15]:Formula='=NetWorkDays(M2,N2)'

as a suggestion

--
HTH

-------

Bob Phillips
"Nirmal Singh" <nirmal.singh@towerhamlets.removethisbitgov.uk> wrote in
message news:41dbb5fd.1491046@news.microsoft.com...
> I am using the following code in a COM based application:
>
> excelApp.Cells.Item[2,13]:=EncodeDate(2003,12,8);
> excelApp.Cells.Item[2,14]:=EncodeDate(2003,12,10);
> excelApp.Cells.Item[2,15]:='=NetWorkDays(M2,N2)';
>
> The first two lines simply insert these dates into cells M2 and N2.
> The next line should return the Working days between these dates (in
> cell O2).
>
> The procedure executes without any error, but when I open the Excel
> Workbook cell O2 contains #NAME?, althogh the formula bar shows the
> correct formula. If I then select cell O2 and press ENTER I get the
> correct value (3).
>
> I have also tried the following with the same results:
>
> excelApp.Cells.Item[2,15].Select;
> excelApp.ActiveCell.Formula:='=NetWorkDays(M2,N2)';
>
> Any suggestions would be appreciated.
>
> Nirmal Singh
>
>

[Nirmal Singh]

On Wed, 5 Jan 2005 11:44:14 -0000, "Bob Phillips"
<phillips@tiscali.co.uk> wrote:

>excelApp.Cells.Item[2,15]:Formula='=NetWorkDays(M2,N2)'
>
>as a suggestion
>
Thanks for the suggestion, Bob, but that doesn't work either.

Nirmal

[Tom Ogilvy]

I don't know your specific situation, but when you open the workbook through
Automation, addins are not loaded. It is unclear when you manually edit the
cell, but if you have previously closed excel in your application and then
reopened it manually (at which time the addin would be loaded), then editing
it would indeed clear up the problem.

You could try opening the analysis toolpak like a regular workbook in your
COM app before you perform this operation.

--
Regards,
Tom Ogilvy

"Nirmal Singh" <nirmal.singh@towerhamlets.removethisbitgov.uk> wrote in
message news:41dbdaba.10819640@news.microsoft.com...
> On Wed, 5 Jan 2005 11:44:14 -0000, "Bob Phillips"
> <phillips@tiscali.co.uk> wrote:
>
> >excelApp.Cells.Item[2,15]:Formula='=NetWorkDays(M2,N2)'
> >
> >as a suggestion
> >
> Thanks for the suggestion, Bob, but that doesn't work either.
>
> Nirmal
>

[Nirmal Singh]

On Wed, 5 Jan 2005 08:25:35 -0500, "Tom Ogilvy" <twogilvy@msn.com>
wrote:

>I don't know your specific situation, but when you open the workbook through
>Automation, addins are not loaded. It is unclear when you manually edit the
>cell, but if you have previously closed excel in your application and then
>reopened it manually (at which time the addin would be loaded), then editing
>it would indeed clear up the problem.
>
>You could try opening the analysis toolpak like a regular workbook in your
>COM app before you perform this operation.
>

Tom

Thanks Tom, that was the problem. I've added the following line

excelApp.AddIns.Item['Analysis ToolPak - VBA'].Installed:=True;

and the procedure is working fine now.

This group is absolutely amazing - an answer within a few hours!

Nirmal