Two weeks ago, I was asked by a user to provide access to some data of one of their database so that they can use it directly from Excel. I first suggested some methods that are already implemented in the application (like extracting the data and use it in a lookup table). But nothing could satisfy him. He has one idea in mind: being able to use a formula to have access to the data (ie: GetData(value1, value2)) much like Bloomberg and many others vendors are providing.

It was time for me to dig into the creation of an add-in which I knew it was possible but I never built. So guess what is my article all about this month?

Demo code

The demo code is provided in both VB and C#. It was built using Visual Studio 2008. I have tested the add-in against Office 2003 and also 2007.

Is VSTO required?

At first, I thought that VSTO (Visual Studio Tools for Office) was required to create that kind of interaction between a Visual Studio component and Office applications. But for what we will build here, you don’t. Plain old Visual Studio is all you need.

The component we will create will be a library of functions. The add-ins we will create here will be found under “Automation Add-ins” in Excel.

Creating the project

The really first thing we need to do is to create a new Class Library project as shown in figure 1. This is the same kind of project you surely have already created in the past to create a DLL for use in your application.

Figure 1: the creation of the library of functions

Now that the project is created, rename the generic empty class (Class1.vb) to something more meaningful for you. I named mine EMFunctions.

A single project used as an add-in may contain multiple classes as any other class library. There is no limitation here.

Because we will want to use this component from within Excel, we have to set special properties that we normally don’t.

One thing I found that I don’t really understand the full impact is that add-ins only seems to work if your configuration is using the Release mode. So better switch right now to this configuration. Even if you are in Release mode, debugging will be available.

Open the project properties (by double-clicking “My Project” in the Solution Explorer and make sure that the Application tab is selected. Now click the “Assembly Information” button to open the dialog of the same name and check the “Make assembly COM-Visible” as shown in figure 2. This is required since Excel still rely on COM to communicate with components. You can also set other properties such as the title, the company and others to your own discretion (but don’t touch the GUID). Finally click the OK button to close this dialog.

Figure 2: Check the “Make assembly COM-Visible”

Now open the Compile tab and check the “Register for COM Interop” option which is completely at the bottom as shown in figure 3.

Figure 3: Check “Register for COM Interop”

We also need to add some references. So open the References tab, click the Add button and go to the COM tab of the “Add References” dialog. From the list of components, select these 2:

• Microsoft Excel xx Object Library
• Microsoft Office xx Object Library

The “xx” here represents the version. Since you may have multiple versions of those libraries in your list, select the higher versions.

Figure 4: The References

If you will need to use the Excel objects (worksheets, range, cell, ...) from within your add-in, you will also need to add other references found under the .Net tab :

• Microsoft.Office.Tools.Common
• Microsoft.Office.Tools.Excel

One last thing we need to do in the project properties is to sign the assembly so that the user won’t have to lower the security settings in Excel to be able to use our add-in. This can be done by opening the “Signing” tab, checking “Signing the assembly”. From the “Choose a strong name key file” combo, either select an existing file you have or create a new one.

Figure 5: Signing the assembly

Plumbing code

Having marked the component has COM-visible is not enough to have the code available to Excel. There is some specific code to add to the classes you will want to expose.

Open your class (remember mine is called EMFunctions).it currently only contains the class declaration like this:

Public Class EMFunctions

End Class

The very first thing we need to do with this class declaration is to mark this specific class as visible for COM by changing the current code for (notice that you don’t have to mark all your classes as visible for COM, only those that requires it):

Imports System.Runtime.InteropServices
Imports WIN32 = Microsoft.Win32

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class EMFunctions

End Class

Then, to the classes that are visible to COM, we need to add some code that will automatically be called to register and unregister the class:

<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal pType As Type)
    WIN32.Registry.ClassesRoot.CreateSubKey(GetSubkeyName(pType))
End Sub

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal pType As Type)
    WIN32.Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(pType), False)
End Sub

Private Shared Function GetSubkeyName(ByVal pType As Type) As String
    Return "CLSID\{" + pType.GUID.ToString.ToUpper + "}\Programmable"
End Function

The important things here are the attributes that are doing all the hard stuff. They tell .Net to call those functions when the assembly is registered.

The public methods

The plumbing is now all done. We haven’t written anything “fun” yet.

Now consider this code:

Public Function GetPopulationByID(ByVal pID As Integer) As Integer
    Dim ds As DataSet = cShared.CreateDataSet

    Dim drs As DataRow() = ds.Tables("States").Select(String.Format("ID = {0}", pID))
    If drs.Count = 1 Then
        Return Convert.ToInt32(drs(0).Item("Population"))
    Else
        Return 0
    End If
End Function

What this code does is that it receives an ID as an argument, use this argument to search into a dataset and if found, it returns the population. Long time readers will recognize my famous dataset that I have been using for a long time containing countries, states and the population for some states (full code available in the downloadable code).

Is there anything in that code that relates to Excel or that let us think that it will be called by Excel? Absolutely not. You could even use this method from a regular .Net application.

Testing the project locally

It is now the time to start testing what we have done so far. It can be easily done by having Excel starting when we hit F5. To configure this behaviour, open the project properties (by double- clicking the My Project item in the solution explorer). Then, open the debug tab and set the start action to “Start external program”. Finally, find Excel.exe using the ellipsis button to the right of the textbox (or type the full path if you know it). This is what is shown in figure 6.

Figure 6: Setting Excel as the startup application

You are now ready to press F5. Most of you will probably be disappointed at this point because you will get this error:

Cannot register assembly "C:\_data\Articles\UT2010\201006 - Excel Add-in\EMoreauDemoXLAddin\EMoreauDemoXLAddin\bin\Debug\EMoreauDemoXLAddin.dll" - access denied. Please make sure you're running the application as administrator. Access to the registry key 'HKEY_CLASSES_ROOT\EMoreauDemoXLAddin.cShared' is denied.

This error has nothing to do with the code. It is only because very special permissions are required in order to register the DLL. Close Visual Studio and reopen it with the administrator privileges. To do this, find Visual Studio in the Start menu, right click on it and choose "Run as Administrator". Now reopen your solution.

When you now hit F5, the project compiles and automatically opens Excel.

The first time you will run your add-in, it won’t be available right away in Excel. You need to add it to the list of allowed add-ins. If you are running Excel 2003, open the Add-ins dialog from the Tools menu and click the Automation button. If you are running Excel 2007, you will find the Automation servers dialog by following these steps:

• Click the Microsoft Office button in the top left corner of the window.
• Click the “Excel Options” button.
• Click the Add-Ins tab in the Excel Options dialog.

Figure 7: The Excel Options dialog

At the bottom of this dialog, make sure that the Manage option is set to “Excel Add-Ins” and click the Go button. This will bring you another dialog titled “Add-Ins”. In this dialog, click the “Automation” button which will again bring another dialog titled “Automation Servers” as shown in figure 8. From the list of available components, find the one you created and click the OK button.

Figure 8: The automation servers dialog

At this point you might have Excel complaining about mscoree.dll. Don’t worry! It will ask if you want to delete the add-in from the list to which you need to answer No. Now, click the OK button on the Add-Ins dialog to close it.

We are finally ready to use the functions created in the project from Excel.

If you are using my sample, you have 2 methods: GetPopulationByID and GetPopulationByCode. To test them, simply type this into 2 cells:

=GetPopulationByID(17)
=GetPopulationByCode("QC")

If everything was done correctly, you should see some results on the worksheet.

Debugging the code

Do you remember that one of the very first steps after we created the project was to set the configuration into Release mode? That won’t stop you debugging your code. While Excel is still running your add-in, go back to your project and place a breakpoint into the GetPopulationByID method, return to Excel and change the parameter from 17 to 16. After you pressed the Enter key, the breakpoint will be intercepted in Visual Studio letting you debug your add-in just like any other regular code from a regular project.

Setup and Distribution

When you will be happy of your add-in, chances are that you will want to spread it to users.

The easiest way I found to properly install and register your new component to be usable by Excel is to create a setup package.

So in the Solution Explorer, right-click the solution node and choose the Add item and then the New Project item. In the “Add new project” dialog, find the “Setup Wizard” template as shown in figure 9. Give your setup project a meaningful name and click the OK button.

Figure 9: Adding the setup project

This wizard contains 5 short steps (in which we will only do the first 3 steps.

• The first step is only showing a welcome message so click the Next button.
• The second step requires you to select a project type. Ensure that the selected item “Create a setup for a Windows application” and click the Next button.
• The third step lets you pick the modules you want to distribute. In our case, ensure that “Primary output from xxx” and “Documentation Files from xxx” are checked and click the Finish button.

Once the setup project is created, select the setup project into the solution and click the Properties tab. In the properties, you can fill some of them to let the user know what they are installing (like the Author, Manufacturer, Description, ManufacturerURL, ...).

Finally, right-click the setup project from the Solution Explorer and select Properties. From the “xxxSetup Property Pages” dialog, ensure that the configuration combo is set to Release. Also ensure that the “Package files” combo is set to “In setup file”. Still from that dialog, click the Configuration Manager button and check the Build checkbox for both projects. Click Close and OK.

You are now ready to build the deployment package. You should always build your add-in first and then your setup. Or if you are as lazy as I am, select right click the solution and select the “Build solution” option.

In the release folder (under the setup project folder), you will find 2 files: setup.exe and xxxSetup.msi. Copy these 2 files to the PC where you want to run your add-in and run Setup.exe.

The very last step before being able to use the component on another PC is to register it with RegAsm (which is not done by the installation). You will normally find RegAsm.exe on your development PC into the c:\Windows\Microsoft.Net\Framework\v2.0.50727. Once you copied that file to the other PC in the same folder where your add-in DLL sits, you can open a command prompt, go to the folder where you installed your dll and type: RegAsm YourAddInDll.dll

Finally the add-in can be used from within Excel. The same steps you needed to do to test the add-in locally must be done on the destination PC before your add-in is available.

Conclusion

The development of a component in .Net usable from Excel is quite simple. You can easily re-use your knowledge to extend the features of Excel. The part that might be a bit tougher is the deployment. I hope the steps I have provided here will help you acheiving your goals!