Tuesday, October 9, 2012

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.

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.

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


Bruno Zogbede said...
This comment has been removed by the author.
Bruno Zogbede said...

Hi Darren,

I tried it, with some small changes and bingo!
It worked :-)
Thanks a lot for sharing this knowledge !!!

The changes I made were in the ribbon (customised for XL2007; in your example, get rid of the 'qat' line) and the registration.

open prompt as administrator (win 7: start > cmd > right mouse click on commandprompt icon > open as administrator);
on my machine, this came:

then type after a space

where SPACE is just one space between the letters

That's it! It then worked perfectly!

Again, muchos gracias :-)