Fill the .Formula's of an Excel Range with an array (of strings)

Question

_{(In C# (though the language shouldn’t matter))}

range.Formula = array;

does not result in an error, but behaves exactly like

range.Value2 = array;

: the Formulas appear like Values. Only after I do F2, Enter on a cell, its Formula gets evaluated properly.

This is how I fill the array:

// setup (parameters)
int rows = cols = 10;
int rowStep = 1000*1000;
var array = new string[rows, cols];
// Fill the array.
for (long iRow = 1; iRow == rows; iRow++)
{
  for (long iCol = 1; iCol == cols; iCol++)
  {
    // Put a count in the cell.  E.g. "=A1+1+1000000"
    array[iRow-1, iCol-1] = "=A1+" + iCol + "+" + (iRow * rowStep);
  }
}

FunctorSalad · Accepted Answer · 2013-02-15 15:13:56Z

4

Works for me if I use object[,] instead of string[,].

edited Feb 15, 2013 at 15:13

answered Feb 15, 2013 at 15:04

FunctorSalad

2,6602 gold badges26 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Koerner Over a year ago

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post.

FunctorSalad Over a year ago

The change I described solves the problem the question was about (it causes Excel to evaluate the formulas without any further action). I guess I worded my answer too cautiously (I only checked that it solves this particular instance, with my version of Excel) and you understood it as saying that I only got it to compile this way?

Community · Accepted Answer · 2017-05-23 11:33:26Z

0

Don't ask me why, but this (second line) fixed it:

range.Formula = array;
range.Formula = range.Formula;

Unfortunately this takes twice the time. Is there a better way which avoids that penalty?

_{The upper solution was informed by this post.}

edited May 23, 2017 at 11:33

CommunityBot

11 silver badge

answered Mar 11, 2012 at 22:44

Aaron Thoma

4,0461 gold badge40 silver badges38 bronze badges

1 Comment

StoriKnow Over a year ago

As @FunctorSalad suggests in his answer, change the type to object. If you do this, Excel will successfully evaluate range.Formula = array.

Collectives™ on Stack Overflow

Fill the .Formula's of an Excel Range with an array (of strings)

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related