为什么在以下VBA代码中将对象类型而不是工作表类型用于变量声明? [英] Why is Object type instead of Sheet type used for variable declaration in the following VBA code?

问题描述

如果激活了 Chart ，下面的代码将使用户返回到旧表，它显示了 Chart 中包含多少个数据点，然后再返回.我不知道为什么在两个事件处理程序过程中将变量 Sh 定义为 Object 而不是 Sheet .与变量 OldSheet 相同.

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

推荐答案

因为Excel中没有表"之类的东西.

请注意，该事件是 SheetActivate ，而不是 WorksheetActivate -工作表"的概念涵盖了几种类型，除了可以激活"之外，没有其他共同点".Excel对象模型中没有 Sheet 类型- Workbook.Sheets 集合包含各种类型的对象，包括 Chart 和 Worksheet 对象.

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.

SheetActivate 事件中的 Sh 参数已成为 Object ，因为没有通用接口在图表和 Worksheet 之间.

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

因此，您需要做的是:验证对象的类型，而不是假设您要处理的是图表或 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.

您应该使用 TypeOf 运算符，而不是使用 TypeName 函数和字符串类型的类型检查，而不是:

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

作为参数 Object 的参数允许将来的版本激活一种类型的工作表"，该类型在编写事件声明时无法实现.每个 Workbook 对象实现的 IWorkbookEvents 隐藏接口.

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.

这篇关于为什么在以下VBA代码中将对象类型而不是工作表类型用于变量声明?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！