Inserting multiple formulas using Excel VSTO Add-In wrongly placed/not calculated

Question

I'm using C# and Interop to develop an Excel add-in. I wrote code which inserts formulas into cells one-by-one which works fine:

private static void SetFormulasCellwise(Range dataRange,
    IEnumerable<string> columnFormulas,
    int columnIndex)
{
    var rowIndex = 0;
    foreach (var columnFormula in columnFormulas)
    {
        var dataColumnCell = dataRange.Cells[rowIndex + 1, columnIndex + 1];

        try
        {
            dataColumnCell.FormulaLocal = columnFormula;
        }
        catch (COMException)
        {
            MessageBox.Show("Failed to set column formula\n"
                            + columnFormula + "\n" +
                            "to row " + (rowIndex + 1) + " and column "
                            + (columnIndex + 1) + ".\n" +
                            "Please make sure the formula is valid\n" +
                            "and that the workbook is writable.",
                "Error");
            throw;
        }

        rowIndex++;
    }
}

However, performance wise this isn't ideal. Even with disabling formula calculations, setting all formulas and re-enabling formula calculations this is kinda slow:

Excel.Application.Calculation = enable ? xlCalculationManual : xlCalculationAutomatic;

I wrote another version which sets the formulas column wise as follows:

private static void SetFormulasColumnwise(Range dataRange,
    int columnIndex,
    IReadOnlyCollection<string> columnFormulas)
{
    var columnNumber = columnIndex + 1;
    var dataColumnCell = dataRange.Range[dataRange.Cells[1, columnNumber],
        dataRange.Cells[dataRange.Rows.Count, columnNumber]];
    var columnFormulas2Dimensional = ToColumn2DimensionalArray(columnFormulas);

    try
    {
        dataColumnCell.Formula = columnFormulas2Dimensional;
    }
    catch (COMException)
    {
        MessageBox.Show("Failed to set column formulas "
                        + "to column " + columnNumber + ".\n" +
                        "Please make sure the formula is valid\n" +
                        "and that the workbook is writable.",
            "Error");
        throw;
    }
}

private static string[,] ToColumn2DimensionalArray(IReadOnlyCollection<string> columnFormulas)
{
    var columnFormulas2Dimensional = new string[columnFormulas.Count, 1];
    var columnFormulasIndex = 0;
    foreach (var columnFormula in columnFormulas)
    {
        columnFormulas2Dimensional[columnFormulasIndex, 0] = columnFormula;
        columnFormulasIndex++;
    }

    return columnFormulas2Dimensional;
}

With the column version the formulas are inserted into the range but shifted downwards by an offset like 6 and they are not calculated automatically. If I select the formula bar and press Enter just that one formula is calculated but I need all of them automatically calculated. Why is this happening? Both code snippets should more or less be identical since they modify the cells in the same way. The column version just combines operations to improve performance. I tried forcing Excel to recalculate the range by calling e.g. dataRange.Calculate() but it did nothing.

What's wrong with the column version? How can I make it behave like the first version but more efficiently than the first version?

Answer 1

 dataColumnCell.Formula = columnFormulas2Dimensional;

columnFormulas2Dimensional is of type: string[,], but dataColumnCell.Formula is waiting for a type: string

Just to try, this should display a value:

dataRange.FormulaLocal = columnFormulas2Dimensional[0,0];

(I've tested on Visual Studio 2017 and it seems to work. I've just used a basic formula.)

 ThisAddIn.SetFormulasColumnwise(Globals.ThisAddIn.GetTestCells(), 1, new List<string>() { "=SOMME(A1:A10)", "=SOMME(A1:A10)", "=SOMME(A1:A10)", "=SOMME(A1:A10)" });

Here is my full code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace ExcelAddIn1
{
public partial class ThisAddIn
{
    private void ThisAddIn_Startup(object sender, System.EventArgs e)
    {

    }

    private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
    {
    }
    public void Calc(Range dataRange)
    {
        dataRange.Calculate();
    }
    public void SetSource()
    {
        Excel.Worksheet ws = Application.ActiveSheet as Excel.Worksheet;
        Excel.Range c1 = ws.Cells[1, 1];
        Excel.Range c2 = ws.Cells[10, 1];

        var oRangeSource = (Excel.Range)ws.get_Range(c1, c2);
        for (int i = 1; i <= 10; i++)
        {
            oRangeSource.Cells[i, 1].Value = i;
        }
    }
    public Excel.Range GetTestCells()
    {
        Excel.Worksheet ws = Application.ActiveSheet as Excel.Worksheet;
        Excel.Range c1 = ws.Cells[1, 2];
        Excel.Range c2 = ws.Cells[10, 2];

        //Get the range using number index
        var oRange = (Excel.Range)ws.get_Range(c1, c2);

        return oRange;
    }
    public static void SetFormulasCellwise(Range dataRange,IEnumerable<string> columnFormulas,int columnIndex)
    {
        var rowIndex = 0;
        foreach (var columnFormula in columnFormulas)
        {
            var dataColumnCell = dataRange.Cells[rowIndex+1 , columnIndex];

            try
            {
                dataColumnCell.FormulaLocal = columnFormula;
            }
            catch (COMException)
            {
                MessageBox.Show("Failed to set column formula\n"
                                + columnFormula + "\n" +
                                "to row " + (rowIndex + 1) + " and column "
                                + (columnIndex + 1) + ".\n" +
                                "Please make sure the formula is valid\n" +
                                "and that the workbook is writable.",
                    "Error");
                throw;
            }

            rowIndex++;
        }
    }
    public static void SetFormulasColumnwise(Range dataRange, int columnIndex, IReadOnlyCollection<string> columnFormulas)
    {
        var columnNumber = columnIndex;
        var dataColumnCell = dataRange;
        var columnFormulas2Dimensional = ToColumn2DimensionalArray(columnFormulas);

        try
        {
            dataRange.FormulaLocal = columnFormulas2Dimensional[0,0];


        }
        catch (COMException)
        {
            MessageBox.Show("Failed to set column formulas "
                            + "to column " + columnNumber + ".\n" +
                            "Please make sure the formula is valid\n" +
                            "and that the workbook is writable.",
                "Error");
            throw;
        }
    }

    public static string[,] ToColumn2DimensionalArray(IReadOnlyCollection<string> columnFormulas)
    {
        var columnFormulas2Dimensional = new string[columnFormulas.Count, 1];
        var columnFormulasIndex = 0;
        foreach (var columnFormula in columnFormulas)
        {
            columnFormulas2Dimensional[columnFormulasIndex, 0] = columnFormula;
            columnFormulasIndex++;
        }

        return columnFormulas2Dimensional;
    }

    #region Code généré par VSTO

    /// <summary>
    /// Méthode requise pour la prise en charge du concepteur - ne modifiez pas
    /// le contenu de cette méthode avec l'éditeur de code.
    /// </summary>
    private void InternalStartup()
    {
        this.Startup += new System.EventHandler(ThisAddIn_Startup);
        this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
    }

    #endregion
}

}

and in my test ribbon button click:

private void testxav_Click(object sender, RibbonControlEventArgs e)
    {
        Globals.ThisAddIn.SetSource();
        ThisAddIn.SetFormulasColumnwise(Globals.ThisAddIn.GetTestCells(), 1, new List<string>() { "=SOMME(A1:A10)", "=SOMME(A1:A10)", "=SOMME(A1:A10)", "=SOMME(A1:A10)" });
        //ThisAddIn.SetFormulasCellwise(Globals.ThisAddIn.GetTestCells(), new List<string>() { "=SOMME(A1:A10)", "=SOMME(A1:A10)", "=SOMME(A1:A10)", "=SOMME(A1:A10)" },1);

        Globals.ThisAddIn.Calc(Globals.ThisAddIn.GetTestCells());
    }