we are using Aspose.Cells For Java 19.10 and have a problem with method calculateFormula.
In given example we are using two variants of formulas.
First one is a simple concatenation of two cells using formula CONCAT in cell D2 (in Germany it is called TEXTKETTE).
The other one is nearly doing the same but code is stored in function “gen_name_u1”. This function is called in C2.
After calling calculateFormula() and saving of excel file per Aspose the cell C2 (contains formula with function call “gen_name_u1”) seems to be invalid. #NAME? is shown instead of “a c”.
Result in cell D2 (direct use of formula function CONCAT) is fine.
Hints for opening generated excel file:
The problem does not occure if you are asked for macro execution. After allowing macro execution, all formula fields will be recalculated in Excel again and result is ok. Please allow macro execution per default.
If you insert manually a new column before column A and delete it immediateley all formulas will be recalculated properly in Excel.
Attachements:
TestCalculateFormulaWithFunctions.java
FormulaTest.xlsm (Test excel file)
FormulaTest gen.xlsm (Generated excel file after use of calculateFormula())
FormulaTest gen.png (Screenshot of generated excel file)
I reproduced the issue as you described using your sample code with your template file. I found after calling calculateFormula() and saving Excel file, the formulas in C column cells are invalid. Please note, Aspose.Cells does not support to execute macros/vba code, so it should not consider the vba functions (e.g “=gen_name_u1”) as common MS Excel formulas/functions (which it seems does) and should not try to calculate/re-calculate it, rather leave or skip these vba formulas. I have logged a ticket with an id “CELLSJAVA-43198” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
Well, the VBA function is stored with VBA codes in the file. As we told you we do not support to execute the vba codes or macros and we have no plan to support executing the VBA functions or macros for now. We suggest you to kindly implement ICustomFunction to calculate custom functions in your own way by yourself. See the topics on how to use ICustomFunction feature for your reference:
ok i forgot to remember that VBA is not supported.
Therefore i would expect that vba cells are not handled and therefore value of cell should be empty. Instead of an empty value the value #NAME? is shown and Excel classifies formula as invalid.
I think this result is not expected.
As we suggested, please implement ICustomFunction interface to handle those custom functions in your own way by yourself. See the topics on how to use ICustomFunction feature for your reference: