Creating a COM Add-in for the Visual Basic Editor

liucqa

http://msdn.microsoft.com/en-us/library/office/aa189036(v=office.10).aspx

Building COM Add-ins for the Visual Basic EditorOffice 2000
This topic has not yet been rated - Rate this topic

By creating COM add-ins for the Visual Basic Editor, you can customize your development environment and work with components in a VBA project from code. For example, you can build a code wizard that walks a programmer through a series of steps and then builds a procedure. Or you can build a code analyzer that determines how many times and from where a procedure is called. Creating COM add-ins for the Visual Basic Editor is one way to make your own job easier!

When you create a COM add-in for the Visual Basic Editor, it appears in all instances of the Visual Basic Editor. You can't, for example, create a COM add-in that appears only in the Visual Basic Editor in Word; it will also appear in the Visual Basic Editor in Access, Excel, PowerPoint, FrontPage, and any other VBA host applications on the computer where the COM add-in DLL is registered.

Note also that you can create multiple add-ins in a single DLL. Each add-in designer in the add-in project represents a separate add-in. For example, you can create a single DLL that contains a suite of add-ins for developers, and the developers can load just the add-ins they want to use.

To control the Visual Basic Editor from the code inside an add-in, you use the Microsoft Visual Basic for Applications Extensibility 5.3 library. This object library contains objects that represent the parts of a VBA project, such as the VBProject object and the VBComponent object. The top-level object in the VBA Extensibility library object model is the VBE object, which represents the Visual Basic Editor itself. For more information about this object library, use context-sensitive Help (F1) in the Object Browser. For a diagram of its objects, see the http://msdn.microsoft.com/en-us/library/office/cc326919.aspx .

Note   Don't confuse the VBA Extensibility library with the IDTExtensibility2 library. Although their names are similar, the VBA Extensibility library provides objects that you can use to work with the Visual Basic Editor from an add-in while it is running, and the IDTExtensibility2 library provides events that are triggered when the add-in is connected or disconnected. In addition, don't confuse the VBA Extensibility library with the Microsoft Visual Basic 6.0 Extensibility library, which is used for creating add-ins in Microsoft Visual Basic.

To see a sample COM add-in for the Visual Basic Editor, copy the DevTools project from the ODETools\V9\Samples\OPG\Samples\CH11\DevTools subfolder on the Office 2000 Developer CD-ROM to your computer. The DevTools sample project includes two simple add-ins that work with objects in the VBA Extensibility library. The Insert Procedure Template add-in found in this project inserts a new procedure skeleton into a selected code module, complete with scoping, arguments, a return value, and simple error handling. The PathFinder add-in saves you from having to type long file paths — you can use it to navigate to the file you're interested in, and then copy a string containing the file path.

http://msdn.microsoft.com/en-us/library/office/aa164903(v=office.10).aspx

Creating a COM Add-in for the Visual Basic Editor

Office 2000 0 out of 1 rated this helpful - Rate this topic
For the most part, creating a COM add-in for the Visual Basic Editor is similar to creating one for an Office 2000 application. COM add-ins for the Visual Basic Editor also include either the add-in designer or a class module that implements the IDTExtensibility2 library. You can begin with the COM add-in template project.
One key difference to note is that the initial load behavior setting for a COM add-in for the Visual Basic Editor differs from that of a COM add-in for an Office application. A COM add-in for the Visual Basic Editor can have one of two initial load behaviors: None, meaning that the add-in is not loaded until the user loads it, or Startup, meaning that the add-in is loaded when the user opens the Visual Basic Editor.
To create a COM add-in in Visual Basic 6.0 for the Visual Basic Editor
Create a new COM add-in project and modify the add-in designer so that the Application box is set to VBE and the Application Version box is set to VBE 6.0. Set the initial load behavior for the add-in to either None or Startup.

Set a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library. The file Vbe6ext.olb contains this object library; if the object library doesn't appear in the list of available references, it is installed by default in C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6. The name of the library as it appears in the Object Browser is VBIDE.

In the add-in designer's class module, implement the IDTExtensibility2 library as described in "Implementing the IDTExtensibility2 Library" earlier in this chapter. Make sure that each event procedure contains code or a comment.

The OnConnection event procedure passes in the Application argument, which contains a reference to the instance of the Visual Basic Editor in which the add-in is running. You can use this object to work with all other objects in the VBA Extensibility library. To do so, create a public module-level object variable of type VBIDE.VBE, and assign the object referenced by the Application argument to this variable.

Within the OnConnection event procedure, you can optionally include code to hook the add-in's form up to a command bar control in the Visual Basic Editor. You can work with the Visual Basic Editor's command bars by using the CommandBars property of the VBE object.

Build any forms or other components to be included in the project.

Place a breakpoint in the OnConnection event procedure, and then click Start with Full Compile on the Run menu.

In a VBA host application, such as Excel, open the Visual Basic Editor, click Add-in Manager on the Add-ins menu, and select your add-in from the list. Select the Loaded/Unloaded check box to load the add-in, if it's not set to load on startup.

Debug the add-in. When you've debugged it to your satisfaction, end the running project in Visual Basic 6.0, and make the add-in's DLL by clicking Make projectname.dll on the File menu.
You can use the same strategies to distribute COM add-ins for the Visual Basic Editor as you use to distribute COM add-ins for the Office 2000 applications. For more information, see "Distributing COM Add-ins" earlier in this chapter.
Working with the Microsoft Visual Basic for Applications Extensibility 5.3 Library
The VBA extensibility library provides objects that you can use to work with the Visual Basic Editor and any VBA projects that it contains. From an add-in created in Visual Basic 6.0, you can return a reference to the VBE object, the top-level object in the VBA Extensibility library, through the Application argument of the OnConnection event procedure. This argument provides a reference to the instance of the Visual Basic Editor in which the add-in is running.
The VBProject object refers to a VBA project that's open in the Visual Basic Editor. A VBProject object has a VBComponents collection, which in turn contains VBComponent objects. A VBComponent object represents a component in the project, such as a standard module, class module, or form. Because a VBComponent object can represent any of these objects, you can use its Type property to determine which type of module you're currently working with.
For example, suppose you have a variable named vbeCurrent, of type VBIDE.VBE, that represents the instance of the Visual Basic Editor in which the add-in will run. The following code fragment prints the names and types of all components in the active project to the Immediate window:
Dim vbcComp As VBIDE.VBComponent

For Each vbcComp In vbeCurrent.ActiveVBProject.VBComponents
   Debug.Print vbcComp.Name, vbcComp.Type
Next vbcComp
A VBComponent object has a CodeModule property that returns a CodeModule object, which refers to the code module associated with that component. You can use the methods and properties of the CodeModule object to manipulate the code in that module on a line-by-line basis. For example, you can insert lines by using the InsertLines method, or perform find and replace operations by using the Find and Replace methods.
To work with command bars in the Visual Basic Editor, use the CommandBars property of the VBE object to return a reference to the CommandBars collection.
For more information about working with the VBA Extensibility library, search the Visual Basic Reference Help index for "VBProject object."

wqfzqgk

呵呵，资料挺全的

liucqa

HOWTO: Create a toolwindow for the VBA editor of Office from an add-in with Visual Studio .NET.
http://www.mztools.com/articles/2012/MZ2012017.aspx

HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET.
http://www.mztools.com/articles/2012/MZ2012013.aspx

HOWTO: Create a setup for an add-in for the VBA editor of Microsoft Office for the current user (not requiring admin rights) using Inno Setup.
http://www.mztools.com/articles/2012/MZ2012019.aspx


INFO: Registry entries to register an add-in for the VBA editor of Office for the current user without admin rights.
http://www.mztools.com/articles/2012/MZ2012018.aspx

HOWTO: Adding buttons, commandbars and toolbars to the VBA editor of Office from an add-in with Visual Studio .NET.
http://www.mztools.com/articles/2012/MZ2012015.aspx

liucqa

http://www.codeproject.com/Articles/229280/VBAExtend

Download setup file that will install complied application and source code
VBAExtendSetup.zip (822.51 KB, 下载次数: 48)

2013-8-30 19:34 上传

点击文件名下载附件

What this application does:

When you write Visual Basic for application code, this add in will do the following:

• Format the code correctly
• Add line numbers
• Add error handler
• Expose some dot Net library to extend VBA programing
• Expose some dot Net Controls to be used in office or other com application.
• Works With VBA6 and VBA7 for both 32 and 64 bit version of Microsoft office applications
  

What problem is solved

• Visual Studio does not provide a direct wizard to create add ins for VBA, so we should add the registry Key manually.
• Support VBA7 for 64 bit version of Microsoft office applications
  

Requirement

• Microsoft Office 2010 (32 bit or 64 bit)
• Microsoft dot net framework version 2
• Visual Studio Tools for Office
  

How to use this code as VBA/VBE Addin:

• Download the setup file and run it as administrator.
• If registration fail please Run RegisterForComClients.exe file from the application path as administrator.
• Now you can you some dot net control in your MS Access form.
• You can use this application to format you VBA code
• Open an Office document that contains macro and edit the macro in VBA Window it or open Microsoft Access Module
• Write your VBA code
• From Add-Ins menu, choose Add error handler to this file
• This will change the format of your code as follows...
  

Sample of code before applying this add in:

Public Sub Macro1()    MsgBox("This is Macro1")End Sub

Sample of code after applying this add in:

Public Sub Macro1()    On Error GoTo EH11  MsgBox("This is Macro1")    Exit SubEH:    Debug.Print "Error in: Module1.Macro1." & Erl & vbNewLine & Err.Description    'This will print the error Module name and the Error Sub Name and     'line number in the immediate window and this is useful in debugging    Debug.Assert False    'This will stop the execution of the code if you are in debug mode and     'has no effect in run mode    MsgBox "Error in: Module1.Macro1." & Erl & vbNewLine & Err.Description    'This will show a message box about the error in run timeEnd Sub How to use this code to expose some dot Net library to VBA programing:

• Open an Office document that contains macro and edit the macro in VBA Window it or open Microsoft Access Module
• In VBE Window Select Tools, References, Brows then select the file VBAExtend.tlb form the folderVBAExtendBin in the application installing folder.
• Now you could start writing the code using this library.
• Please note that the Class NP is the main class that could be used to access most of other classes.
• Before using this class please insert the following code in a VBA Module
  
  Public NP As New VBAExtend.NP
• After this you could use the NP object in any code in your project
  

Using NP object in your code:

• NP.Clipboard: It is used to access computer clipboard. ex: put or get text from the clipboard with specific format, clear the clipboard or converting the text in it form or to Unicode.
  You could put or get text with the following formats: Text, Unicode text, RTF, HTML.
  
  Sub Test()    NP.Clipboard.Clear    NP.Clipboard.SetText "Some Text"    NP.Clipboard.Ascii2Unicode    NP.Clipboard.Unicode2Ascii    Debug.Print NP.Clipboard.GetTextEnd Sub
• NP.Directory: Create, Delete, Exists, Move
  
  Sub Test()    If Not NP.Directory.Exists("C:\Temp") Then NP.Directory.CreateDirectory "C:\Temp"    NP.Directory.Move "C:\Temp", "C:\Temp2"    NP.Directory.Delete "C:\Temp2"End Sub
• NP.File: Copy, Create, Delete, Exists, Move, Shell.
  
  Sub Test()    If Not NP.File.Exists("C:\Temp.txt") Then NP.File.Create "C:\Temp.txt"    NP.File.Move "C:\Temp.txt", "C:\Temp2.txt"    NP.File.Copy "C:\Temp2.txt", "C:\Temp.txt"    NP.File.Delete "C:\Temp2.txt"    NP.Shell "C:\Temp.txt"End Sub
• NP.Screen: CM, Height, Width, TwipsPerPixelX, TwipsPerPixelY
• NP.Text: EncodingConvert, EncodingConvertByCodePage, GetTextHeight, Md5Hash, TrimAny.
• NP.SQL
  

Using VBADatabase Class:

• This class is used in MS Access.
• 1st: db object should be declared in the same manner as NP Class.
• Second: the default database should be set.
• Then you could start coding:
  Public db As New VBAExtend.VBADatabaseSub Test()    db.SetDB CurrentDB   '...... your codeEnd Sub
• db.IsField
• db.ListFields
• db.IsTableOrQuery
• db.RefreshLinkChooseDB
• SettingDelete, SettingGet, SettingSave: Manage the setting form setting table in this database.
  

Switch off the debug mode when executing Office macro:

• Open an Office document that contains macro and edit the macro in VBA Window it or open Microsoft Access Module
• From Tools menu choose your project properties then choose Protection
• check the Lock project for viewing
• Type a password and click OK
• close your document and reopen it
  

liucqa

http://www.codeproject.com/Articles/310710/VBE-CreateToolWindow

VBE CreateToolWindow
By sam gerene, 6 Jan 2012

   0.00 (No votes)

Introduction
To create a dockable window in the Microsoft Excel VBA Editor, you must make sure that you create a VBE (VBA) add-in and not an Excel add-in. This article quickly shows how to create a new custom window in the VBA editor making use of the CreateToolWindow method.

Background
Many of the articles on the web regarding COM add-ins for Office discuss add-ins for Excel, Word, or Outlook. But not too many talk about creating and add-in for the Visual Basic Editor of Excel. The VBE can be customised as well creating toolbars, menus, and dockable windows. The API can be found here: http://msdn.microsoft.com/en-us/ ... %28v=vs.60%29.aspx.

The CreateToolWindow method can only be used if a true VBE COM add-in is built, and not with an Excel add-in. The CreateToolWindow method creates a new Tool window containing the indicated UserDocument object.

Using the code
Instead of building an add-in for Excel and calling the Run method to force the VBE to initialize from the OnConnection event, you can also build a VBE COM add-in for the VBA Editor. The only difference is the location of the Registry keys with respect to an Excel add-in. The same Registry entries need to be made, but placed in the following location: HKEY_CURRENT_USER/Software/Microsoft/VBA/VBE/6.0/Addins.

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
Option Explicit

Implements AddInDesignerObjects.IDTExtensibility2

Private m_cooldoc As CoolDoc
private m_window as VBIDE.Window

Private Sub IDTExtensibility2_OnAddInsUpdate(custom() As Variant)
'do nothing
End Sub

Private Sub IDTExtensibility2_OnBeginShutdown(custom() As Variant)
'do nothing
End Sub

Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
        ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
        ByVal AddInInst As Object, custom() As Variant)

On Error GoTo errorHandler

Dim app As VBIDE.VBE
Set app = Application

Set m_window = app.Windows.CreateToolWindow(AddInInst, "VBEDemo.CoolDoc", _
                   "My CoolDoc", "anystring",  m_cooldoc)
m_window.Visible = True

errorHandler:
Select Case Err.Number
Case 0
Case Else
    Debug.Print Err.Number & "  " & Err.Description
End Select
End Sub

Private Sub IDTExtensibility2_OnDisconnection(ByVal RemoveMode As _
            AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
'do nothing
End Sub

Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)
'do nothing
End Sub
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!

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:

Collapse | Copy Code
Private Sub IDTExtensibility2_OnConnection(ByVal Application As Object, _
        ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
        ByVal AddInInst As Object, custom() As Variant)

Dim gxlApp as Excel.Application
Set gxlApp = Application

Dim gxCAI as Excel.Addin
Set gxCAI = AddInInst

On Error Resume Next
''' Force the VBE to initialize.
gxlApp.Run "xhYe1lsGtd3soe2t4ns"
On Error GoTo errorHandler

Set m_window = app.Windows.CreateToolWindow(AddInInst, "VBEDemo.CoolDoc",
                   "My CoolDoc", "anystring",  m_cooldoc)
m_window.Visible = True

errorHandler:
Select Case Err.Number
Case 0
Case Else
    Debug.Print Err.Number & " " & Err.Description
End Select
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.

History
First version: 2012-01-05 (yes, some people still use VB6).