遍历菜单：
Public Class ConnectClass Connect
Implements Extensibility.IDTExtensibility2 Dim app As Excel.Application Dim addInInstance As Object Dim mainMenuBar As Microsoft.Office.Core.CommandBar Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup Dim subMenu1 As Microsoft.Office.Core.CommandBarButton Dim addPosition As Integer
Public Sub OnConnection()Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection app = CType(application, Excel.Application) addInInstance = addInInst addMenu()
End Sub Public Sub addMenu()Sub addMenu() Dim wb As Excel.Workbook If app.Workbooks.Count > 0 Then wb = app.ActiveWorkbook Else wb = app.Workbooks.Add End If Dim ws As Excel.Worksheet If wb.Worksheets.Count > 0 Then ws = wb.ActiveSheet Else ws = wb.Worksheets.Add End If mainMenuBar = CType(app.CommandBars.ActiveMenuBar, Microsoft.Office.Core.CommandBar) Dim i As Long Dim j As Long Dim k As Long = 1 ws.Range("A1").Select() app.ActiveCell.Value = mainMenuBar.Name ws.Range("B1").Select() app.ActiveCell.Value = mainMenuBar.NameLocal.ToString Dim oMenuBar As Microsoft.Office.Core.CommandBarPopup For i = 1 To mainMenuBar.Controls.Count ws.Range(Microsoft.VisualBasic.Chr(Microsoft.VisualBasic.Asc("A") + (i - 1)) & "3").Select() app.ActiveCell.Value = mainMenuBar.Controls(i).Caption.ToString() oMenuBar = CType(mainMenuBar.Controls(i), Microsoft.Office.Core.CommandBarPopup) For j = 1 To oMenuBar.Controls.Count ws.Range(Microsoft.VisualBasic.Chr(Microsoft.VisualBasic.Asc("A") + (i - 1)) & (j + 4).ToString).Select() app.ActiveCell.Value = oMenuBar.Controls(j).Caption k = k + 1 Next Next End Sub
End Class在指定的菜单前添加菜单
Public Class ConnectClass Connect Implements Extensibility.IDTExtensibility2 Dim app As Excel.Application Dim addInInstance As Object Dim mainMenuBar As Microsoft.Office.Core.CommandBar Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup Dim subMenu1 As Microsoft.Office.Core.CommandBarButton Dim addPosition As Integer Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown End Sub Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate End Sub Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete End Sub Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection End Sub Public Sub OnConnection()Sub OnConnection(ByVal application As Object, ByVal connectMode As Extensibility.ext_ConnectMode, ByVal addInInst As Object, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnConnection app = CType(application, Excel.Application) addInInstance = addInInst addMenu() End Sub Public Sub addMenu()Sub addMenu() Dim wb As Excel.Workbook If app.Workbooks.Count > 0 Then wb = app.ActiveWorkbook Else wb = app.Workbooks.Add End If Dim ws As Excel.Worksheet If wb.Worksheets.Count > 0 Then ws = wb.ActiveSheet Else ws = wb.Worksheets.Add End If mainMenuBar = CType(app.CommandBars.ActiveMenuBar, Microsoft.Office.Core.CommandBar) Dim i As Long Dim j As Long Dim k As Long = 1 '下面注释的代码 是 利用的遍历的方式找到 ［编辑］ 菜单 'For i = 1 To mainMenuBar.Controls.Count ' If mainMenuBar.Controls(i).Caption.ToString().IndexOf("编辑") >= 0 Then ' addPosition = i ' Exit For ' End If 'Next addPosition = mainMenuBar.Controls("编辑(&E)").Index '直接获得 ［编辑］ 菜单的位置 newEntryBar = CType(mainMenuBar.Controls.Add( _ Microsoft.Office.Core.MsoControlType.msoControlPopup, Before:=addPosition, Temporary:=True), Microsoft.Office.Core.CommandBarPopup) newEntryBar.Caption = "我的菜单(&A)" newEntryBar.Visible = True newEntryBar.Enabled = True subMenu1 = CType(newEntryBar.Controls.Add( _ Microsoft.Office.Core.MsoControlType.msoControlButton, Temporary:=True), _ Microsoft.Office.Core.CommandBarButton) subMenu1.Caption = "我是谁(&Z)" subMenu1.Visible = True subMenu1.Enabled = True AddHandler subMenu1.Click, AddressOf Me.subMenu1Command_Click End Sub Private Sub subMenu1Command_Click()Sub subMenu1Command_Click(ByVal Ctrl As Microsoft.Office.Core.CommandBarButton, ByRef CancelDefault As Boolean) app.ActiveCell.Value = "我是第 " & app.ActiveCell.Row.ToString() & " 行,第 " & app.ActiveCell.Column.ToString & " 列" End SubEnd Class
在指定的菜单中的指定的子菜单前添加子菜单
Public Class ConnectClass Connect Implements Extensibility.IDTExtensibility2 Dim app As Excel.Application Dim addInInstance As Object Dim mainMenuBar As Microsoft.Office.Core.CommandBar Dim newEntryBar As Microsoft.Office.Core.CommandBarPopup Dim subMenu1 As Microsoft.Office.Core.CommandBarButton Dim addPosition As Integer Public Sub OnBeginShutdown()Sub OnBeginShutdown(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnBeginShutdown End Sub Public Sub OnAddInsUpdate()Sub OnAddInsUpdate(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnAddInsUpdate End Sub Public Sub OnStartupComplete()Sub OnStartupComplete(ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnStartupComplete End Sub Public Sub OnDisconnection()Sub OnDisconnection(ByVal RemoveMode As Extensibility.ext_DisconnectMode, ByRef custom As System.Array) Implements Extensibility.IDTExtensibility2.OnDisconnection End Sub