Build add-in for VBA IDE using VB.NET

Question

I have asked this elsewhere, but have never found anyone knows how to build an add-in for VBA IDE using VB.NET. Is it even possible? Could someone point me to an example?

Answer 1

It is possible you need to write a com addin using IDTExtensibility2 interface, select the shared addin project template from new project.

EDIT

Otherwise to create this addin from scratch you will need to do the following:

1. Create a new project class library
2. Add references to "Extensibility", it should be in the list. You may need to download the PIAs for your version of office. (and perhaps VSTO but i am unsure on this point)
3. Add references to "Microsoft.Vbe.Interop" again should be with the PIAs.
4. Check the box "Register for Com Interop" in the properties tab.
5. OPTIONAL In the debug settings tab change the startup to external program and enter the path to the excel exe in the programfiles folder (if this is intended for excel) this is to allow the project to be debuggable.
6. OPTIONAL In the command options add a entry to a worksheet, or word doc that will show the addin dialog using a macro on startup, for development this makes sense to streamline the debugging experience. eg "C:\vbe.xlsm"
7. OPTIONAL Also set the startup path to the worksheet directory eg "C:\"
8. Implement the interface "IDTExtensibility2" found in "Extensibility" assembly.
9. Call this class "Connect" (this is just a preference)
10. Attribute the class with the following

[ComVisible(true), Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")]

Heres an implementation to get you started, Firstly replace the "YourAddinName" with your AppName and Create a Guid for "YourGeneratedGuid". You will need to register the Addin into the right Registry location, see the registry keys that follow to get an idea, also replace some vars in the registry keys.

Imports System
Imports System.Drawing
Imports System.Linq
Imports System.Runtime.InteropServices
Imports Extensibility
Imports Microsoft.Vbe.Interop

Namespace VBEAddin


''' <summary>
''' The object for implementing an Add-in.
''' </summary>
''' <seealso class='IDTExtensibility2' />
<Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")> _ 
Public Class Connect
    Implements IDTExtensibility2
    Private _application As VBE 'Interop VBE application object


    #Region "IDTExtensibility2 Members"

    ''' <summary>
    ''' Implements the OnConnection method of the IDTExtensibility2 interface.
    ''' Receives notification that the Add-in is being loaded.
    ''' </summary>
    ''' <param term='application'>
    ''' Root object of the host application.
    ''' </param>
    ''' <param term='connectMode'>
    ''' Describes how the Add-in is being loaded.
    ''' </param>
    ''' <param term='addInInst'>
    ''' Object representing this Add-in.
    ''' </param>
    ''' <seealso class='IDTExtensibility2' />
    Public Sub OnConnection(ByVal application As Object, ByVal connectMode As ext_ConnectMode, ByVal addInInst As Object, ByRef [custom] As Array)
    _application = CType(Application,VBE)
    End Sub

    Private Sub onReferenceItemAdded(ByVal reference As Reference)
        'TODO: Map types found in assembly using reference.
    End Sub

    Private Sub onReferenceItemRemoved(ByVal reference As Reference)
        'TODO: Remove types found in assembly using reference.
    End Sub


    Private Sub BootAddin()
        'Detect change in active window. 
    End Sub

    ''' <summary>
    ''' Implements the OnDisconnection method of the IDTExtensibility2 interface.
    ''' Receives notification that the Add-in is being unloaded.
    ''' </summary>
    ''' <param term='disconnectMode'>
    ''' Describes how the Add-in is being unloaded.
    ''' </param>
    ''' <param term='custom'>
    ''' Array of parameters that are host application specific.
    ''' </param>
    ''' <seealso class='IDTExtensibility2' />
    Public Sub OnDisconnection(ByVal disconnectMode As ext_DisconnectMode, ByRef [custom] As Array)
    End Sub

    ''' <summary>
    ''' Implements the OnAddInsUpdate method of the IDTExtensibility2 interface.
    ''' Receives notification that the collection of Add-ins has changed.
    ''' </summary>
    ''' <param term='custom'>
    ''' Array of parameters that are host application specific.
    ''' </param>
    ''' <seealso class='IDTExtensibility2' />
    Public Sub OnAddInsUpdate(ByRef [custom] As Array)
    End Sub

    ''' <summary>
    ''' Implements the OnStartupComplete method of the IDTExtensibility2 interface.
    ''' Receives notification that the host application has completed loading.
    ''' </summary>
    ''' <param term='custom'>
    ''' Array of parameters that are host application specific.
    ''' </param>
    ''' <seealso class='IDTExtensibility2' />
    Public Sub OnStartupComplete(ByRef [custom] As Array)
        'Boot dispatcher

    End Sub


    ''' <summary>
    ''' Implements the OnBeginShutdown method of the IDTExtensibility2 interface.
    ''' Receives notification that the host application is being unloaded.
    ''' </summary>
    ''' <param term='custom'>
    ''' Array of parameters that are host application specific.
    ''' </param>
    ''' <seealso class='IDTExtensibility2' />
    Public Sub OnBeginShutdown(ByRef [custom] As Array)
    End Sub

    #End Region
End Class
End Namespace

Here is the script to register the Addin, note you will need to change some of the settings in order to register it properly.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\YourAddinName.Connect]
"CommandLineSafe"=dword:00000000
"Description"="Description for your new addin"
"LoadBehavior"=dword:00000000
"FriendlyName"="YourAddinName"


[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}]
@="YourAddinName.Connect"

[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}\Implemented Categories]

[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}\InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="YourAddinName.Connect"
"Assembly"="YourAssemblyNameFullTypeName"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///PathToAssembly"

[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}\ProgId]
@="YourAddinName.Connect"

NOTE the tokens "YourGeneratedGuid" must have the curly braces {} included and be the same as the Guid in the attrib above, the token "YourAssemblyNameFullTypeName" must be the Assembly full name, the token "YourAddinName.Connect" must be the same ProgId set in the attrib above.

SIDE NOTE

Also found this helpful, might save you couple hours googling.

'HKEY_CURRENT_USER\Software\Microsoft\VBA\6.0\Common
'FontFace=Courier New (STRING - Default if missing)
'FontHeight=10 (DWORD - Default if missing)                

Answer 2

I would imagine you could call a .NET DLL from your VBA code (never actually done this myself). Just create a VB Class Library project and create a DLL to use in your VBA.

After a quick google it looks like you would need to set "Register for Com Interop" = True under Project Properties->Build, but like I said, I've never actually tried this before.

Answer 3

What about VSTO (Visual Studio Tools for Office)? It can be used to create Office Add-Ins in any .NET language.

Answer 4

Impressive. I fiddled w/ VS.Net to build a VBAIDE addin but could never quite get there. Your registry instructions did the trick. Ori, where did u find the reg info or how did decipher it? I ended up building my addin w/ VB6 but I'v been worried about continued use of the older VB tools. Now I can migrate to VS.Net. Many Thx!

Update: Based on your notes, I Googled answers to my questions. Thx.