#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

 

[Bob Phillips]

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

as a suggestion

 

[Nirmal Singh]

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

 

[Nirmal Singh]

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

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]

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