Calculate workbook but leave out the cells with link to macros

Aspose.Cells Product Family
1

hi,
I am trying to write some data back into an excel file which contains macros and naturally I use calculate workbook before saving the file because I want my changes to take effect.
Now I know that aspose can not calculate macros but is it possible so that aspose calculates rest of workbook but leaves out macros because right now it’s one way or the other.
Either it calculates the whole workbook and I get error values in place of macro linked cells or I leave it without calculating and then even the regular formulas don’t get calculated/updated.

Some kind of fix will be highly appreciated.
Thanks

2

@kanbee,

Aspose.Cells does not support to execute macros, so you may safely use Workbook.CalculateFormula method to calculate all the formulas inside the workbook except executing macros or vba codes.

Let us know with details and sample Excel file, if you find any issue with calculating formulas in Excel file.

3

I have not found this to be the case. When I use Workbook.CalculateFormula method, the cells which use macro return error values like “#NAME?”. I have attached the excel file.

If calculating could skip macro linked cells, it would help my case.
Macro_saving_test_case.7z (19.7 KB)

Thanks

4

@kanbee
Aspose.Cells does not support cacluating macro functions , so you have to implement a custom calculation engine to calculate them when performing formula calculations.
The sample code as follows:

Workbook wb = new Workbook(filePath + "Macro_saving_test_case.xlsm");
CalculationOptions calculationOptions = new CalculationOptions();
calculationOptions.CustomEngine = new BreakingCalcChain();

wb.CalculateFormula(calculationOptions);
PdfSaveOptions options = new PdfSaveOptions();
options.OnePagePerSheet = true;
wb.Save(filePath + "out.pdf", options);
            
            
private class BreakingCalcChain : AbstractCalculationEngine
{
    public override void Calculate(CalculationData data)
    {
        if (data.FunctionName == "getEleNameFromDesc")
        {
           //Skipping macro execution by throwing exceptions
            throw new Exception("Skip macro calculation");
           //calculate macro formulas by yourself.
        }
    }
}

Hope helps a bit.