Why is an object type instead of a sheet type used to declare a variable in the following VBA code?

The following code returns the user to the old sheet if activated Chart, and it shows how many data points are included in Chartbefore returning. And I wonder why a variable Shis defined as Object, rather than Sheetin two event handler procedures. The same goes 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
+4
source share
2 answers

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

, SheetActivate, WorksheetActivate - "" , , "". Excel Sheet - Workbook.Sheets , Chart Worksheet.

Sh SheetActivate Object, Chart a Worksheet.

, , : , , Chart Worksheet.

TypeName , , , TypeOf:

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

Object "" , IWorkbookEvents, Workbook.

+5

, Object. "" COM- IDispatch ( VBA Object) , . ByVal Sh As Object , , . Excel dispinterface WorkbookEvents :

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

COM- , Object, Sheets Worksheet Chart, , . , . , (IDispatch). , , , .

+3

Source: https://habr.com/ru/post/1670764/


All Articles