I am currently creating an Excel web add-in in C# with Visual Studio.

Is there a way to find if a cell has a formula ?

Find my code below. The aim of the code is to check the cells in A1 to E1 and write in A2 to E2 if the cell in the previous line has a formula. A button (using html file) on the side is used to launch the code.

Office.onReady(() => {
    document.getElementById("ajoutDonnees").addEventListener("click", function () {
        deplacerValeursEtRemplacerFormulesParZeroOuUn();
    });

    async function deplacerValeursEtRemplacerFormulesParZeroOuUn() {
        try {
            await Excel.run(async (context) => {
                const sourceSheet = context.workbook.worksheets.getItem("Feuil1");
                const targetSheet = context.workbook.worksheets.getItem("Feuil1");

                const sourceRange = sourceSheet.getRange("A1:E1");
                sourceRange.load("values, formulas");
                await context.sync();

                const sourceValues = sourceRange.values;
                const sourceFormulas = sourceRange.formulas[0];

                
                for (let i = 0; i < sourceValues[0].length; i++) {
                    if (sourceRange.getCell(0, i).formula == true) {
                        sourceRange.getCell(1, i).values = [[1]]; // Cell with formula
                    } else {
                        sourceRange.getCell(1, i).values = [[0]]; // Cell without formula
                    }
                }
            });
        } catch (error) {
            console.error("Erreur lors du déplacement : " + error);
        }
    }
});

I tried to find an alternative: HasFormula seems to be usable in the case of VSTO, which is not my case; .Cellfformula is not working; getting the equal sign with sourceRange.getCell(0, i).formula.toString().charAt(0) === "=" did not work.

I tried with the help of Gemini and Copilot (AI), but they could not find a solution...

Thank you for your help

I am currently creating an Excel web add-in in C# with Visual Studio.

Is there a way to find if a cell has a formula ?

Find my code below. The aim of the code is to check the cells in A1 to E1 and write in A2 to E2 if the cell in the previous line has a formula. A button (using html file) on the side is used to launch the code.

Office.onReady(() => {
    document.getElementById("ajoutDonnees").addEventListener("click", function () {
        deplacerValeursEtRemplacerFormulesParZeroOuUn();
    });

    async function deplacerValeursEtRemplacerFormulesParZeroOuUn() {
        try {
            await Excel.run(async (context) => {
                const sourceSheet = context.workbook.worksheets.getItem("Feuil1");
                const targetSheet = context.workbook.worksheets.getItem("Feuil1");

                const sourceRange = sourceSheet.getRange("A1:E1");
                sourceRange.load("values, formulas");
                await context.sync();

                const sourceValues = sourceRange.values;
                const sourceFormulas = sourceRange.formulas[0];

                
                for (let i = 0; i < sourceValues[0].length; i++) {
                    if (sourceRange.getCell(0, i).formula == true) {
                        sourceRange.getCell(1, i).values = [[1]]; // Cell with formula
                    } else {
                        sourceRange.getCell(1, i).values = [[0]]; // Cell without formula
                    }
                }
            });
        } catch (error) {
            console.error("Erreur lors du déplacement : " + error);
        }
    }
});

I tried to find an alternative: HasFormula seems to be usable in the case of VSTO, which is not my case; .Cellfformula is not working; getting the equal sign with sourceRange.getCell(0, i).formula.toString().charAt(0) === "=" did not work.

I tried with the help of Gemini and Copilot (AI), but they could not find a solution...

Thank you for your help

• c#
• excel
• add-in

• You could use Specialcells, formulas on the range and then offset the ones found by 1 column? – jkpieterse Commented Mar 18 at 16:57
• From what I could read, Specialcells works with Interop which is used in VSTO, in my case it is not possible to use it since I would an Add-in working on Android and IOS – Mike Commented Mar 19 at 4:08
• I haven't found a reliable source on what a C# web add-in is exactly, nor docs about the object model that is available to you. But I would expect SpecialCells to be part of the Object model – jkpieterse Commented Mar 19 at 8:34

1 Answer 1

I tried new things and it works, see code below. I put a comment on the lines in the loop.

An answer to my question is :

const sourceValues = sourceRange.values[0][i];
const sourceFormulas = sourceRange.formulas[0][i];
if (sourceValues === sourceFormulas) { //No formula

The code below gets values from A1 to E1 then:
- write 0 in the line 2 if cell above has a formula, otherwise 1;
- write the formula for calculation in line 3;
- write the formula with ' before to be read in cell in line 4;
- write the values collected in line 5.

Office.onReady(() => {
    document.getElementById("ajoutDonnees").addEventListener("click", function () {
        deplacerValeursEtRemplacerFormulesParZeroOuUn();
    });

    async function deplacerValeursEtRemplacerFormulesParZeroOuUn() {
        try {
            await Excel.run(async (context) => {
                const sourceSheet = context.workbook.worksheets.getItem("Feuil1");
                const targetSheet = context.workbook.worksheets.getItem("Feuil1");

                const sourceRange = sourceSheet.getRange("A1:E1");
                sourceRange.load("values, formulas"); // Charger les valeurs et les formules
                await context.sync();

                for (let i = 0; i < sourceRange.values[0].length; i++) {
                    const sourceValues = sourceRange.values[0][i];
                    const sourceFormulas = sourceRange.formulas[0][i];
                    if (sourceValues === sourceFormulas) { //No formula
                        sourceRange.getCell(1, i).values = [[1]]; //Write 1
                    } else { //Formula
                        sourceRange.getCell(1, i).values = [[0]]; //Write 0
                        sourceSheet.getCell(2, i).values = [[sourceRange.formulas[0][i]]]; //Write formula in cell (calculated formula)
                        sourceSheet.getCell(3, i).values = [["'" + sourceRange.formulas[0][i]]]; //Write formula in cell (readable formula)
                    }
                    sourceSheet.getCell(4, i).values = [[sourceRange.values[0][i]]]; // Write value in cell
                }
            });
        } catch (error) {
            console.error("Erreur lors du déplacement : " + error);
        }
    }
});