Menu für den VBA Editor

  • Excel

    Menu für den VBA Editor

    Hallo,

    ich versuche einen Menü aufzubauen (AddIn) für den VBA Editor für Excel und Access mit VB.NET
    Der Grund ist es paar spezielle Funktionen für unsere Firma darin zu Integrieren.
    Könnte mir jemand ein Buch, Tutorial oä. Dafür vorschlagen,
    ich finde nämlich keine Information dafür, außer die von MZ Tools aber, da ich kein Profi bin,
    komme ich einfach nicht weiter,

    Oder vieleich kann mir jemand bei den Fehler Helfen:

    System.ArgumentException: Das System kann den angegebenen Pfad nicht finden.
    bei Microsoft.Office.Core._CommandBars.get_Item(Object Index)
    bei MenuTest.Connect.InitializeAddIn()in …. MenuTest.vb: Zeile159


    Hier der Quellcode:

    VB.NET-Quellcode

    1. Imports Extensibility
    2. Imports System.Windows.Forms
    3. Imports System.Runtime.InteropServices
    4. Imports Microsoft.Vbe.Interop
    5. Imports Microsoft.Office.Core
    6. <ComVisible(True), Guid("e5dc17d9-33e7-4253-87a3-20eabdfc56a9"), ProgId("MenuTest.Connect")>
    7. Public Class Connect
    8. Implements Extensibility.IDTExtensibility2
    9. Private _VBE As VBE
    10. Private _AddIn As AddIn
    11. 'CommandBar
    12. Private WithEvents _myStandardCommandBarButton As CommandBarButton
    13. Private WithEvents _myToolsCommandBarButton As CommandBarButton
    14. Private WithEvents _myCodeWindowCommandBarButton As CommandBarButton
    15. Private WithEvents _myToolBarButton As CommandBarButton
    16. Private WithEvents _myCommandBarPopup1Button As CommandBarButton
    17. Private WithEvents _myCommandBarPopup2Button As CommandBarButton
    18. ' CommandBars created by the add-in
    19. Private _myToolbar As CommandBar
    20. Private _myCommandBarPopup1 As CommandBarPopup
    21. Private _myCommandBarPopup2 As CommandBarPopup
    22. Private Sub OnConnection(Application As Object, ConnectMode As Extensibility.ext_ConnectMode,
    23. AddInInst As Object, ByRef custom As System.Array) Implements IDTExtensibility2.OnConnection
    24. Try
    25. _VBE = DirectCast(Application, VBE)
    26. _AddIn = DirectCast(AddInInst, AddIn)
    27. Select Case ConnectMode
    28. Case Extensibility.ext_ConnectMode.ext_cm_Startup
    29. ' OnStartupComplete will be called
    30. Case Extensibility.ext_ConnectMode.ext_cm_AfterStartup
    31. InitializeAddIn()
    32. End Select
    33. Catch ex As Exception
    34. MessageBox.Show(ex.ToString())
    35. End Try
    36. End Sub
    37. Private Sub OnDisconnection(RemoveMode As Extensibility.ext_DisconnectMode,
    38. ByRef custom As System.Array) Implements IDTExtensibility2.OnDisconnection
    39. Try
    40. Select Case RemoveMode
    41. Case ext_DisconnectMode.ext_dm_HostShutdown, ext_DisconnectMode.ext_dm_UserClosed
    42. ' Delete buttons on built-in commandbars
    43. If Not (_myStandardCommandBarButton Is Nothing) Then
    44. _myStandardCommandBarButton.Delete()
    45. End If
    46. If Not (_myCodeWindowCommandBarButton Is Nothing) Then
    47. _myCodeWindowCommandBarButton.Delete()
    48. End If
    49. If Not (_myToolsCommandBarButton Is Nothing) Then
    50. _myToolsCommandBarButton.Delete()
    51. End If
    52. ' Disconnect event handlers
    53. _myToolBarButton = Nothing
    54. _myCommandBarPopup1Button = Nothing
    55. _myCommandBarPopup2Button = Nothing
    56. ' Delete commandbars created by the add-in
    57. If Not (_myToolbar Is Nothing) Then
    58. _myToolbar.Delete()
    59. End If
    60. If Not (_myCommandBarPopup1 Is Nothing) Then
    61. _myCommandBarPopup1.Delete()
    62. End If
    63. If Not (_myCommandBarPopup2 Is Nothing) Then
    64. _myCommandBarPopup2.Delete()
    65. End If
    66. End Select
    67. Catch e As System.Exception
    68. System.Windows.Forms.MessageBox.Show(e.ToString)
    69. End Try
    70. End Sub
    71. Private Sub OnStartupComplete(ByRef custom As System.Array) _
    72. Implements IDTExtensibility2.OnStartupComplete
    73. InitializeAddIn()
    74. End Sub
    75. Private Sub OnAddInsUpdate(ByRef custom As System.Array) Implements IDTExtensibility2.OnAddInsUpdate
    76. End Sub
    77. Private Sub OnBeginShutdown(ByRef custom As System.Array) Implements IDTExtensibility2.OnBeginShutdown
    78. End Sub
    79. Private Function AddCommandBarButton(ByVal commandBar As CommandBar) As CommandBarButton
    80. Dim commandBarButton As CommandBarButton
    81. Dim commandBarControl As CommandBarControl
    82. commandBarControl = commandBar.Controls.Add(MsoControlType.msoControlButton)
    83. commandBarButton = DirectCast(commandBarControl, CommandBarButton)
    84. commandBarButton.Caption = "My button"
    85. commandBarButton.FaceId = 59
    86. Return commandBarButton
    87. End Function
    88. Private Sub InitializeAddIn()
    89. ' Constants for names of built-in commandbars of the VBA editor
    90. Const STANDARD_COMMANDBAR_NAME As String = "Standard"
    91. Const MENUBAR_COMMANDBAR_NAME As String = "Menu Bar"
    92. Const TOOLS_COMMANDBAR_NAME As String = "Tools"
    93. Const CODE_WINDOW_COMMANDBAR_NAME As String = "Code Window"
    94. ' Constants for names of commandbars created by the add-in
    95. Const MY_COMMANDBAR_POPUP1_NAME As String = "MyTemporaryCommandBarPopup1"
    96. Const MY_COMMANDBAR_POPUP2_NAME As String = "MyTemporaryCommandBarPopup2"
    97. ' Constants for captions of commandbars created by the add-in
    98. Const MY_COMMANDBAR_POPUP1_CAPTION As String = "My sub menu"
    99. Const MY_COMMANDBAR_POPUP2_CAPTION As String = "My main menu"
    100. Const MY_TOOLBAR_CAPTION As String = "My toolbar"
    101. ' Built-in commandbars of the VBA editor
    102. Dim standardCommandBar As CommandBar
    103. Dim menuCommandBar As CommandBar
    104. Dim toolsCommandBar As CommandBar
    105. Dim codeCommandBar As CommandBar
    106. ' Other variables
    107. Dim toolsCommandBarControl As CommandBarControl
    108. Dim position As Integer
    109. Try
    110. ' Retrieve some built-in commandbars
    111. standardCommandBar = _VBE.CommandBars.Item(STANDARD_COMMANDBAR_NAME)
    112. menuCommandBar = _VBE.CommandBars.Item(MENUBAR_COMMANDBAR_NAME)
    113. toolsCommandBar = _VBE.CommandBars.Item(TOOLS_COMMANDBAR_NAME)
    114. codeCommandBar = _VBE.CommandBars.Item(CODE_WINDOW_COMMANDBAR_NAME)
    115. Catch ex As Exception
    116. System.Windows.Forms.MessageBox.Show(ex.ToString)
    117. End Try
    118. MessageBox.Show(_AddIn.ProgId & " loaded in VBA editor version " & _VBE.Version)
    119. End Sub
    120. End Class


    Dank im Vorauß

    Mitrozo