7
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.)?

Community
  • 1
  • 1
HeinrichStack
  • 694
  • 3
  • 8
  • 28
  • cross posted in [Social-MSDN](http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/13150936-a39f-42c1-9cd5-86e70c03e797) – Siddharth Rout May 06 '13 at 17:55

3 Answers3

13

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

UndeadBob
  • 1,110
  • 1
  • 15
  • 34
PunkUnicorn
  • 280
  • 6
  • 12
1

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";
Nevyn
  • 2,623
  • 4
  • 18
  • 32
  • Well, as I said. I dont need to set explicitlz `.Formula`, and it works. But my question was a little more than this !! – HeinrichStack May 06 '13 at 20:34
  • Yes, your question was how can I set the entire array in one single statement. My answer was I dont think you can, here is a link that you should browse with all the properties and methods available to you through the interface if you bother to look through it, and an optional implementation. – Nevyn May 07 '13 at 12:46
  • You can set an entire array with a single statement. My question is how to make excel evaluate the formulas in this array (and not treat them as simple strings). – HeinrichStack May 08 '13 at 09:15
  • Correct, you can set the Values with the single statement. But if you read the documentation on that method, its functionally equivalent to directly setting the `value` or `value2` property...which will treat a formula like a text string. I'm not sure there's any method you can use to set the `Formula` property in the manner you are attempting. I have to ask though...if you have to build the array manually anyway, why is it such a big deal to skip the array step and just set the cell formulas directly instead of having the array and setting them from there? – Nevyn May 08 '13 at 11:42
  • Because, I would do this several times, and I dont want to build it each time, i.e. for performance reasons. – HeinrichStack May 09 '13 at 22:10
  • Have you considered a different approach. As long as the formulas are always going to be the same...or close to it at least, you could set the formulas once, manually...then for any other locations you need to set them at, use the built in `copy` functionality, making sure to set the options correctly to copy formulas and not values. You would simply copy the stored formulas from their initial location and plop them down in a new one...making minor changes as needed afterwards. Simply save the original range in its own object for easy back-referencing. – Nevyn May 16 '13 at 20:19
1

I had just met this problem a short while ago, and was searching nearly whole day for the solution but no luck.

And I finally found a solution when trying to fix another problem also relate to cell datatype, in which you should set your 2-dimension array to 'object' type instead of 'string' type. The reason of the problem, in my opinion, is the Formula properties (also Value, Value2) automatically write the data depends on datatype in code. The code should be something like this:

Worksheet sampleSheet = ...;
object[,] dataArr = new object[rowNum,colNum];
for (int i = 0; i < rowNum; i+=1) {
  for (int j = 0; j < colNum; j+=1) {
    dataArr[i,j] = "=SUM(..<something here>..)";
  }  
}
sampleSheet.Range[....].Formula = dataArr;

Hope this help!

Tuong Lan Nguyen
  • 31
  • 5
  • This should be the accepted answer. The data type of the array setting the formula should be 'object' and not string. – EylM Jun 04 '19 at 10:06