Microsoft KB Archive/316653

= PRB: Error Using WithEvents or Delegates to Handle Excel Events from Visual Basic .NET or Visual C# .NET =

Article ID: 316653

Article Last Modified on 1/30/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition
 * Microsoft Visual Studio .NET 2002 Professional Edition

-



This article was previously published under Q316653



SYMPTOMS
When an application tries to handle events from Excel by using WithEvents (in Visual Basic .NET) or Delegates (in Visual Basic .NET or Visual C# .NET), you may receive the following error message:

An unhandled exception of type 'System.InvalidCastException' occurred in interop.excel.dll

Additional information: No such interface supported



CAUSE
The event wrapper classes in the interop assembly that is generated for Excel have access privileges that are too strict. Because these wrapper classes are marked Private, the universal runtime disallows an IUnknown::QueryInterface call to the wrapper classes and returns E_NOINTERFACE. This translates to the exception error that is described in the &quot;Symptoms&quot; section.



RESOLUTION
To work around this problem, you can manually modify the interop assembly that is generated for Excel to relax the access privileges for the event wrapper classes. To do this, follow these steps:  Save and close your Visual Studio .NET project. Open a Visual Studio .NET command prompt and change the directory to the output directory of your project (for example, \bin for a Visual Basic project or  \bin\release for a C# project).  Use Ildasm.exe to extract the intermediate language from the Excel interop assembly, as follows: ildasm.exe /source Interop.Excel.dll /output=Interop.Excel.il                    Open Interop.Excel.il in a text editor such as WordPad and search for occurrences of &quot;_SinkHelper&quot;. Change the access privileges of the _SinkHelper classes from Private to Public, and then save and close Interop.Excel.il.  At the Visual Studio .NET command prompt, use Ilasm.exe with the /dll switch to recompile the intermediate language file into an interop assembly, as follows: ilasm.exe /dll Interop.Excel.il /output=Interop.Excel.dll  Open your project in Visual Studio .NET.</li> Add a reference to the Interop.Excel.dll that you created in step 5. To do this, follow these steps:

IMPORTANT: These steps are required. If you do not set a reference to the new interop assembly, Visual Studio .NET regenerates the interop assembly when you compile the project or create a setup package. <ol style="list-style-type: lower-alpha;"> In Visual Studio .NET Solution Explorer, right-click Excel in the list of project references, and then click Remove.</li> On the Project menu, click Add Reference.</li> On the .NET tab, click Browse, locate the Interop.Excel.dll file in the output directory of your project, and then click Open.</li> Click OK in the Add References dialog box to accept your selection.</li></ol> </li> Test the program again with the modified interop assembly. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> Press F5 to rebuild and to run the program.</li> When Form1 appears, click Button1 to start Excel.</li> Start a new workbook, and then enter data in any cell.</li> On the View menu in Visual Studio .NET, click Other Windows and then click Output. The &quot;Change Event Triggered&quot; text in the Output window confirms that your program handled the event.</li></ol> </li></ol>

<div class="moreinformation_section">

Steps to Reproduce the Problem
<ol> Start Visual Studio .NET.</li> On the File menu, click New, and then click Project.</li> Under Visual Basic Projects, click Windows Application, and then click OK. By default, Form1 is created.</li> Add a reference to the Microsoft Excel Object Library. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> On the Project menu, click Add Reference.</li> On the COM tab, locate Microsoft Excel Object Library, and then click Select.

NOTE: The Excel object library contains a version number. The version for Excel 2000 is 9.0; the version for Excel 2002 is 10.0.</li> <li>Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.</li></ol> </li> <li>Add a command button to Form1.</li> <li>Double-click Button1 to edit the Click event handler.</li> <li> Replace the following code Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

End Sub with: Dim WithEvents oXL As Excel.Application

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click oXL = New Excel.Application oXL.Visible = True oXL.UserControl = True End Sub

Private Sub oXL_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Handles oXL.SheetChange Debug.WriteLine(&quot;Change Event Triggered&quot;) End Sub </li> <li>Press F5 to build and to run the program.</li> <li> After Form1 loads, click Button1. Notice that you receive the error message on the following line: oXL = New Excel.Application </li></ol>

<div class="references_section">