CODEWITHSUNDEEP
vbaexcel
Nicholas
Nicholas

Reputation: 2668

Why is Object type instead of Sheet type used for variable declaration in the following VBA code?

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

Upvotes: 5

Views: 895

Answers (2)

Comintern
Comintern

Reputation: 22205

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.

Upvotes: 3

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

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.

Upvotes: 6

Related Questions