Iterate over Excel range

Question

I use the ExcelChange event of an Excel application to commit any changes to my Excel application sheets. if the user changes only 1 cell, then the extraction of the cell coordinates can be performed using:

void CellsChange(object Sh, Excel.Range Target) { .... string changedCell = Target.get_Address(Missing.Value, Missing.Value, Excel.XlReferenceStyle.xlA1, Missing.Value, Missing.Value); .... } 

In the above example, the typical return value is "$ C $ 11".

But if the user changes the range of cells, selecting more than one cell and using shift-enter to fill the entire range with the same value, the returned string may be something like this: "$ C $ 11: $ K $ 11" (indicating a change of 9 cells in line).

How can I repeat the range selection? getting each cell coordinate and value in foreach or for loops. I tried the following ...

 for (int i = 0; i < Target.Count; i++) { Excel.Range r = Target.Item[i]; MessageBox.Show(Convert.ToString(r.Value2)); } 

but this code does not give me a new range cell value. I also did not follow the logic of Target.Item - it is a zero array or a single array. After several attempts, it looked like the Item array is the entire range of sheet cells formed as an array (thus, Item [0], which is one cell to the left of the selected range, can be used).

Does anyone have more experience using a Range object and / or the above event?

thanks

Answer 1

Edit: Sorry, I misunderstood the question. Since you already get a Range object in the event handler, you do not want to request a worksheet for your range - you will not get new values.

Instead, try looping through the Range.Cells property, for example:

  foreach (Range c in Target.Cells) { string changedCell = c.get_Address(Type.Missing, Type.Missing, XlReferenceStyle.xlA1, Type.Missing, Type.Missing); MessageBox.Show("Address:" + changedCell + " Value: " + c.Value2); } 

Answer 2

To iterate over a range, it is based on 1, i.e.:

 for (int i = 1; i <= Target.Count; i++) { Excel.Range r = (Excel.Range)Target.Item[i]; MessageBox.Show(Convert.ToString(r.Value2)); }