VBE 小插件--- Office 编程助手。

liucqa

How this addin work with VBA7 and office 64 bit:

    In Office 64 bit the VBAAddin class is not called by the COM.
    The Office 64 bit called another class named SharedOfficeAddin
    VBA7 is not support direct addins so this project act as a shared office application add in
    The VBE is accessed form the host application
    In SharedOfficeAddin Class and on OnConnection method we define the host application and call the another method for initializing the VBA add in
    To access VBE form application class a register AccessVBOM value should be set to 1
    The key is under local machine\Software\Microsoft\Office\14.0\Office application name\Security
    Collapse | Copy Code

    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As ext_ConnectMode, _
                            ByVal addInInst As Object, ByRef custom As Array) Implements IDTExtensibility2.OnConnection
        Try
            applicationObject = application
            addInInstance = addInInst
            '......
            Dim _VBE = applicationObject.VBE

            If _VBE.Version >= "7" Then
                If _VBAAddin Is Nothing Then _VBAAddin = New VBAAddin
                _VBAAddin.OnConnection(_VBE)
            End If

        Catch ex As Exception
        End Try
    End Sub

liucqa

The VB6 project

Create a new ActiveX DLL project in VB6 and name it VBEDemo and add the following references to the project:

    Microsoft Add-In Designer
    Microsoft Visual Basic for Applications Extensibility 5.3
    Microsoft Excel 14.0 Object Library
    Microsoft Office 14.0 Object Library

Add a class called VBEConnect to the project and implement the IDTExtensibility2 interface in this class. Add a User Document to the project and name it CoolDoc, this User Document will be hosted in the new Tool window in the VBE. Declare two private variables at the top of the class, m_cooldoc as CoolDoc and m_window as VBIDE.Window. The m_window object will hold a reference to the Tool window that we are about to create. The VBEConnect class should now look something like this:
Collapse | Copy Code

1. Option Explicit
   
2. 
   
3. Implements AddInDesignerObjects.IDTExtensibility2
   
4. 
   
5. Private m_cooldoc As CoolDoc
   
6. private m_window as VBIDE.Window
   
7. 
   
8. Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
   
9. 'do nothing
   
10. End Sub
    
11. 
    
12. Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
    
13. 'do nothing
    
14. End Sub
    
15. 
    
16. Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
    
17.         ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    
18.         ByVal AddInInst As Object, custom() As Variant)
    
19. 
    
20. On Error GoTo errorHandler
    
21. 
    
22. Dim app As VBIDE.VBE
    
23. Set app = Application
    
24. 
    
25. Set m_window = app.Windows.CreateToolWindow(AddInInst, "VBEDemo.CoolDoc", _
    
26.                    "My CoolDoc", "anystring",  m_cooldoc)
    
27. m_window.Visible = True
    
28. 
    
29. errorHandler:
    
30. Select Case Err.Number
    
31. Case 0
    
32. Case Else
    
33.     Debug.Print Err.Number & "  " & Err.Description
    
34. End Select
    
35. End Sub
    
36. 
    
37. Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As _
    
38.             AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
    
39. 'do nothing
    
40. End Sub
    
41. 
    
42. Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
    
43. 'do nothing
    
44. End Sub
    
45. 
    
46. The magic happens in the OnConnection event. Here a VBIDE.VBE application object is declared that is used to call the CreateToolWindow method. The method takes the current add-in and User Document as arguments and this is where things go wrong if an Excel COM add-in is created. The AddInInst object must be a VBE add-in and not an Excel add-in!
    
47. 
    
48. In a previous project (an Excel add-in), I forced the VBE to start using the Run method and tried to execute CreateToolWindow, but that did not work. Just to show the difference, here is the code that does not work:
    
49. Collapse | Copy Code
    
50. 
    
51. Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
    
52.         ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
    
53.         ByVal AddInInst As Object, custom() As Variant)
    
54. 
    
55. Dim gxlApp as Excel.Application
    
56. Set gxlApp = Application
    
57. 
    
58. Dim gxCAI as Excel.Addin
    
59. Set gxCAI = AddInInst
    
60. 
    
61. On Error Resume Next
    
62. ''' Force the VBE to initialize.
    
63. gxlApp.Run "xhYe1lsGtd3soe2t4ns"
    
64. On Error GoTo errorHandler
    
65. 
    
66. Set m_window = app.Windows.CreateToolWindow(AddInInst, "VBEDemo.CoolDoc",
    
67.                    "My CoolDoc", "anystring",  m_cooldoc)
    
68. m_window.Visible = True
    
69. 
    
70. errorHandler:
    
71. Select Case Err.Number
    
72. Case 0
    
73. Case Else
    
74.     Debug.Print Err.Number & " " & Err.Description
    
75. End Select
    
76. End Sub

复制代码

Running the code

Before the add-in will work, it is necessary to compile the DLL and add it to the Registry using the regsvr32.exe command. The proper keys need to be created in the Windows Registry in the following location: HKEY_CURRENT_USER/Software/Microsoft/VBA/VBE/6.0/Addins.

The new key must be named after the project name and the class that is implementing the IDTExtensibility2 interface: VBEDemo.VBEConnect. The key needs to contain two DWORDs and two string values:

    DWORD: CommandLineSafe | 0
    DWORD: LoadBehavior | 3
    String value: FriendlyName | VBEDemoToolWindow
    String value: Description | A Tool Window VBE Addin Demo

When the Excel VBA editor is started, the add-in will load and the Tool window will appear. Of course, this does not do anything since no functionality has been added, but I had a grin from ear to ear having spent some hours searching the web to find out how to do it (without success if I may add) and reverting to trial and error. I hope this little super simple article may save other people a couple of hours.

yf_home

liucqa 发表于 2013-9-13 00:43
这种东西，可以考虑开源嘛，国外都有类似的介绍，也没有保密的技术和商业价值。用VB写Addins的代码很多，开 ...

支持一下，也希望楼主开源，谢谢。

yf_home

使用情况反馈，检测到一处错误，应该是手误吧，见下图：





，，，

yf_home

继续，反馈第2个问题（测试环境: 繁体XP+繁体OFFICE2010+繁体office2003），见图：
     


、、

东亚五金

谢谢楼主分享,2013也可以用

lbpp

win7 64bit+office2013 32bit 不能运行。

geryjun

看起来非常不错.谢谢分享.

Moneky

谢谢楼主分享，给个建议，智能缩进可以考虑放在第一级菜单，类似搜索功能，把常用的放在第一级

crazy0qwer

感谢大家关注。

TO 19F

智能缩进可以考虑放在第一级菜单，类似搜索功能，把常用的放在第一级

因为每个人的习惯不一样，所以常用的功能也因人而异。我自己感觉是没有哪个功能是大家都会用到的。
其实我的想法是， 统计各个菜单的使用率。设置一个菜单来动态显示使用率较高的几个菜单。

TO 17F

win7 64bit+office2013 32bit 不能运行。

win7 64位系统的问题，现在我还没有这个系统镜像，有空我下载安装后再测试看看。

TO 16F   

谢谢楼主分享,2013也可以用

感谢反馈

TO 15F   
这个是系统原因了，因为有些字无法识别。至于解决办法，我的想法是另外做一个版本，用繁体字。
不过我看你的截图，好像不是完全的繁体？

TO 14F
这个丑出大了。。。

TO 6F

还是像类似MZTools3那样，做个工具条吧，选菜单累死了

因为菜单比较多，如果用工具条的话，就只能用图标来识别(当然，鼠标放上去可以提示，但是那样感觉比右键更麻烦)
能力有限，很多菜单都不知道用什么样的图标比较能让人一看就知道功能。
我的想法还是统计菜单使用率，然后看看把使用率较高的菜单找个方便的方式列出来。

TO ALL
源码暂时先不发。因为还是测试阶段，代码比较乱，也比较多错误。
其实除了几个API外，基本上都是用VB的基本函数处理的，主要是思路而已。

周末了，可能会到周六晚上或者周日再更新。