Macro function calculation

Aspose.Cells Product Family
,
1

We have a xlsm file with a macro function, we are calculating
we are using some logic like this
THIS IS THE SAMPLE LINK

            Workbook wb = new Workbook(filePath);
            wb.calculateFormula();
            WorksheetCollection sheets = wb.getWorksheets();
            for (int i = 0; i < sheets.getCount(); i++) {
                cells.removeFormulas();
            }
            String tempFilePath = Files.createTempFile(null, "." + fileExtension).toString();
            wb.save(tempFilePath);
            return tempFilePath;

Why are the macro function calculation returning #Name ?
image.png (4.0 KB)

2

@chetanepiq

The #NAME? error in your macro function calculation typically indicates that Excel cannot recognize a name or function used in the formula. Here are some common reasons for this error and troubleshooting steps you can take:

  1. Undefined Names: Ensure that all named ranges or functions used in your formulas are defined correctly in the workbook.

  2. Macro Functions: If your formulas rely on custom VBA functions, make sure that macros are enabled and that the functions are correctly defined in the VBA editor.

  3. Formula Syntax: Check for any syntax errors in your formulas. Even a small typo can lead to this error.

  4. Calculation Mode: Ensure that the workbook is set to automatic calculation mode. You can do this by checking the calculation options in Excel.

  5. Remove Formulas: In your code, you are removing formulas with cells.removeFormulas();. If you need to keep the formulas for calculation, consider commenting out or removing this line.

Here’s a modified version of your code snippet that retains the formulas:

Workbook wb = new Workbook(filePath);
wb.calculateFormula();
WorksheetCollection sheets = wb.getWorksheets();
for (int i = 0; i < sheets.getCount(); i++) {
    Worksheet sheet = sheets.get(i);
    sheet.setVisible(true);
    Cells cells = sheet.getCells();
    double standardHeight = cells.getStandardHeight();
    for (int row = 0, len = cells.getMaxDataRow() + 1; row < len; row++) {
        cells.setRowHeight(row, standardHeight);
    }
    double standardWidth = cells.getStandardWidth();
    for (int col = 0, len = cells.getMaxDataColumn() + 1; col < len; col++) {
        cells.setColumnWidth(col, standardWidth);
    }
    // cells.removeFormulas(); // Commented out to retain formulas
}
String tempFilePath = Files.createTempFile(null, "." + fileExtension).toString();
wb.save(tempFilePath);
return tempFilePath;

If the issue persists, consider checking for any specific error messages or details that might provide further insight into the problem.

3

We want to remove the formulas from it , that’s why we calculated early and remove from it ~ aspose cells JAVA

SOURCE: Removing Formula’s after calculation - Free Support Forum - aspose.com

PLEASE SEE THE SAMPLE ATTACHED FILE IN FIRST COMMENT AND IMAGE SHOWING THE ISSUE

IN THE LIVE VIEWER IT SEEMS TO WORK FINE , DO WE NEED TO DO SOMETHING DIFFERENT HERE ?

4

@chetanepiq,

Thanks for the sample Excel file and screenshot.

I am afraid, Aspose.Cells does not support to execute vba codes or macros in Excel file/spreadsheet although it supports to add/update vba codes in Excel workbook via APIs.

I think you may try to create and implement custom calculations via Aspose.Cells to perform the tasks what your macros/vba codes are doing. Please see the document with example code on how to calculate custom formulas for your reference: Implement Custom Calculation Engine to extend the Default Calculation Engine of Aspose.Cells|Documentation

In the event of any other query or issue, feel free to contact us any time, we will be happy to assist you.

5

@chetanepiq

For such kind of custom functions, we cannot know how to calculate them because we do not know anything about the function itself.
To keep the original value of it which may be saved in the template, you may choose one of below options according to your requirement:

  1. Just call Cells.removeFormulas() without re-calculating the workbook. All values of cells/formulas will be kept as what they were saved in the template file.
  2. If you know how those custom functions should be calculated, you may implement the calculation engine(AbstractCalculationEngine | Aspose.Cells for Java API Reference) of your own to calculate them, just as Amjad said in prior reply.
  3. If you do not need those custom functions to be re-calculated, but there are some other formulas that need to be re-calculate, you may implement the custom calculation engine in a easier way:
        class IgnoreCustomFunction extends AbstractCalculationEngine
        {
            public void calculate(CalculationData data)
            {
                skipCalculation();
            }
        }
6

Thanks for the response but if the reply here which I understood means aspose cells cannot calculate this macro type function, then how is it correctly getting executed in aspose cells live viewer

image.png (4.9 KB)

7

@chetanepiq
We do not calculate formulas when opening the file with viewer, so you will see the value that is cached in template file.
We do not support calculating VBA codes because it’s coded with VB codes.