Tweet
Step 36. Handling Excel events
Excel can raise events which can be handled by the VB2010 project. An event indicates that something has happened within Excel, and usually originates from an Application, Workbook or Worksheet object.
If you add the WithEvents keyword to the object variable declarations, the events associated with each object will be listed in the left-hand dropdown of the code window. You will see that there are many events. In most cases the name gives a brief description of the event.
Also add the line Msheet = .ActiveSheet to the Load event sub (to make this example work better).
You handle events originating from Excel in the same way as events originating in the VB2010 project itself. For this example a handler for the Excel application’s SheetActivate event is:
This event fires whenever a new worksheet is selected. This allows the variable Msheet to keep track of the currently active worksheet.
Now add a handler for the worksheet’s Change event:
The Change event fires whenever the contents of a cell on the currently-active worksheet changes. The Target parameter is a Range object which references the changed cell. If you manually enter something into a cell in column F, alongside the runner list, this sub should print the selectionId and name of the runner on the same line.
Looks simple, but... if you try it you will probably get this exception when Sub Print executes:
As I mentioned previously, the Excel application executes on a different thread to your VB2010 project. This means that the event handlers we’ve just added also execute on the Excel application’s thread. Textbox tLog (which is used by Sub Print to log messages) belongs to the VB2010 project’s thread and raises the exception if we attempt to access it from Excel’s thread.
There is a quick fix for this problem. Put this statement in you form’s Load event handler:
Here we have changed the name of the existing Print sub to Printx and added a new Sub Print. The Delegate statement is simply a template for Sub Printx, having the same "signature" i.e. number and type of calling parameters.
Now, when Sub Print is called, the InvokeRequired property of the tLog textbox is tested to see if the call is being made on the form’s thread or on a different (i.e. Excel’s) thread. If the call is on a different thread, tLog.InvokeRequired returns True, and the delegate PrintDel is created. This delegate "represents" Sub Printx. The form’s Invoke method is now called using this delegate as an argument. This has the effect of marshalling Printx onto the form’s thread. Note that the Message parameter required by Sub Printx is passed in an Object array.
If the call is on the form’s (i.e. tLog’s) thread then tLog.InvokeRequired returns False, and Sub Printx is called in the usual way.
I hope this makes sense. Multithreading is a complex field with many pitfalls, and I’m no expert.
In a typical VB2010 project your UI will consist of several controls on a form. If you intend updating these controls from event handlers driven from an Excel application, the rule is you must Invoke an updating sub via a Delegate, rather than access the control directly from Excel's thread.
Excel can raise events which can be handled by the VB2010 project. An event indicates that something has happened within Excel, and usually originates from an Application, Workbook or Worksheet object.
If you add the WithEvents keyword to the object variable declarations, the events associated with each object will be listed in the left-hand dropdown of the code window. You will see that there are many events. In most cases the name gives a brief description of the event.
Code:
[COLOR="Gray"]Private [COLOR="Black"]WithEvents[/COLOR] Exl As Application 'A variable for the Excel object
Private [COLOR="Black"]WithEvents[/COLOR] Mbook As Workbook 'A variable for the markets workbook
Private [COLOR="Black"]WithEvents[/COLOR] Msheet As Worksheet 'A variable for the market worksheet
Private Sub ExcelEx_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
........
........
Exl = New Excel.Application 'Launch an instance of Excel
With Exl
.WindowState = XlWindowState.xlNormal
.Visible = True 'Show it
If My.Computer.FileSystem.FileExists(MarketsFile) Then
Mbook = .Workbooks.Open(MarketsFile) 'Open the Excel file if it exists
[COLOR="Black"]Msheet = .ActiveSheet [/COLOR]
End If
End With
End Sub[/COLOR]
You handle events originating from Excel in the same way as events originating in the VB2010 project itself. For this example a handler for the Excel application’s SheetActivate event is:
Code:
[COLOR="Black"]Private Sub Exl_SheetActivate(ByVal Sh As Object) Handles Exl.SheetActivate Msheet = Exl.ActiveSheet 'Msheet refers to the active worksheet End Sub[/COLOR]
Now add a handler for the worksheet’s Change event:
Code:
[COLOR="Black"]Private Sub Msheet_Change(ByVal Target As Excel.Range) Handles Msheet.Change
With Target
If .Column = 6 Then 'A cell in column F has changed
Print(.Offset(0, -4).Value & " " & .Offset(0, -3).Value) 'Print selectionId & name
End If
End With
End Sub[/COLOR]
Looks simple, but... if you try it you will probably get this exception when Sub Print executes:
Cross-thread operation not valid:
Control 'tLog' accessed from a thread other than the thread it was created on.
Control 'tLog' accessed from a thread other than the thread it was created on.
As I mentioned previously, the Excel application executes on a different thread to your VB2010 project. This means that the event handlers we’ve just added also execute on the Excel application’s thread. Textbox tLog (which is used by Sub Print to log messages) belongs to the VB2010 project’s thread and raises the exception if we attempt to access it from Excel’s thread.
There is a quick fix for this problem. Put this statement in you form’s Load event handler:
System.Windows.Forms.TextBox.CheckForIllegalCrossT hreadCalls = False
This turns off the system’s cross-thread checking facility and Sub Msheet_Change should now work. However, this approach is not recommended by the powers that be. Apparently it is a case of two wrongs making a right. The "proper" way of doing it is to declare a Delegate for the Print sub, then invoke this Delegate if calling from a another thread. We now revise the Print sub:Code:
[COLOR="Black"]Delegate Sub PrintDelegate(ByVal Message As String) 'The delegate declaration
Sub Print(ByVal Message As String)
If tLog.InvokeRequired Then 'Call is from another thread
Dim PrintDel As New PrintDelegate(AddressOf Printx) 'Create a delegate for Sub Printx
Invoke(PrintDel, New Object() {Message}) 'Invoke Sub Printx
Else 'Call is from tLog's thread
Printx(Message) 'Call Sub Printx normally
End If
End Sub
[COLOR="Gray"]Sub [COLOR="Black"]Printx[/COLOR](ByVal Message As String) 'The existing Print sub (re-named Printx)
With tLog
.SelectionStart = .Text.Length
.SelectedText = vbCrLf & Message
End With
End Sub[/COLOR][/COLOR]
Now, when Sub Print is called, the InvokeRequired property of the tLog textbox is tested to see if the call is being made on the form’s thread or on a different (i.e. Excel’s) thread. If the call is on a different thread, tLog.InvokeRequired returns True, and the delegate PrintDel is created. This delegate "represents" Sub Printx. The form’s Invoke method is now called using this delegate as an argument. This has the effect of marshalling Printx onto the form’s thread. Note that the Message parameter required by Sub Printx is passed in an Object array.
If the call is on the form’s (i.e. tLog’s) thread then tLog.InvokeRequired returns False, and Sub Printx is called in the usual way.
I hope this makes sense. Multithreading is a complex field with many pitfalls, and I’m no expert.
In a typical VB2010 project your UI will consist of several controls on a form. If you intend updating these controls from event handlers driven from an Excel application, the rule is you must Invoke an updating sub via a Delegate, rather than access the control directly from Excel's thread.
Comment