Reading range w/ 255+ characters throws error

[jarabe28]

Hi, I need to read an Excel range into an array which I then use to modify
it's data and formulas. I then write the modified data back to Excel. This
code: shtValues = (System.Array)row.Formula;
fails when it encounters 255+ characters in a cell. My code is below. Please
tell me if there's a way to work around this.

try
{
// Creates and initializes a new CollectionBase.
this.oSheetRangeCollection = new RangeCollection();

System.Array shtValues=null;
int iLastCol = ws.UsedRange.Columns.Count;
int iLastRow = ws.UsedRange.Rows.Count;

for (int i = 1; i <= iLastRow; i++)
{
Excel.Range row = (Excel.Range) ws.get_Range(ws.Cells[i, 1], ws.Cells[i,
iLastCol]);
//insert the data into the object[,] one row at a time
shtValues = (System.Array)row.Formula;

this.oSheetRangeCollection.AddRow(shtValues);
nav.ReleaseComObj(row);
}
}

 

[Peter T]

The limit of a formula array is 255 characters, or a even slightly less.

If the formula includes sheet names you might be able to cheat slightly by
temporarily renaming the sheet name to a single character, applying your
formula with the single character names, then renaming the sheet.

Having done that you would not be able to programmatically read the 255+
array formula until you again reduced the sheet names.

Regards,
Peter T

 

[Dave Peterson]

If I were using VBA...

Take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

It's a technique to workaround that .formulaarray length limit.

Hi, I need to read an Excel range into an array which I then use to modify
it's data and formulas. I then write the modified data back to Excel. This
code: shtValues = (System.Array)row.Formula;
fails when it encounters 255+ characters in a cell. My code is below. Please
tell me if there's a way to work around this.

try
{
// Creates and initializes a new CollectionBase.
this.oSheetRangeCollection = new RangeCollection();

System.Array shtValues=null;
int iLastCol = ws.UsedRange.Columns.Count;
int iLastRow = ws.UsedRange.Rows.Count;

for (int i = 1; i <= iLastRow; i++)
{
Excel.Range row = (Excel.Range) ws.get_Range(ws.Cells[i, 1], ws.Cells[i,
iLastCol]);
//insert the data into the object[,] one row at a time
shtValues = (System.Array)row.Formula;

this.oSheetRangeCollection.AddRow(shtValues);
nav.ReleaseComObj(row);
}
}

 

[Peter T]

If I were using VBA...

Take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

It's a technique to workaround that .formulaarray length limit.

I forgot about that, yes a cunning trick (also a clever calendar formula in
the demo)

"Dave sent me a solution to this problem that's shown below."
Don't suppose you know which "Dave" ...?

Regards,
Peter T

 

[Dave Peterson]

I don't think it was me.

I forgot about that, yes a cunning trick (also a clever calendar formula in
the demo)

"Dave sent me a solution to this problem that's shown below."
Don't suppose you know which "Dave" ...?

Regards,
Peter T