遍历菜单：
Public
Class Connect
Class 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 Connect
Class 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 Sub
End Class
在指定的菜单中的指定的子菜单前添加子菜单
Public Class Connect
Class 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 oMenuBar As Microsoft.Office.Core.CommandBarPopup
oMenuBar = mainMenuBar.Controls("编辑(&E)")
addPosition = oMenuBar.Controls("清除(&A)").Index '直接获得 ［清除］ 菜单的位置
'添加子菜单在最后
subMenu1 = CType(oMenuBar.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
'添加子菜单自制定位置
subMenu1 = CType(oMenuBar.Controls.Add( _
Microsoft.Office.Core.MsoControlType.msoControlButton, Before:=addPosition, 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 Sub
End Class
