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

1. Save and close your Visual Studio .NET project.
2. 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).
3. 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
   					

4. 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.
5. 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
   					

6. Open your project in Visual Studio .NET.
7. 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.
8. 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.

Back to the top

Steps to Reproduce the Problem

1. Start Visual Studio .NET.
2. On the File menu, click New, and then click Project.
3. Under Visual Basic Projects, click Windows Application, and then click OK. By default, Form1 is created.
4. 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.
5. Add a command button to Form1.
6. Double-click Button1 to edit the Click event handler.
7. 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
   					

8. Press F5 to build and to run the program.
9. After Form1 loads, click Button1. Notice that you receive the error message on the following line:

   oXL = New Excel.Application()
   					

Back to the top