Excel.Worksheet.Cells[row,col] = "=Formula" vs. Range.set_Value(Missing.Value, arrayFormulas)

Question

Excel.Worksheet.Cells[row,col] = "=Formula / reference" 

While in the above Excel updates the formula / reference and shows the result in the datasheet, in the code below, when using Range.set_Value(..) the datasheet is not updated at all

string[,] myFormulas = new string[nrRows, nrColumns];
...
myFormulas [x,y] = e.g. "=SUM(D1:D" + rowNr + ")";
myFormulas [x,y+1] = e.g. "=Table1!A1";
...
Range.set_Value(Missing.Value, myFormulas)

and it only shows the formula as a string, e.g. =Table1!A1.

I cannot make it update. Neither with CalucalteAll(), nor with RefreshAll(), nor with anyhing. Any suggestions how to achieve an update in the datasheet?

EDIT : You can set an entire array with a single statement Range.set_Value(Missing.Value, myFormulas). My question is how to make excel evaluate the formulas in this array (and not treat them as simple strings, or setting the cells one by one which Excel than recalculates.)?

Answer 1

I found that rangeVariable.Formula = rangeVariable.Value will translate a 'formula as text' into a bona fide Excel formula, and this is done against all cells in that range.

Worksheet sheetOne = ...
int numberOfRows = 5000;

// Make a massive range on sheet 1, then use cell assignment by array for fastness
Range range = sheetOne.Range["A1"];
string[,] links = new string[numberOfRows+1, 1];
range = range.Resize[numberOfRows+1, 1];

for (int count = 0; count < numberOfRows; count++)
{
    // Build the =HYPERLINK formula to set as text in each cell
    string worksheet = "Sheet2";
    string cellRef = string.Format("A{0}", count + 1);
    string formula = string.Format("=HYPERLINK(\"#{0}!{1}\", \"{2}\")", worksheet, cellRef, string.Format("Hyperlink number {0}", count));

    links[count, 0] = formula;
}

//range.set_Item(Type.Missing, Type.Missing, links);
range.set_Value(Type.Missing, links) // thanks HeinrichStack
range.Formula = range.Value; //<--- Boom baby, all 'formula as text' turns into bona fide excel formula

Hope this helps

Answer 2

I dont think that there is any way to set the Formula value for any given cell, except by deliberately setting the Formula property for it, one cell at a time.

Check out the properties and examples here

so I think what you need to do is something more like this:

Worksheet.Cells[x,y].Formula = "=SUM(D1:D" + rowNr + ")";
Worksheet.Cells[x,y+1].Formula = "=Table1!A1";