Enumeration of Excel.Range Cells via .NET Interop

When using C# or VB.NET 2002/2003 (.NET 1.0/1.1) and either the Office XP or Office 2003 Primary Interop Assemblies from Microsoft to automate Excel, a System.Runtime.InteropServices.COMException "Member not found" error will be encountered when trying to enumerate the cells of a Range using foreach in C# or For Each in VB.NET. This issue was documented by Microsoft back in August 2004 in KB328347; however, the workaround they provided is insufficient for dealing with all Ranges.

What Microsoft's workaround doesn't take into account is that every Range includes at least one Area, itself a Range, which represents a single cell or a set of contiguous cells, and many Ranges include multiple Areas, such as "A1:B5,C1" or "A1:B5,B2:C3". So, in order to work correctly for all Range objects, Microsoft's workaround needs to include an Area enumeration as well.

Here's the Microsoft workaround, originally given in VB.NET, with an Area enumeration added to it:

Dim oApp As New Excel.Application()
oApp.Visible = True
oApp.UserControl = True

Dim oSheet As Excel.Worksheet
oSheet = oApp.Workbooks.Add.Worksheets.Item(1)

Dim oRng As Excel.Range, oAreaRng As Excel.Range
Dim nRows As Long, nCols As Long, nAreas as Long

oRng = oSheet.Range("A1:B5,C1") ' Multiple Areas

For nAreas = 1 To oRng.Areas.Count
  oAreaRng = oRng.Areas(nAreas)
  For nRows = 1 To oAreaRng.Rows.Count
    For nCols = 1 To oAreaRng.Columns.Count
      oAreaRng.Cells(nRows, nCols).Value = "test"
    Next
  Next
Next

Even by Microsoft's historically weak sample standards that's some pretty ugly code. Plural enumeration variables? Requiring Option Strict Off in order to compile? Yuck.

Here's the same code in C#:

System.Reflection.Missing missing = System.Reflection.Missing.Value;

Excel.Application oApp = new Excel.ApplicationClass();
oApp.Visible = true;
oApp.UserControl = true;

Excel.Worksheet oSheet;
oSheet = (Excel.Worksheet) oApp.Workbooks.Add(missing).Worksheets[1];

Excel.Range oRng, oAreaRng;

oRng = oSheet.get_Range("A1:B5,C1", missing); // Multiple Areas

for (int nAreas = 1; nAreas <= oRng.Areas.Count; nAreas++) {
  oAreaRng = oRng.Areas[nAreas];
  for (int nRows = 1; nRows <= oAreaRng.Rows.Count; nRows++) {
    for (int nCols = 1; nCols <= oAreaRng.Columns.Count; nCols++) {
      ((Excel.Range) oAreaRng.Cells[nRows, nCols]).Value2 = "test";
    }
  }
}

Ugly VB.NET Office automation code just looks worse in C#.

As far as I can tell, foreach/For Each enumeration of Range cells is not an issue in C# or VB.NET 2005 (.NET 2.0).