4

The following code gets user back to the old sheet if a Chart is activated, and it shows how many data points are included in the Chart before getting back. And I wonder why the variable Sh is defined as Object rather than Sheet in the two event-handler procedures. Same for the variable OldSheet. 

Dim OldSheet As Object

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set OldSheet = Sh
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim Msg As String
    If TypeName(Sh) = "Chart" Then
        Msg = "This chart contains "
        Msg = Msg & ActiveChart.SeriesCollection(1).Points.Count
        Msg = Msg & " data points." & vbNewLine
        Msg = Msg & "Click OK to return to " & OldSheet.Name
        MsgBox Msg
        OldSheet.Activate
    End If
End Sub
Nicholas
  • 2,560
  • 2
  • 31
  • 58
  • It looks lke use of late binding to me. Normally when interacting with different object models, you use references or you use late binding. I would actually find a good article and brush up on this b/c late binding is something fairly useful from time to time. check this link http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/ – Doug Coats Feb 24 '17 at 15:34
  • It may be sloppy @Wayne, but it's what Excel inserts when you create the event procedure. I'm wondering if it's a throwback to earlier spreadsheets when `Sh` may not just be a `Sheet`, a `WorkSheet` or a `ChartSheet`, it could've been a macro sheet. – Darren Bartrup-Cook Feb 24 '17 at 15:44
  • 1
    @WayneG.Dunn it's not any more "sloppy" than any standard .net event handler signature - `(object sender, EventArgs e)`; that `object sender` is *exactly* what `Sh As Object` is here. – Mathieu Guindon Feb 24 '17 at 16:07
  • 1
    This is also fairly standard practice in the COM world. There is absolutely zero requirement that all of the callable members of a object are declared on its interfaces. Passing an `IDispatch` (VBA's `Object`) allows the callback function to query the interface for specific members that it wants to call. It would be more like a default .NET event handler signature of `(dynamic sender, EventArgs e)`. – Comintern Feb 24 '17 at 16:10

2 Answers2

5

Because there is no such thing as a 'Sheet' in Excel.

Notice the event is SheetActivate, not WorksheetActivate - the concept of a "sheet" encompasses several types that have nothing in common, other than the ability to be "activated". There is no Sheet type in the Excel object model - the Workbook.Sheets collection contains various types of objects, including Chart and Worksheet objects.

The Sh parameter in the SheetActivate event has to be an Object, because there is no common interface between a Chart and a Worksheet.

So you need do what you did: verify the type of the object instead of assuming you're dealing with a Chart or a Worksheet object.

Instead of using the TypeName function and thus stringly-typed type checks, you should use the TypeOf operator instead:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If TypeOf Sh Is Excel.Worksheet Then
        Debug.Print "Worksheet!"
    ElseIf TypeOf Sh Is Excel.Chart Then
        Debug.Print "Chart!"
    Else
        Debug.Print "Something else!"
    End If
End Sub

The parameter being Object allows future versions to activate a "sheet" of a type that couldn't be dreamed of at the time the event declaration was written in the IWorkbookEvents hidden interface that every Workbook object implements.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Appreciated. I wonder if it makes any difference here to use Variant instead of Object. They seem both "indeterminate" now. – Nicholas Feb 24 '17 at 16:01
  • @Nicholas `Variant` would have worked too (well except you can't use it there yourself because then the signature will mismatch the event interface definition), but `Object` is much more specific - an `Object` can only ever contain an object reference, whereas a `Variant` can be *anything*, from a `String` literal to an `IUnknown` object reference. – Mathieu Guindon Feb 24 '17 at 16:03
  • I see, one last question, you consider `TypeOf` better than `TypeName` here, is this due to the fact that `TypeOf` is faster? – Nicholas Feb 24 '17 at 16:08
  • 1
    @Nicholas no, it's because `TypeOf` is making the type check *strongly-typed* - nothing forbids having your own `Worksheet` class module. If `TypeName(Sh)` returns a `"Worksheet"` string literal, how do you know if that's `Excel.Worksheet` or `VBAProject.Worksheet`? You can't. With `TypeOf Sh Is Excel.Worksheet` there is no possible ambiguity (or imagine checking for a `Range` in Excel when you're referencing the Word object library, which *also* has a `Range` type). It's probably a bit faster too, since there's no string creation and comparison involved. – Mathieu Guindon Feb 24 '17 at 16:10
  • 2
    FWIW, `TypeOf` is *also* faster. ;-) – Comintern Feb 24 '17 at 16:19
3

The short version of the answer is that it has to be declared as Object. The events are being "fired" through a COM source sink, and that returns an IDispatch pointer (known in VBA as Object) to anything that has a subscribed callback function. The ByVal Sh As Object parameter is passed to the callback function so that the event handler can determine which object was responsible for raising the event. It's declared in the Excel type library on dispinterface WorkbookEvents like this:

[id(0x00000619), helpcontext(0x0007ad30)]
void SheetActivate([in] IDispatch* Sh);

Even without considering the COM plumbing of its implementation, it has to be declared as Object because the Sheets collection holds both Worksheet and Chart objects, and the event will fire if either type of tab is activated. The two types don't share a common interface, but they do both source the same event. That means in order to pass the source object to the event handler, it has to be passed as late bound (IDispatch). The assumption is that the handler will determine what type of object it was passed and take the appropriate action based on the type of the sender.

Comintern
  • 21,855
  • 5
  • 33
  • 80