#NAME? for formulas saved in xlsb format

Aspose.Cells Product Family
1

Hi,

We have some user defined formulas that we include as an excel addin. When using the cell.SetFormula function, we can see that the UDF works fine when the file is saved and opened as an xlsx. Cell A1 contains “TEST DATA” which is returned from our custom function. However, when we save the same workbook object as an xlsb we can see that the cell actually has a #NAME? error instead which means that excel couldn’t find the custom function. When you hit enter on the cell formula bar, it’ll pick up the formula and return the correct value.

I created a reproducer here with this code:

        var workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];
        var cell = worksheet.Cells["A1"];
        cell.SetFormula("=TEST_UDF()", "-");
        workbook.Save("test_udf.xlsx", SaveFormat.Xlsx);
        workbook.Save("test_udf_binary.xlsb", SaveFormat.Xlsb);

For this to work, you can add this simple test addin to your excel by going to: file-> options -> Add-ins -> Excel Add-ins and browsing to the TestUDF.xlam file.

TestUDF.zip (9.5 KB)

It contains a simple TEST_UDF() custom function that will return TEST DATA in the cell.

2

@dfactset,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46535 - #NAME? for formulas saved in xlsb format
3

@dfactset,

We have evaluated your issue further. Well, your issue is due to the different formula data expression for xml and binary data. Even in MS Excel, if you create such kind of formulas and save it before loading the Excel addin, then when opening the generated XLS/XLSB file again, the generated formulas will always be “#NAME?”, no matter whether the Excel addin has been loaded or not. To make such kind of formulas work with the Excel addin, please set formulas like following:

cell.Formula = “=‘TESTUDF.xlam’!TEST_UDF()”;

Or, in the coming new versions (from v19.1), register the Excel addins at first and then use them in formulas:
int id = workbook.Worksheets.RegisterAddInFunction(“TESTUDF.xlam”, “TEST_UDF”, false);
workbook.Worksheets.RegisterAddInFunction(id, “TEST_UDF1”); //in this way you can add more functions that are in the same file
cell.Formula = “=TEST_UDF()”;
…

4

Interesting, in this case we only used the XLAM file as a simpler reproducer and not what we actually use in practice. Do you know if the RegisterAddInFunction would work with an XLL file that contains custom functions?

5

@dfactset,

We do not have enough add ins to test all scenarios. Please test the add ins you have and need to use, if you find it can work in MS Excel but cannot for our component, please send us the files (add in files and the Excel file in which formulas use those add ins). We will make further investigations.

6

The issues you have found earlier (filed as CELLSNET-46535) have been fixed in Aspose.Cells for .NET v19.1. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi