Excel get_Range missing when interop assembly is embedded in .NET 4.0

Question

I build an assembly referencing a COM interop DLL. If I embed the COM interop types by setting Embed Interop Types to True in the Reference's properties (VS2010), at run-time an error occurs "object does not contain a definition for get_Range". If COM interop types are not embedded then no error occurs.

Does anyone know why a particular method, Worksheet.get_Range should be ommitted or how to work around this or have any other relevant insights? I should be grateful for any help.

The interop dll contains a reference to Worksheet.get_Range(object, [object]). Using reflector on my calling assembly there is no mention of get_Range under Worksheet.

The interop assembly I am embedding is generated from Excel9.olb. I am not using PIAs as the application targets multiple Excel versions.

Answer 1

I have not tried this out myself yet, but I believe that the syntax has changed when using embedded COM Interop types under C# 4.0 to a more "C# friendly" syntax.

Therefore, instead of looking for the get_Range(object, [object]) method, you can either omit the optional parameter (instead of having to provide Type.Missing), or you can avoid calling the get accessor completely, and instead reference the property name using square brackets:

// Using C# 3.0:
Excel.Range range = worksheet.get_Range("A1", Type.Missing);

// Using C# 4.0, omitting the optional parameter:
Excel.Range range = worksheet.get_Range("A1");

// Using C# 4.0, utilizing square-bracket indexing:
Excel.Range range = worksheet.Range["A1"];

From what I understand, though, you should still be able to call it the "old way", where the new indexer syntax is really calling the required 'get' and 'set' accessor behind the scenes, so I don't really know why you are having trouble. My guess is that you need to look under Worksheet.Range instead of Worksheet.get_Range within the IntelliSense listing. If this does not work for you, then it sounds like something may be wrong with your setup or installation.

For more on this, see: Indexed Properties in C# 4.0 by Kirill Osenkov.

Hope this helps...

Mike

Answer 2

This was raised as a bug with Microsoft at beta stage https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=530769&wa=wsignin1.0 and was resolved as By Design after RTM. Mike Rosenblum's answer is approved there.

This can be summarised as Excel.Range r = sh.Range[sh.Cells[1, 1], sh.Cells[2, 2]]; where sh is a worksheet.