.net Option Strict Excel For Each In Loop Question

[madferretnick]

Has anybody tried automating Excel / creating COM Add in from Vb.net and using the For Each In Loop with Option Strict on for a list of cells in a range?

I get a System. Runtime. InteropServices. COMException!

'This Fails
Option Strict On
Private Sub TestForEachLoop()

Dim cell As Excel.Range
Dim ws As Excel.Worksheet
Dim rng As Excel.Range
ws = CType(m_ExcelApp.ActiveWorkbook.Worksheets.Item("Sheet1"), Excel.Worksheet)
rng = ws.Range("a1").CurrentRegion
For Each cell In rng
Debug.WriteLine(cell.Value)
Next

End Sub

'This Works
Option Strict Off
Private Sub TestForEachLoop()

Dim cell As Excel.Range
Dim ws As Excel.Worksheet
Dim rng As Object
ws = CType(m_ExcelApp.ActiveWorkbook.Worksheets.Item("Sheet1"), Excel.Worksheet)
rng = ws.Range("a1").CurrentRegion
For Each cell In rng
Debug.WriteLine(cell.Value)
Next

End Sub


_COMPlusExceptionCode -532459699
_message "Member not found."
StackTrace " at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Excel.Range.GetEnumerator()
at TestForEachLoop() in

 

[Greg Truby]

OK - this is an ancient thread. I just happened across it due to a search. But should it be of any help to madferret or another reader, in VBA you'd need Set rng = ws.range("a1").currentregion

Don't know if the same rules would apply in vb.net or not since I've not programmed in it.

 

[Legacy 98055]

No "Set" in "Net". You need to Implement the IEnumerable interface to enum through objects.

Tom