When foreach in Excel goes wrong
This seems like a reasonable piece of code:
For Each oCell As Excel.Range In Me.Range("a1:b5")
oCell.Value = "test"
Next
so does this:
foreach Excel.CustomProperty prop in worksheet.CustomProperties
{
Console.WriteLine(prop.Name);
}
Unfortunately, when you run this code you will get a "Missing member" exception. Some collections in Excel can't be "for-eached" over. To do "foreach" the CLR has to get an enumerator from the collection. The CLR and CLR interop expect to find a _NewEnum member method on the collection. Some Excel collections have a _NewEnum member that is implemented as a property and not a method.
There is a KB on this issue: https://support.microsoft.com/?kbid=328347
Clearly this is not happy, but there is a work around--use a for next loop and get members of the collection using Item with a 1-based index.
Hopefully, this is an issue we will get fixed in VS 2005 so that all Excel collections, even the ones that have a _NewEnum member that is a property, can be for-eached.
Comments
- Anonymous
November 18, 2004
Which of these fail? Both? Or just the second?
(I ask because the first one looks fine. I use code like that all the time.) - Anonymous
November 23, 2004
I don't know this dot net stuff, but in regular VBA, the official way to write that For Each is:
For Each oCell In Me.Range("a1:b5").Cells
Usually Excel figures out what you meant, but you could also have meant .Rows or .Columns.
- Jon
XL MVP - Anonymous
January 21, 2009
PingBack from http://www.keyongtech.com/4019085-worksheet-customproperties