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; 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.