How to make a .NET (Excel) Add-In Using Visual Basic Express 2010

Automation add-ins are easier than what follows and are the way to go if you just want a few custom functions.  Actually, for efficiency's sake, you might want to just use VBA functions added to Module1  (must be Module1—no renaming to a more suitable name) and then save the workbook as an Excel Add-In (*.xlam).  But, if it is user interaction or in some way making Excel "do stuff" (rather than just return a single result or an array of results), an add-in which implements the IDTExtensibility2 and IRibbonExtensibility interfaces may be a good strategy.  (The main alternative is to use VBA.)

Making an Excel Add-in in VB.NET involves the following (the ordering of the steps is not absolute):

1. Ensure you have Office installed on the development machine and have the Primary Interop Assemblies (PIAs) installed in the Global Assembly Cache (GAC).  You can download them from Microsoft.  (The Microsoft installer will put the PIAs in the GAC.)
2. Start a Class Library project in VB.NET.
3. It's a good idea to generate your own strong name using the sn.exe tool (part of the .NET framework).  You only need one for multiple projects.  In a nutshell, you use the sn.exe tool to create a *.snk file which you will tell VB.NET about.
• let VB.NET know where your *.snk file is by going to Project/Properties..., under Signing check "Sign the assembly" and choose a strong name key file.  You will be able to browse to where your *.snk file is
• if your add-in is intended to be widely distributed, and especially if you want to sell your add-in, you would be well-advised to learn more about this topic to ensure your end-users don't get warning messages that cause them to be (perhaps unduly) alarmed.
4. Apparently, step 4 is unnecessary, but I did step 4, so here it is:  In the project properties, go to Application and click the button Assembly Information....  In this screen, check the Make assembly COM-Visible box.
5. Create a GUID.  There are several ways but a program called guidgen.exe may already be on your computer which will do the job.  Just search your computer for it.  It is located under C:\Program Files\Microsoft SDKs\Windows\... on my computer.
• Copy and Paste the GUID into the Assembly Information... dialog box referred to in step 4.
6. Encapsulate your project in a namespace either by typing it explicitly in the files or by specifying a root namespace in the project properties.
7. Add references to needed PIAs, and extensibility (.NET tab)
• It is recommended that you add these by referencing the Interop Assembly on the .NET tab, not the COM tab. You may need to browse for this: Look under C:\Windows\assembly\gac_msil\Microsoft.Office.Interop.Excel\...
8. Create a class named Connect (seems that the name has to be Connect) which implements the IDTExtensibility2 and IRibbonExtensibility interfaces.
• Implements Extensibility.IDTExtensibility2, Microsoft.Office.Core.IRibbonExtensibility
9. Implement the methods of the interfaces.
10. Decorate the class with COM visibility specifications and include functions for creating and deleting custom registry settings.
11. Register your assembly with regasm.exe.

Steps 8, 9, and 10 are implemented in the following code snippet, which has been parameterized somewhat.

Imports System.Runtime.InteropServices Imports Extensibility Imports XL = Microsoft.Office.Interop.Excel <ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> Public Class Connect Implements Extensibility.IDTExtensibility2, Microsoft.Office.Core.IRibbonExtensibility Dim oExcel As XL.Application Public Function GetCustomUI(RibbonID As String) As String Implements Microsoft.Office.Core.IRibbonExtensibility.GetCustomUI Dim res As String res = "<customui xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">" res += "<ribbon>" res += "<qat/>" res += "<tabs>" res += "<tab id=""ArithmosTab"" label=""Arithmos"" insertBeforeQ=""TabFormat"">" res += "<group id=""exportGroup"" label=""Export"" autoScale=""true"">" res += "<button id=""secret111"" label=""Write Secret Number"" screentip=""See what the secret number is..."" imageMso=""DiagramTargetInsertClassic"" onAction=""WriteSecretNumber""/>" res += "</group>" res += "</tab>" res += "</tabs>" res += "</ribbon>" res += "</customUI>" Return res End Function Public Sub WriteSecretNumber(control As Microsoft.Office.Core.IRibbonControl) MsgBox("I don't know what you're talking about.") End Sub Public Sub OnStartupComplete(ByRef custom As System.Array) Implements IDTExtensibility2.OnStartupComplete End Sub Public Sub OnBeginShutdown(ByRef custom As System.Array) Implements IDTExtensibility2.OnBeginShutdown End Sub Public Sub OnDisconnection(RemoveMode As ext_DisconnectMode, ByRef custom As System.Array) Implements IDTExtensibility2.OnDisconnection End Sub Public Sub OnConnection(Application As Object, ConnectMode As ext_ConnectMode, AddInInst As Object, ByRef custom As System.Array) Implements IDTExtensibility2.OnConnection oExcel = Application End Sub Public Sub OnAddInsUpdate(ByRef custom As System.Array) Implements IDTExtensibility2.OnAddInsUpdate End Sub <ComRegisterFunctionAttribute()> Public Shared Sub RegisterFunction(ByVal pType As Type) Microsoft.Win32.Registry.ClassesRoot.CreateSubKey("CLSID\{" + pType.GUID.ToString.ToUpper() + "}\Programmable") Dim key As Microsoft.Win32.RegistryKey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey("CLSID\{" + pType.GUID.ToString.ToUpper() + "}\InprocServer32", True) key.SetValue("", System.Environment.SystemDirectory + "\mscoree.dll", Microsoft.Win32.RegistryValueKind.String) ' Add-in registry settings so Excel knows that this is an add-in and what it should do with it / register for all users key = Microsoft.Win32.Registry.LocalMachine.CreateSubKey("Software\Microsoft\Office\Excel\AddIns\<yournamespace>.Connect") key.SetValue("CommandLineSafe", 0, Microsoft.Win32.RegistryValueKind.DWord) key.SetValue("Description", "<description of your Add-in.>", Microsoft.Win32.RegistryValueKind.String) key.SetValue("LoadBehavior", 3, Microsoft.Win32.RegistryValueKind.DWord) ' if something goes wrong, tell the user what it is key.SetValue("FriendlyName", "<your friendly addin name>", Microsoft.Win32.RegistryValueKind.String) End Sub <ComUnregisterFunctionAttribute()> Public Shared Sub UnregisterFunction(ByVal pType As Type) Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey("CLSID\{" + pType.GUID.ToString.ToUpper() + "}\Programmable", False) Microsoft.Win32.Registry.LocalMachine.DeleteSubKey("Software\Microsoft\Office\Excel\Addins\<yournamespace>.Connect", False) End Sub End Class

The functions RegisterFunction and UnregisterFunction are invoked by utilities like regasm.exe which comes with the .NET Framework.  I have created a batch file which I run in order to register the assembly.  You will need to modify it to suit your assembly.

cd C:\Users\<userid>\Documents\Visual Studio 2010\Projects\<projectname or names>\bin\Release C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm /codebase yourassembly.dll pause

Note that once the Add-in is registered and added, you will not need to reregister it after every Build (while developing).  

DXFert: An Example VBA Add-In with Custom Ribbon

I implemented this project in Excel 2010. Only the Ribbon modifications are likely to be version dependent (unless you go way, way back). The form and macro in this project should work in any version of Excel.  

To use the macro launchmyForm(control as IRibbonControl)without the Ribbon buttons, such as you would need to do in an earlier version of Excel, you will need to either change

   launchmyForm(control As IRibbonControl) to launchmyForm() 

or 

write another macro that calls launchmyForm(someDummyObject).

In this post, I am going to walk through the building blocks of the project. Except for Excel, everything you need for this project is freely available for download.

What You Will Need to Develop Your Own Similar Project

1. Excel 2007 or later.
2. DXF file format documentation from AutoDesk
3. CustomUI Editor Tool from Open XML Developer
4. A program to allow you to test your DXF output (if that's what you're going for), such as DWG TrueView.

Overview

You need to 

1. create a UserForm within a macro-enabled workbook (*.xlsm)
2. write a one-liner macro that invokes the UserForm
3. create a Ribbon interface which references the one-liner macro, and, finally,
4. save the project as an Add-In (*.xlam) and activate it.

Creating the UserForm

If you don't have the developer tab available to you,...why ever not? Turn the silly thing on! To learn about developing user forms in Excel generally, there are lots of examples on the web, but I'll just give you a few key pieces of code that were important in my project.

Here's the code I used to open a file SaveAs dialog to help the user indicate the name and path for the file that the results will be exported:

Private Sub btn_Browse_Click() Dim fn fn = Application.GetSaveAsFilename(filefilter:="Drawing eXchange Format (*.dxf),*.dxf") If fn <> False Then Me.in_fileName.Text = fn End If End Sub

This should work in very early versions of Excel all the way up to the present (version 2010) and probably future versions as well.  My main beef with it is there is a lack of control.  I would like it to complain about the selection of a file which already exists, but if I really wanted to I could include that complaint in my macro code right before I follow through with the export (which I didn't do—but you can, if you wish).

When you want to terminate the form (either by cancelling or after successful completion), you'll want use something like this:

Private Sub btn_Cancel_Click() Unload Me End Sub

You can learn a lot about the DXF format by looking at my code in the download.  (Hint:  If you have the Add-In added, you don't need to open up the file separately to see the code.  Just go to the Visual Basic Editor (button on the developer tab) and locate the user form DXFout_Form.  Right-click it and choose View Code.)  See page 143 of the AutoDesk's DXF format documentation for a description of the fields in the polyline Entity.

The one-liner code which the Ribbon will call is pretty easy:

Sub launchMyForm(control As IRibbonControl) DXFout_Form.Show End Sub

The Ribbon expects to pass this control parameter to the subroutines it calls, whether you need to use the parameter or not.

Create the Ribbon Interface

I overcomplicated this my first few tries at it. Which is what a lot of people on the internet do. If you want your add-in to have its own ribbon elements, you create those elements using CustomUI Editor (referred to above) within a regular *.xlsm file. When you are ready to finalize it as an Add-In you just save it as an add-in and activate it. The same custom UI that showed up in the original *.xlsm file, will then show up all the time. No fuss, no muss. No Workbook_AddinInstall() code, no SetCustomUI(longComplicatedXMLString), no nothing, it just works! You do have to look at a bit of XML code, mind you.

But first, close down your *.xlsm file and open up CustomUI. Open up your *.xlsm file from within CustomUI. Go to the Insert menu and select Office 2010 Custom Part and, likewise, Office 2007 Custom Part. Now it is time for something ugly. Here is the xml code I used in my project:

For 2010 (customUI14):

<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui"> <mso:ribbon> <mso:qat/> <mso:tabs> <mso:tab id="dxfTab" label="DXF" insertBeforeQ="mso:TabFormat"> <mso:group id="exportGroup" label="Export" autoScale="true"> <mso:button id="highlightManualTasks" label="Export to DXF" imageMso="DiagramTargetInsertClassic" onAction="launchMyForm"/> </mso:group> </mso:tab> </mso:tabs> </mso:ribbon> </mso:customUI>

For 2007 (customUI):

<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2006/01/customui"> <mso:ribbon> <mso:qat/> <mso:tabs> <mso:tab id="dxfTab" label="DXF" insertBeforeQ="mso:TabFormat"> <mso:group id="exportGroup" label="Export"> <mso:button id="highlightManualTasks" label="Export to DXF" imageMso="DiagramTargetInsertClassic" onAction="launchMyForm"/> </mso:group> </mso:tab> </mso:tabs> </mso:ribbon> </mso:customUI>

If you look at the <mso:button> tag you'll notice the launchMyForm bit.  That's the name of the procedure that is to be called.  One parameter is passed, namely, the a reference to the ribbon control that called the subroutine.

Save As an Add-In

At any point, you can turn your *.xlsm project into an Add-In.  (You can even continue editing it while it is an Add-In.)  Go to File, Save As, and select Excel Add-In from the file type options in the SaveAs dialog box.