A Beginner’s Guide to calling a .NET Library from Excel

Introduction

It’s actually very easy to call a .NET library directly from Excel, particularly if you are using Visual Studio 2005. You don’t need Visual Studio Tools for Office. However there doesn’t seem to be an easy guide on the internet anywhere. MSDN help is quite good on the subject, but can be a little confusing. This article is an attempt to redress the situation.

This article was updated 24th August 2007 to cover Excel 2007 and to clarify the issues with intellisense.

A Basic Walk Through

We’ll start by walking through a very basic example. We’ll get Excel to call a .NET method that takes a string as input (for example “ World”) and returns “Hello” concatenated with that input string (so, for example, “Hello World”).

1. Create a C# Windows class library project in Visual Studio 2005 called ‘DotNetLibrary’. It doesn’t matter which folder this is in for the purposes of this example.

2. To call a method in a class in our library from Excel we need the class to have a default public constructor. Obviously the class also needs to contain any methods we want to call. For this walk through just copy and paste the following code into our default class file:

using System;
using System.Collections.Generic;
using System.Text;
 
namespace DotNetLibrary
{
    public class DotNetClass
    {
        public string DotNetMethod(string input)
        {
            return "Hello " + input;
        }
    }
}

That’s it: if you look at existing articles on the web, or read the MSDN help, you might think you need to use interfaces, or to decorate your class with attributes and GUIDs. However, for a basic interop scenario you don’t need to do this.

3. Excel is going to communicate with our library using COM. For Excel to use a COM library there need to be appropriate entries in the registry. Visual Studio can generate those entries for us.

To do this bring up the project properties (double-click ‘Properties’ in Solution Explorer). Then:
i) On the ‘Application’ tab click the ‘Assembly Information…’ button. In the resulting dialog check the ‘Make assembly COM-visible’ checkbox. Click ‘OK’.
ii) On the ‘Build’ tab check the ‘Register for COM interop’ checkbox (towards the bottom: you may need to scroll down).

4. Build the library.

5. Now start Excel and open a new blank workbook. Open the VBA code editor:
i) In Excel 2007 this is a little difficult to find. You have to get the Developer tab visible on the Ribbon if it’s not already set up. To do this click the Microsoft Office Button (top left of the screen), then click Excel Options (at the very bottom). Check the ‘Show Developer tab in the Ribbon’ checkbox in the resulting Options dialog. Click OK. This adds ‘Developer’ to the end of the ribbon menu: click this. Then click the ‘Visual Basic’ icon at the left end of the ribbon.
ii) In earlier versions of Office (2003, XP, 2000) just go to Tools/Macro/Visual Basic Editor on the menu bar.

6. We now need to include a reference to our new library. Select ‘References’ on the Visual Basic Editor’s ‘Tools’ menu. If you scroll down in the resulting dialog you should find that ‘DotNetLibrary’ is in the list. Check the checkbox alongside it and click ‘OK’.

7. Now open the code window for Sheet1 (double click Sheet1 in the Project window). Paste the VBA code below into the code window for Sheet1:

Private Sub TestDotNetCall()
Dim testClass As New DotNetClass
MsgBox testClass.DotNetMethod(“World”)
End Sub

8. Click anywhere in the code you’ve just pasted in and hit ‘F5’ to run the code. You should get a ‘Hello World’ message box.

Getting Intellisense Working in Excel

Whilst the VBA code above compiles and executes, you will discover that intellisense is not working in the code editor. This is because by default our library is built with a late binding (run-time binding) interface only. The code editor therefore doesn’t know about the types in the library at design time.

There are good reasons for only using a late-bound interface by default: with COM versioning libraries can become difficult with early-bound interfaces. In particular, if you change the early-bound interface by adding, for example, a method in between two existing methods you are likely to break existing clients as they are binding based on the order of the methods in the interface.

For similar reasons you are heavily encouraged to code your interface separately as a C# interface and then implement it on your class, rather than using the default public interface of the class as here. You then should not change that interface: you would implement a new one if it needed to change.

For more on this see:

http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices.classinterfaceattribute(vs.80).aspx
http://msdn2.microsoft.com/en-us/library/system.runtime.interopservices.classinterfacetype(VS.80).aspx

However, we can build our library to use early bound interfaces, which means intellisense will be available. To do this we need to add an attribute from the System.Runtime.InteropServices namespace as below:

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
 
namespace DotNetLibrary
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class DotNetClass
    {
        public DotNetClass()
        {
        }
        public string DotNetMethod(string input)
        {
            return "Hello " + input;
        }
    }
}

If you change your code as above it will expose an ‘AutoDual’ interface to COM. This means it is still exposing the late-bound interface as before, but now also exposes an early-bound interface. This means intellisense will work.

To get this working:

1. Save your workbook and close Excel. Excel will lock the DotNetLibrary dll and prevent Visual Studio from rebuilding it unless you close it. Remember you need to save your new code module. If you are using Excel 2007 you will need to save as type Excel Macro-Enabled Workbook (*.xlsm). In earlier versions you can just save as a standard xls.

2. Go back into Visual Studio, change the DotNetClass as shown above, and rebuild the library.

3. Re-open your Excel spreadsheet. Once again if you are using Excel 2007 there is an extra step: you need to explicitly enable macros. A warning bar will appear beneath the ribbon saying the ‘Macros have been disabled’. Click the ‘Options’ button next to this, select ‘Enable this content’, and click OK.

4. Get the VBA code window up again (see item 5 above).

5. Excel can get confused about the interface changes unless you re-reference the library. To do this go to Tools/References. The DotNetLibrary reference should be near the top of the list now. Uncheck it and close the window. Now open the window again, find the library in the list, and re-check it (trust me, you need to do this).

6. Now run the code and it should still work (put a breakpoint in the routine and hit F5).

7. Enter a new line in the routine after the ‘MsgBox’ line, and type ‘testClass.’. When you hit the ‘.’ you should get an intellisense dropdown which shows that DotNetMethod is available. See below.

Let me re-iterate that this works and is fine for development, but for release code you are better off using the default late binding interfaces unless you understand the full versioning implications. That is, you should remove the ClassInterface attribute from your code when you do a release.

Deployment

In the example here we are using Visual Studio to register our .NET assembly on the workstation so that Excel can find it via COM interop. However, if we try to deploy this application to client machines we’re not going to want to use Visual Studio.

Microsoft have provided a command-line tool, regasm.exe, which can be used to register .NET assemblies for COM interop on client workstations. It can also be used to generate a COM type library (.tlb) separate from the main library (.dll), which is considered good practice in general.

As usual with .NET assemblies you have the choice of strong-naming your assembly and installing it in the GAC, or of not strong-naming it and including it in a local path. If you have strong-named your assembly and installed it in the GAC all you need to do is bring up a Visual Studio 2005 command prompt and run:

regasm DotNetLibrary.dll

If you have not strong-named your assembly you need to tell regasm.exe where it is so that it can find it to register it. To do this you need to run the command below, where c:\ExcelDotNet is the path where DotNetLibrary.dll can be found. This works fine, although it will warn you that you should really strong-name your assembly:

regasm /codebase c:\ExcelDotNet\DotNetLibrary.dll

Note that you can unregister an assembly with the /u option of regasm.

For more detail on this see http://msdn2.microsoft.com/en-us/library/tzat5yw6(vs.80).aspx

Debugging into .NET from Excel

You may want to debug from Excel into your class library. To do this:

1. Using Visual Studio 2005 bring up the Properties window for the class library.

2. Go to the Debug tab and select the ‘Start external program’ option under ‘Start Action’. In the textbox alongside enter the full path including file name to Excel.exe for the version of Excel you are using (usually in Program Files/Microsoft Office/Office).

3. On the same Debug tab under ‘Command line arguments’ enter the full path including file name to your test workbook (the .xls file, or .xlsm if you are using Excel 2007). Once you’re done it should something like below::

4. Now put a breakpoint in the code (in our example the sensible place is in method DotNetMethod) and hit F5 in the .NET project. The .NET code should compile and Excel should start with your workbook opened. If you now run the VBA code to call the .NET library again, as above, you should find that the code will break at the breakpoint you set in the .NET code.

Possible Problem with these Examples

One problem we have had with these examples is that Excel can get confused about which version of the .NET Framework to load if you have more than one version installed. If this happens you will get an automation error when you try to instantiate .NET objects at runtime from Excel. The .NET types will appear correctly in the Excel object browser.

The workaround for this is to tell Excel explicitly that the version of the .NET Framework that you are using is supported. To do this create a text file called Excel.exe.config and put it in the same directory as Excel.exe itself. The file should contain the text below (with the version number replaced with the .NET Framework version you are using):

<?xml version="1.0"?>
<configuration>
  <startup>
    <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>
 

References:

Index page from MSDN
http://msdn2.microsoft.com/en-us/library/zsfww439.aspx

More on COM Interop from COM clients into .NET:
http://www.codeproject.com/vb/net/MusaExposingCOM.asp

A COM Class Wizard for C#
http://www.codeproject.com/csharp/cscomtemplate.asp

Guidelines for COM Interoperability from .NET
http://blogs.gotdotnet.com/heaths/archive/2005/03/09/391358.aspx

In Defense of regasm /codebase
http://weblogs.asp.net/savanness/archive/2003/05/29/7749.aspx

Excel/.NET versioning problems
http://krgreenlee.blogspot.com/2006/01/software-running-excel-with-net-11.html

---

Possibly related posts: (automatically generated)

• A Beginner’s Guide to calling a .NET Library from Excel