浏览工作表的所有对象(表除外)，并打印其类型 [英] Go through all the objects (except the table) of a worksheet, and print its type

问题描述

我想编写一个VBA程序，该程序分析工作表中除二维数据表之外的所有元素.

I would like to write a VBA program that parses all the elements of a worksheet except the 2-dimensional data table.

这意味着，我想浏览所有图表，按钮，复选框等(如果存在).我希望此列表详尽无遗.所以第一个问题是要进行哪些收集.

That means, I want to go through all the charts, buttons, checkboxs, etc. if they exist. I would like this list to be exhaustive. So the 1st question is what collections to go through.

一个选项是 ActiveSheet.Shapes ，另一个选项是 ActiveSheet.OLEObjects ，我不知道还有什么...谁能告诉我哪一个涵盖一切?

One option is ActiveSheet.Shapes, another option is ActiveSheet.OLEObjects, I don't know if there is anything else... Could anyone tell me which one covers everything?

一旦确定了一个项目，我想确定它是否是图表，按钮，复选框...我意识到以下代码无法实现它:它始终打印 Shape .

Once an item is identified, I want to determine if it is a chart, button, checkbox... I realize that the following code doesn't achieve it: it prints always Shape.

For Each sShape In ActiveSheet.Shapes
    MsgBox TypeName(sShape)
Next

有人知道如何显示商品类型吗?

Does anyone know how to show the type of an item?

推荐答案

从MSDN进行跟进( OLEObject ):

As follow up from MSDN (Shape and OLEObject):

OLEObject对象:

OLEObject Object:

代表工作表上的ActiveX控件或链接或嵌入的OLE对象.

Represents an ActiveX control or a linked or embedded OLE object on a worksheet.

形状:

Shape:

表示绘图层中的对象，例如AutoShape，自由格式，OLE对象或图片.

Represents an object in the drawing layer, such as an AutoShape, freeform, OLE object, or picture.

因此， ActiveSheet.Shapes include ActiveSheet.OLEObjects .

两个集合都不包含表，它们是 ListObjects 集合.

Both collections doesn't include Tables, which are part of ListObjects collection.

因此，您需要 Shapes 集合.以下代码在活动工作表中打印所有形状的类型:

So, you want Shapes collection. Following code prints types of all shapes in active sheet:

Sub test()
    Dim s As Shape

    For Each s In ActiveSheet.Shapes
        Debug.Print "Type: " & s.Type & "  Name: " & s.Name
        If s.Type = msoOLEControlObject Then
            Debug.Print "     OleObject Type: " & TypeName(s.OLEFormat.Object.Object)
        ElseIf s.Type = msoFormControl Then
            Debug.Print "     FormControl Type: " & s.FormControlType
        End If
    Next
End Sub

可能的子程序输出:

Type: 3  Name: Chart 1
Type: 1  Name: Rectangle 2
Type: 12 Name: CheckBox1
      OleObject Type: CheckBox
Type: 8  Name: Button 2
      FormControl Type: 0

注释:

1. s.Type 表示 Shape 对象的类型，并返回
2. 如果 s.Type = msoFormControl ( Form 控件)，则部分 s.FormControlType 返回

1. s.Type represents type of Shape object and returns MsoShapeType Enumeration
2. if s.Type = msoOLEControlObject (OleObject), part TypeName(s.OLEFormat.Object.Object) returns name of OleObject type, like "CheckBox", "Button" and etc.
3. if s.Type = msoFormControl (Form control), part s.FormControlType returns XlFormControl Enumeration

这篇关于浏览工作表的所有对象(表除外)，并打印其类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！