Knowledge Base

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 "Symptoms" 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, project name\bin for a Visual Basic project or project name\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 "_SinkHelper". 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.
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.
1. In Visual Studio .NET Solution Explorer, right-click Excel in the list of project references, and then click Remove.
2. On the Project menu, click Add Reference.
3. On the .NET tab, click Browse, locate the Interop.Excel.dll file in the output directory of your project, and then click Open.
4. Click OK in the Add References dialog box to accept your selection.
Test the program again with the modified interop assembly. To do this, follow these steps:
1. Press F5 to rebuild and to run the program.
2. When Form1 appears, click Button1 to start Excel.
3. Start a new workbook, and then enter data in any cell.
4. On the View menu in Visual Studio .NET, click Other Windows and then click Output. The "Change Event Triggered" text in the Output window confirms that your program handled the event.

MORE INFORMATION

Steps to Reproduce the Problem

Start Visual Studio .NET.
On the File menu, click New, and then click Project.
Under Visual Basic Projects, click Windows Application, and then click OK. By default, Form1 is created.
Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
1. On the Project menu, click Add Reference.
2. 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.
3. 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.
Add a command button to Form1.
Double-click Button1 to edit the Click event handler.

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("Change Event Triggered")
    End Sub

Press F5 to build and to run the program.
After Form1 loads, click Button1. Notice that you receive the error message on the following line:
```
oXL = New Excel.Application()
                    
```

REFERENCES

For additional information about handling events in Excel, click the following article numbers to view the articles in the Microsoft Knowledge Base:

302814 HOW TO: Handle Events for Excel by Using Visual Basic .NET

302815 HOW TO: Handle Events for Microsoft Excel by Using Microsoft Visual C# .NET

Keywords: kbautomation kberrmsg kbprb KB316653

Microsoft KB Archive/316653

Contents