Responding to Microsoft Excel events

Add-in Express™ Support Service
That's what is more important than anything else

Responding to Microsoft Excel events
SheetSelectionChange 
Subscribe
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi, Andrei

I am studying Excel Events from your Blog:
Creating Excel 2010, 2007 addin. Customizing Excel Ribbon: C#, VB.NET
SheetActivate Event


Here is the code (converted to VB .NET):

Private Sub AdxExcelAppEvents1_SheetActivate(ByVal sender As System.Object, _
   ByVal hostObj As System.Object) Handles AdxExcelAppEvents1.SheetActivate  '//hostObj

        Dim sheet As Excel.Worksheet = Nothing
        Dim feedbackRange As Excel.Range = Nothing

        Try
            sheet = CType(hostObj, Excel.Worksheet)                          '//hostObj
            feedbackRange = sheet.Range("A1", "A1")
            feedbackRange.Value = "You've activated sheet : " & sheet.Name
        Finally
            If feedbackRange IsNot Nothing Then Marshal.ReleaseComObject(feedbackRange)
        End Try

    End Sub


The 1st line of the Sub says:
Private Sub AdxExcelAppEvents1_SheetActivate(ByVal sender As System.Object, _
      ByVal hostObj As System.Object)                                    '//hostObj


Just after Try, you have this line:
 sheet = CType(hostObj, Excel.Worksheet)                           '//hostObj


hostObj is not defined anywhere, apart from being told it is a System.Object.

So, how can we know it represents something which CAN be CTyped to an Excel.Worksheet?
I've searched the web and also the Object Browser, but did not find an answer.

Thanks
Leon
Posted 19 Mar, 2019 06:39:46 Top
Andrei Smolin


Add-in Express team


Posts: 16223
Joined: 2006-05-11
Hello Leon,

This is a rather awkward area.

First, you get to the description of the event itself: right-click "SheetActivate" (your event handler contains "... Handles AdxExcelAppEvents1.SheetActivate") and choose Go To Definition. Then right-click "ADXHostActiveObject_EventHandler" and choose Go To Definition. Then expand the area containing the declaration of the delegate and see the summary. You are supposed to find "SheetActivate in Excel" in the summary, start the VBA object browser, locate the SheetActivate event, and press F1. This navigates you to https://docs.microsoft.com/en-us/office/vba/api/excel.application.sheetactivate where you learn that the parameter may be Chart or Sheet object.

The description means that code line will produce an exception if the sheet activated is not a Worksheet.


This line

Dim ch As Excel.Chart = CType(hostObj, Excel.Chart)


produces this exception when a worksheet activates:


Exception Type: System.InvalidCastException
Exception Message: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Chart'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D6-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).
Exception Target Site: AdxExcelAppEvents1_SheetActivate


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 20 Mar, 2019 06:23:51 Top
Leon Lai Kan




Posts: 200
Joined: 2018-12-20
Hi, Andrei

Great and very useful information!

Thanks a lot!

Leon
Posted 20 Mar, 2019 06:38:37 Top
Andrei Smolin


Add-in Express team


Posts: 16223
Joined: 2006-05-11
Welcome!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 20 Mar, 2019 06:46:13 Top