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
https://www.add-in-express.com/creating-addins-blog/2012/05/29/write-addins-excel2010/
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.
red
I've searched the web and also the Object Browser, but did not find an answer.
Thanks
Leon |
|
Andrei Smolin
Add-in Express team
Posts: 18794
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
Andrei Smolin
Add-in Express Team Leader |
|
Leon Lai Kan
Posts: 200
Joined: 2018-12-20
|
Hi, Andrei
Great and very useful information!
Thanks a lot!
Leon |
|
Andrei Smolin
Add-in Express team
Posts: 18794
Joined: 2006-05-11
|
Welcome!
Andrei Smolin
Add-in Express Team Leader |
|