编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

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

M Reddy 面向对象 2022-5-11 10:38 5人围观

腾讯云服务器
为什么在以下VBA代码中将对象类型而不是工作表类型用于变量声明?的处理方法

如果激活了 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代码中将对象类型而不是工作表类型用于变量声明?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^