如何用vba返回excel所有的菜单命令栏控件的id、标题、类型等信息？

在如何用vba代码返回excel应用程序中的所有内置菜单命令栏的名称? 一文中我们介绍了如何用vba返回excel应用程序的所有内置菜单命令栏的名称。

今天我们介绍如何用vba返回excel所有的菜单命令栏控件的id、标题、类型等信息。

在vba中CommandBar对象表示excel中的菜单对象，CommandBarControl对象表示菜单中的控件对象。其中Controls控件集合对象是CommandBar对象的属性，也就是如果要遍历每个控件，首先要遍历每个CommandBar对象，然后通过Controls属性遍历每个控件。

代码如下：

Sub QQ1722187970()
    Excel.Application.ScreenUpdating = False
    Dim oCB As CommandBar
    Dim oCBC As CommandBarControl
    Dim oWK As Worksheet
    Set oWK = ActiveSheet
    oWK.Cells.Clear
    Dim arr
    Dim iCol As Integer
    arr = VBA.Array("菜单英文名称", "菜单中文名称", "菜单内的控件ID", "菜单内的控件标题", "菜单内的控件类型")
    iCol = UBound(arr) + 1
    oWK.Range("a1").Resize(1, iCol) = arr
    i = 2
    For Each oCB In Excel.Application.CommandBars
        '遍历每个菜单栏
        With oCB
            For Each oCBC In .Controls
                '遍历每个控件
                sCBName = .Name
                sCBNameLocal = .NameLocal
                With oCBC
                    '控件id
                    sID = .ID
                    '控件标题
                    sCBCName = .Caption
                    '控件类型
                    iType = .Type
                End With
                    With oWK
                        .Cells(i, 1) = sCBName
                        .Cells(i, 2) = sCBNameLocal
                        .Cells(i, 3) = sID
                        .Cells(i, 4) = sCBCName
                        .Cells(i, 5) = iType
                        i = i + 1
                    End With
            Next
        End With
    Next
    oWK.Columns.AutoFit
    Excel.Application.ScreenUpdating = True
End Sub

其中控件的类型可以参考MsoControlType枚举对照。

运行程序后的结果如下：