Iterating through an Excel range

10
votes

I am using Excel application SheetChange event to capture any change in my Excel app sheets. if a user modifies only 1 cell, then retrieving the cell coordinates can be done via:

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 a typical return value is "$C$11".

But if a user modifies a range of cells by highlighting more than one cell, and using shift-enter to fill the whole range with the same value, the returned string can be something like: "$C$11:$K$11" (indicating a 9 cell in-a-row change).

How can i iterate throw the range? getting each cell coordinate and value in a 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 is not giving me the original range cells new value. I also didn't follow the Target.Item logic - is it zero based array or one based array. On few tries it looked like the Item array is the whole sheet cell range formated as array (thus Item[0] can be used as well, which is one cell to the left of the highlighted range).

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

Thanks

2
c#excel
What library are you using to work with Excel? - Jethro
when ever you need to know what the syntax, objects or logic is when performing something in Excel, try recording a Macro. Usually what it generates gives you a pretty good idea of how to go about solving your problems. - Alexandre Brisebois

2 Answers

15
votes

Since you are already getting the Range object in your event handler, you don't want to re-query the worksheet for your range--you won't get the new values.

Instead, try looping through the Range.Cells property, like this:

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);
}
5
votes

To iterate the Range, it's 1-based, that is:

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