User Defined Function returning value of #NAME? when reading cell value

Aspose.Cells Product Family
1

I have a Excel template that a user fills out. One of the cells uses a vba function to calculate a value:

Public Function CalcEDBT(strDivID As String, strItemType As String) As String
CalcEDBT = Format(Now(), "dd") & Trim(strDivID) & Trim(strItemType) & Format(Now(), "MM")
End Function

The cell contains the formula: =CalcEDBT(D2,E2)

The template is then processed by an application that uses Aspose Cells to retrieve the information the user has filled out and stores the information in a database. For the cell that contains the user defined function call, a value of "#NAME?" is stored in the database. When I check the stored spreadsheet, it contains the correctly calculated value.

Any thoughts would be appreciated.

2

Hi Robert,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try the latest version: Aspose.Cells
for .NET v7.6.0.5 and see if it resolves your issue.

If your problem still occurs, then please provide us your source file containing this formula. We will look into it and help you asap.

3

The link to the dll you provided appears to be the same version as the one I am currently using. The end result has not changed utilizing the dll provided.

I have attached the source spreadsheet. This is a simplified version that is used for testing. It yields the same results, however. The cells that experience the problem are E4 and E22.

4

Hi Robert,

Thanks for your file and using Aspose.Cells.

We were able to observe this issue using the following code. It seems, Aspose.Cells is not able to calculate the user-defined formula. The cell E22 returns “#NAME?” when the workbook.CalculateFormula() is called. We have logged this issue in our database. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-42127.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\MasterItemSetupCalcs_FG.xlsm”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“E22”];


Console.WriteLine(cell.StringValue);


workbook.CalculateFormula();


Console.WriteLine(cell.StringValue);
Output:
05BRFG11
#NAME?
5

Thanks for looking into the issue. That was my experience as well. After the CalculateFormula call, the cell value was #NAME?.

I tried putting the user defined functions into an Excel Add In, but nothing seems to have any effect. I still get the same result.

6

Hi,

Thanks for using Aspose.Cells.

We could not support calculating User Defined Function even it’s in
VBA project.

Please call CalculateFormula(bool ignoreError, ICustomFunction
customFunction) method and implement User Defined Function in ICustomFunction.