Build add-in for VBA IDE using VB.NET Build add-in for VBA IDE using VB.NET vba vba

Build add-in for VBA IDE using VB.NET


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 SystemImports System.DrawingImports System.LinqImports System.Runtime.InteropServicesImports ExtensibilityImports Microsoft.Vbe.InteropNamespace 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 RegionEnd ClassEnd Namespace

Here is the registry .key 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)                


Unfortunately almog.ori's steps didn't work for me. Here is my version to aid people in the future:

  1. Create a C# or VB.NET Class Library project named "VBEAddIn"

    Add the following Interop assemblies as references to the project, using the "Project", "Add Reference..." menu, "Browse" tab.

  2. Extensibility (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Common\Extensibility.dll) - if its not there try the C:\Program Files (x86)\ if your using a x64 PC.

  3. Microsoft.Office.Interop.Excel (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll)

  4. Microsoft.Vbe.Interop (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.dll)

  5. (optionally) Microsoft.Vbe.Interop.Forms (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.Forms.dll)

Add a class to your project with the following code:

VB.Net:

Imports Microsoft.Office.InteropImports ExtensibilityImports System.Windows.FormsImports System.Runtime.InteropServicesImports Microsoft.Vbe.Interop<ComVisible(True), Guid("3599862B-FF92-42DF-BB55-DBD37CC13565"), ProgId("VBEAddInVB.Net.Connect")> _Public Class Connect    Implements Extensibility.IDTExtensibility2    Private _VBE As VBE    Private _AddIn As AddIn    Private Sub OnConnection(Application As Object, ConnectMode As Extensibility.ext_ConnectMode, _       AddInInst As Object, ByRef custom As System.Array) Implements IDTExtensibility2.OnConnection        Try            _VBE = DirectCast(Application, VBE)            _AddIn = DirectCast(AddInInst, AddIn)            Select Case ConnectMode                Case Extensibility.ext_ConnectMode.ext_cm_Startup                Case Extensibility.ext_ConnectMode.ext_cm_AfterStartup                    InitializeAddIn()            End Select        Catch ex As Exception            MessageBox.Show(ex.ToString())        End Try    End Sub    Private Sub OnDisconnection(RemoveMode As Extensibility.ext_DisconnectMode, _       ByRef custom As System.Array) Implements IDTExtensibility2.OnDisconnection    End Sub    Private Sub OnStartupComplete(ByRef custom As System.Array) _       Implements IDTExtensibility2.OnStartupComplete        InitializeAddIn()    End Sub    Private Sub OnAddInsUpdate(ByRef custom As System.Array) Implements IDTExtensibility2.OnAddInsUpdate    End Sub    Private Sub OnBeginShutdown(ByRef custom As System.Array) Implements IDTExtensibility2.OnBeginShutdown    End Sub    Private Sub InitializeAddIn()        MessageBox.Show(_AddIn.ProgId & " loaded in VBA editor version " & _VBE.Version)    End SubEnd Class

C#:

using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Diagnostics;using System.Linq;using System.Runtime.InteropServices;using Extensibility;using Microsoft.Vbe.Interop;using System.Windows.Forms;namespace VBEAddin{    [ComVisible(true), Guid("3599862B-FF92-42DF-BB55-DBD37CC13565"), ProgId("VBEAddIn.Connect")]    public class Connect : IDTExtensibility2    {        private VBE _VBE;        private AddIn _AddIn;        #region "IDTExtensibility2 Members"        public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)        {            try             {                _VBE = (VBE)application;                _AddIn = (AddIn)addInInst;                switch (connectMode)                 {                    case Extensibility.ext_ConnectMode.ext_cm_Startup:                        break;                    case Extensibility.ext_ConnectMode.ext_cm_AfterStartup:                        InitializeAddIn();                        break;                }            }            catch (Exception ex)             {                MessageBox.Show(ex.ToString());            }        }        private void onReferenceItemAdded(Reference reference)        {            //TODO: Map types found in assembly using reference.        }        private void onReferenceItemRemoved(Reference reference)        {            //TODO: Remove types found in assembly using reference.        }        public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom)        {        }        public void OnAddInsUpdate(ref Array custom)        {        }        public void OnStartupComplete(ref Array custom)        {              InitializeAddIn();        }        private void InitializeAddIn()        {            MessageBox.Show(_AddIn.ProgId + " loaded in VBA editor version " + _VBE.Version);        }        public void OnBeginShutdown(ref Array custom)        {        }        #endregion    }}

In the Project properties window of the project:

  1. In the "Application" tab, ensure that both the Assembly name and Root namespace are set to "VBEAddIn".

  2. In the "Compile" tab, ensure that the "Register for COM interop" checkbox is checked. We wont bother with registering assembly for COM Interop manually with the proper regasm.exe tool. However note that the "Register for COM interop" checkbox would only register the add-in dll as 32-bit COM library, not as 64-bit COM library.

  3. In the "Compile" tab, "Advanced Compile Options" button, ensure that the "Target CPU" combobox is set to "AnyCPU", which means that the assembly can be executed as 64-bit or 32-bit, depending on the executing .NET Framework that loads it.

  4. In the "Signing" tab, make sure the "Sign the assembly" is unticked.

Next add the registry Keys, save the below snippet as a ASCI file with a reg extension and double click it to add the values to the registry.

Important Note: Before you execute the reg file, change the path: "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"

Windows Registry Editor Version 5.00[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\VBEAddIn.Connect]"CommandLineSafe"=dword:00000000"Description"="Description for your new addin""LoadBehavior"=dword:00000000"FriendlyName"="VBEAddIn"[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}]@="VBEAddIn.Connect"[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}\Implemented Categories][HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}\InprocServer32]@="mscoree.dll""ThreadingModel"="Both""Class"="VBEAddIn.Connect""Assembly"="VBEAddIn""RuntimeVersion"="v2.0.50727""CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}\ProgId]@="VBEAddIn.Connect"
  1. Build the VBE Add-in in Visual Studio and open Excel.
  2. Open its VBA editor (Alt + F11).
  3. Go to the "Add-Ins", "Add-In Manager..." menu to check that the add-in is correctly registered.
  4. Load the add-in. You should see the message box "VBEAddIn.Connect loaded in VBA editor"

If you get this error:

enter image description here

'VBEAddIn' could not be loaded.

Remove it from the list of available Add-Ins?

Its likely you didn't change the path "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"

And check that the CodeBase key is in the registry (add a string regkey with the CodeBase if it doesn't exist):

enter image description here

Then close down the Office application, build the VBE AddIn again from Visual Studio, Open Office (Excel, Outlook, Word, etc) and Alt + F11, AddIns menu > AddIn Manager and select the AddIn and Tick Loaded/UnLoaded.

Final Trick to overcome this issue:

If that still fails, close the Office app, goto Visual Studio, Project Properties > Build Tab > Tick Register for COM Interop > Build Solution and open the Office Add In > Alt + F11 > AddIns Menu > AddIn Manager and click Loaded/Unloaded.


This answer uses some info from Carlo's Quintero (MZTools) that I've modified, Ref: http://www.mztools.com/articles/2012/MZ2012013.aspx


I also found this reference to be helpful in making a VBA DLL from C# or VB.NET:

  1. Create a new C# (or VB.Net) project and select Class Library as the template type.

    using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace SimpleCalc{    public class Calc    {        private int numberOne = 0;        private int numberTwo = 0;        public void SetNumberOne(int number)        {            numberOne = number;        }        public void SetNumberTwo(int number)        {            numberTwo = number;        }        // Add two integers        public int Add()        {            return numberOne + numberTwo;        }    }}
  2. Configure project properties to make it COM visible.

  3. Register for COM Interop.
  4. Compile the project.
  5. Copy the type library file to Windows system folder.
  6. Reference the type library from Access VBA editor.
  7. Use the DLL in your VBA code.

    Public Function test()    Dim lngResult As Long    Dim objCalc As SimpleCalc.Calc    Set objCalc = New SimpleCalc.Calc    objCalc.SetNumberOne (3)    objCalc.SetNumberTwo (6)    lngResult = objCalc.Add()End Function

Made available by GeeksEngine.com