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



The information in this article applies to:

  • Microsoft Visual Basic .NET (2002)
  • Microsoft Excel 2002

This article was previously published under Q302814
For a Microsoft C# .NET version of this article, see 302815.
For a Microsoft Visual C++ .NET version of this article, see 309301.
For a Microsoft Visual Basic 6.0 version of this article, see 219151.

IN THIS TASK

SUMMARY

This step-by-step article describes how to handle Excel events from an Automation client that is developed with Visual Basic .NET.

back to the top

Overview of Event Handling

You can construct an event handler with Visual Basic .NET in the following two ways, depending on how you want to associate the event handler with events:
  • The standard way to create an event handler is to use the Handles keyword with the WithEvents keyword. When you declare a variable by using the WithEvents keyword, Visual Basic .NET automatically connects to the events of that object at run time. To handle a specific event for that object, add the relevant handler by using the Class and Method lists of the Visual Studio .NET environment while you are in Code view. -or-

  • With the AddHandler keyword, Visual Basic .NET provides a second way to handle events. AddHandler and RemoveHandler allow you to start and stop event handling for a specific event dynamically.
back to the top

Create the Visual Basic .NET Automation Client

The following steps demonstrate how to use either approach to handle Excel events from an Automation client that is developed with Visual Basic .NET.
  1. Start Microsoft Visual Studio .NET. On the File menu, click New and then click Project. Under Visual Basic Projects, select Windows Application. Form1 is created by default.
  2. 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 10.0 Object Library and click Select.NOTE: If you have not already done so, Microsoft recommends that you download and install the Microsoft Office XP Primary Interop Assemblies (PIAs). For additional information about Office XP PIAs, click the article number below to view the article in the Microsoft Knowledge Base:

      328912 INFO: Microsoft Office XP PIAs Are Available for Download

    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.
  3. On the Project menu, select Add Module. Select Module in the list of templates and click Open. Paste the following code in the new module:
       '==================================================================
       'Demonstrates Using a Delegate for Event Handling
       '==================================================================
    
       Private xlApp As Excel.Application
       Private xlBook As Excel.Workbook
       Private xlSheet1 As Excel.Worksheet
       Private xlSheet2 As Excel.Worksheet
       Private xlSheet3 As Excel.Worksheet
       Private EventDel_BeforeBookClose As Excel.AppEvents_WorkbookBeforeCloseEventHandler
       Private EventDel_CellsChange As Excel.DocEvents_ChangeEventHandler
    
       Public Sub UseDelegate()
          'Start Excel and create a new workbook.
          xlApp = CreateObject("Excel.Application")
          xlBook = xlApp.Workbooks.Add()
          xlBook.Windows(1).Caption = "Uses WithEvents"
    
          'Get references to the three worksheets.
          xlSheet1 = xlBook.Worksheets.Item(1)
          xlSheet2 = xlBook.Worksheets.Item(2)
          xlSheet3 = xlBook.Worksheets.Item(3)
          CType(xlSheet1, Excel._Worksheet).Activate()
    
          'Add an event handler for the WorkbookBeforeClose Event of the
          'Application object.
          EventDel_BeforeBookClose = New Excel.AppEvents_WorkbookBeforeCloseEventHandler( _
                AddressOf BeforeBookClose)
          AddHandler xlApp.WorkbookBeforeClose, EventDel_BeforeBookClose
    
          'Add an event handler for the Change event of both Worksheet 
          'objects.
          EventDel_CellsChange = New Excel.DocEvents_ChangeEventHandler( _
                AddressOf CellsChange)
          AddHandler xlSheet1.Change, EventDel_CellsChange
          AddHandler xlSheet2.Change, EventDel_CellsChange
          AddHandler xlSheet3.Change, EventDel_CellsChange
    
          'Make Excel visible and give the user control.
          xlApp.Visible = True
          xlApp.UserControl = True
       End Sub
    
       Private Sub CellsChange(ByVal Target As Excel.Range)
          'This is called when a cell or cells on a worksheet are changed.
          Debug.WriteLine("Delegate: You Changed Cells " + Target.Address + " on " + _
                            Target.Worksheet.Name())
       End Sub
    
       Private Sub BeforeBookClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean)
          'This is called when you choose to close the workbook in Excel.
          'The event handlers are removed, and then the workbook is closed 
          'without saving changes.
          Debug.WriteLine("Delegate: Closing the workbook and removing event handlers.")
          RemoveHandler xlSheet1.Change, EventDel_CellsChange
          RemoveHandler xlSheet2.Change, EventDel_CellsChange
          RemoveHandler xlSheet3.Change, EventDel_CellsChange
          RemoveHandler xlApp.WorkbookBeforeClose, EventDel_BeforeBookClose
          Wb.Saved = True 'Set the dirty flag to true so there is no prompt to save.
       End Sub
    					
  4. Add another module to the project, and then paste the following code in the module:
       '==================================================================
       'Demonstrates Using WithEvents for Event Handling
       '==================================================================
    
       Private WithEvents xlApp As Excel.Application
       Private xlBook As Excel.Workbook
       Private WithEvents xlSheet1 As Excel.Worksheet
       Private WithEvents xlSheet2 As Excel.Worksheet
       Private WithEvents xlSheet3 As Excel.Worksheet
    
       Public Sub UseWithEvents()
          'Start Excel and create a new workbook.
          xlApp = CreateObject("Excel.Application")
          xlBook = xlApp.Workbooks.Add()
          xlBook.Windows(1).Caption = "Uses WithEvents"
    
          'Get references to the three worksheets.
          xlSheet1 = xlBook.Worksheets.Item(1)
          xlSheet2 = xlBook.Worksheets.Item(2)
          xlSheet3 = xlBook.Worksheets.Item(3)
          CType(xlSheet1, Excel._Worksheet).Activate()
    
          'Make Excel visible and give the user control.
          xlApp.Visible = True
          xlApp.UserControl = True
       End Sub
    
       Private Sub xlApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, _
         ByRef Cancel As Boolean) Handles xlApp.WorkbookBeforeClose
          Debug.WriteLine("WithEvents: Closing the workbook.")
          Wb.Saved = True 'Set the dirty flag to true so there is no prompt to save
       End Sub
    
       Private Sub xlSheet1_Change(ByVal Target As Excel.Range) Handles xlSheet1.Change
          Debug.WriteLine("WithEvents: You Changed Cells " + Target.Address + " on Sheet1")
       End Sub
    
       Private Sub xlSheet2_Change(ByVal Target As Excel.Range) Handles xlSheet2.Change
          Debug.WriteLine("WithEvents: You Changed Cells " + Target.Address + " on Sheet2")
       End Sub
    					
  5. Add the following to the top of Module1.vb:
    Imports Microsoft.Office.Interop
    					
  6. Add the following to the top of Module2.vb:
    Imports Microsoft.Office.Interop
    					
  7. In Solution Explorer, double-click Form1.vb to display the form in Design view.
  8. On the View menu, select Toolbox to display the Toolbox, and then add two buttons to Form1. Change the Text property of Button1 to Use WithEvents, and then change the Text property of Button2 to Use Delegates.
  9. On the View menu, select Code to display the code window for the form. Add the following code to the Click event handlers for the buttons:
    
        Private Sub Button1_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles Button1.Click
            UseWithEvents()
        End Sub
    
        Private Sub Button2_Click(ByVal sender As System.Object, _
          ByVal e As System.EventArgs) Handles Button2.Click
            UseDelegate()
        End Sub
    					
back to the top

Test the Code

  1. Press CTRL+ALT+O to display the Output window.
  2. Press F5 to build and run the program.
  3. On the form, click Use WithEvents. The program starts Excel and creates a workbook with three worksheets.
  4. Add any data to cells on one or more worksheets. Press the ENTER key after each change. Examine the Output window in Visual Studio to verify that the event handlers are called.
  5. Quit Excel.
  6. On the form, click Use Delegate. Again, the program starts Excel and creates a workbook with multiple worksheets.
  7. Add any data to cells on one or more worksheets. Press the ENTER key after each change. Examine the Output window in Visual Studio to verify that the event handlers are called.
  8. Quit Excel and close the form to end the debug session.
back to the top

Troubleshooting

When you test the code, 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
If you receive this error, see the following article in the Microsoft Knowledge Base:

316653 PRB: Error using WithEvents or Delegates to handle events from Microsoft Excel in Visual Studio.NET

back to the top

REFERENCES

For more information, see the following Microsoft Developer Network (MSDN) Web site:

Microsoft Office Development with Visual Studio
http://msdn.microsoft.com/library/en-us/dnoxpta/html/vsofficedev.asp

For additional information about automating Excel from Visual Basic .NET, click the article numbers below to view the articles in the Microsoft Knowledge Base:

301982 HOWTO: Automate Microsoft Excel from Visual Basic .NET

302094 HOWTO: Automate Excel From Visual Basic .Net To Fill or Obtain Data In a Range Using Arrays

back to the top

(c) Microsoft Corporation 2001, All Rights Reserved. Contributions by Joel Alley, Microsoft Corporation.


Modification Type:MajorLast Reviewed:1/19/2006
Keywords:kbAutomation kbHOWTOmaster KB302814 kbAudDeveloper