Sunday, March 16, 2014

Interfacing C# and VBA with Excel-DNA (with intellisense support)

In my previous posting, I was presenting how to create C# COM Server class and use that class in VBA without intellisense support. If this topic is completely new for you, I suggest that you check out first that posting before starting to work with this one. This posting will present a bit more complex way to do the same thing, but also including full intellisense support for your COM server class to be used in VBA.

PROJECT RESULT

The end result of this small example project will be a C# COM Server class, which can be used in VBA with full intellisense support. For this project, I have been using Visual Studio 2010 Express with Framework 4.0.

PREPARATORY TASKS

Download and unzip Excel-DNA Version 0.30 zip file to be ready when needed. There is also a step-by-step word documentation file available within the distribution folder. In this project, we are going to follow these instructions.

STEP ONE: C# program


Create a new class project and name it to be XLServer. Also, change the class name to be XLServer. CopyPaste the following code into your XLServer.cs and save the project. At this point, do not mind about all those error messages. As we add reference to ExcelDnaIntegration.dll file, those errors will disappear.

using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace XLServer
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class COMLibrary
    {
        public double add(double x, double y)
        {
            return x + y;
        }
    }
    //
    [ComVisible(false)]
    class ExcelAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ComServer.DllRegisterServer();
        }
        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
        }
    }
}

By adding class ExcelAddin (implements IExcelAddin interface), we are enabling the registration and unregistration of our dll file to be done programmatically, without the use of regsvr32.exe from command prompt.

Note also, that ClassInterfaceType attribute for our public COMLibrary class has been set to be AutoDual. This means, that a dual class interface is generated for this class and also exposed to COM. Class type information is produced for the class interface and published in type library file. As a part of this posting, we will create this type library file.

STEP TWO: Excel-DNA


Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what we just downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be True.

Add new file as text file to project (Project - Add new item - Text file) and name it to be XLServer.dna. CopyPaste the following xml code into this file.

<DnaLibrary Name="XLServer" RuntimeVersion="v4.0">
  <ExternalLibrary Path="XLServer.dll" ComServer="true" />
</DnaLibrary>

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder (\\Projects\XLServer\XLServer) and rename it to be XLServer.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see XLServer.xll file what we just pasted into this XLServer folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer.

Build the solution. Everything should have gone well, without any errors or warnings. At this point, my \\XLServer\bin\Release folder looks like the following.




At this point, we are practically done with C#.

You may also notice, that ExcelDna.Integration.dll file has been copied into our release folder. This has not been an accident. To enable intellisense to be used, we need to create type library file (tlb) for our COM Server class by using Type library exporter in Visual Studio Command Prompt. Type library exporter (tlbexp.exe) needs to have that dll file in order to find all program parts it needs when creating type library file.

STEP THREE: Creating Type Library


Next, we have to open our VS command prompt (Start - All Programs - Microsoft Visual Studio 2010 Express - Visual Studio Command Prompt 2010). Before opening, move your cursor over the name (Visual Studio Command Prompt 2010) and by right-clicking, select and open properties window. On properties, replace the existing string in Start in text box with the complete address of our current project release folder (\\VS2010\Projects\XLServer\XLServer\bin\Release).







































Press Apply and OK. By setting this start path string, we are effectively opening VS command prompt directly in our project release folder. Next, open VS command prompt and write the command "tlbexp XLServer.dll" in order to create type library into project release folder.





















Press enter and the following information will confirm that type library file (XLServer.tlb) has been created into current project release folder.





















At this point, we can close our Visual Studio command prompt. Let us take a look at our release folder. My folder has the following files.













We can see, that XLServer.tlb file has been successfully created.

STEP FOUR: VBA


Open a new Excel workbook, open VBA editor and create reference to COM class (Tools - References - Browse).




















Select XLServer.tlb (from our release folder) and press Open. Insert a new standard VBA module and copyPaste the following code.

Option Explicit
'
Sub tester()
    '
    Dim lib As New COMLibrary
    Debug.Print lib.Add(12, 13)
    Set lib = Nothing
End Sub
'

If we run this small program, it prints the value 25 into editor immediate window. This confirms, that our COM Server is working correctly. Moreover, we have full intellisense support for COM Server class methods now available. At this point, we are done.

Early-binding scheme


In our example VBA program above, we are creating instance of our COM Server class by creating an object of type COMLibrary (C# class name) without using VBA CreateObject function. This scheme is called early-binding. The biggest advantage is intellisense support. Another advantage of this scheme is fast execution time (no compiler run-time checkings made). The first downside of this scheme is complex implementation compared to Late-binding scheme. Even bigger downside comes with the issues, concerning version handling safety.

STEP FIVE: Version handling issues - a fair warning


Let us assume, that we have done everything successfully and our COM Server is working well. Now, we would like to implement a new subtract method to our COM Server class. Let us open our project and implement this method. The content of our new XLServer.cs is given below.

using System;
using ExcelDna.Integration;
using ExcelDna.ComInterop;
using System.Runtime.InteropServices;
//
namespace XLServer
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class COMLibrary
    {
        public double add(double x, double y)
        {
            return x + y;
        }
        public double subtract(double x, double y)
        {
            return x - y;
        }
    }
    //
    [ComVisible(false)]
    [ClassInterface(ClassInterfaceType.None)]
    class ExcelAddin : IExcelAddIn
    {
        public void AutoOpen()
        {
            ComServer.DllRegisterServer();
        }
        public void AutoClose()
        {
            ComServer.DllUnregisterServer();
        }
    }
}

Build the project and save results. Now, by using Excel what we created before for testing purposes, let us open its VBA editor again and try to run the program (using add method). Remember to doubleClick XLServer.xll file in your \\XLServer\bin\Release folder after opening your Excel. Now, when running that VBA program, compiler will throw run-time error 430 (Class does not support Automation or does not support expected interface).

If you check your VBA code, you can confirm that intellisense is still alive and showing all class methods for our previous version (before changes). To be able to fix this error, we need to re-create our Type Library file again. After I created my type library file again, my intellisense was showing all methods of current version.

Just for the curious, I made exactly the same changes for late-binding scheme presented in previous posting. It turned out, that after those changes, VBA was able to use both class methods (add, subtract) without any further changes or modifications. Moreover, we did not need to create any type library files either.

Conclusions: using Early-binding scheme with COM Server class is providing intellisense support and fast execution time (without run-time compiler checkings), but whenever there will be any changes or modifications made in COM Server class source, type library file needs to be re-created. Late-binding scheme does not provide intellisense support and execution time is a bit slower (run-time compiler checkings), but whenever there will be any changes or modifications made in COM Server class source, no further modifications are needed to get VBA program working.

Now, interesting question is, that exactly how much slower (due to those run-time compiler checkings) late-bound version really is, compared to early-bound version and does it really matter on our program? If this difference is not substantial, it is hard to find any reason to implement COM Server class as early-bound object. It is nice to have intellisense support, but as we see, it comes with the price. Anyway, more information on this particular topic and also possible solution for version handling issues can be found from here and from here.

Final notes


For learning more things about Excel-DNA, check out its homepage. Getting more information and examples with your problems, the main source is Excel-DNA google group. For those who would like to see useful examples using Excel-DNA in financial programs, there is an excellent book C# for Financial Markets (chapter 22) written by Daniel Duffy and Andrea Germani (published 2013). Finally, Excel-DNA is an open-source project, and we (the happy users) can invest its future development by making a donation.

Thanks for reading again and good luck!

-Mike

18 comments:

  1. Hi - could you by any chance make those files available for download?

    ExcelDna.Integration.dll
    XLServer.dll
    XLServer.dna
    XLServer.pdb
    XLServer.tlb
    XLServer.dll

    ReplyDelete
  2. Hi, sorry for late reply. Not at the moment. If you follow all the instructions, you should be able to create the similar files presented in the posting.

    ReplyDelete
  3. My hunch is, it is not registered in the registry, how and where do i check for it

    ReplyDelete
  4. The above Unknown comment is mine, not sure why it went out as Unknown, Thanks

    ReplyDelete
  5. Hi Mikael,
    I got everything to work as you specified in the blog, but after I ran the VBA script Sub tester instead of result 25 I got a pop up error Run-time error '429':
    ActiveX component cant create object.

    On Debug I can see the Dim lib is lib= ""

    I am using MS Excel Office 365

    ReplyDelete
  6. Hi Shrikant,

    Have you solved your problem?

    I am using Ms Excel 2010 + VS 2010
    The error is on below line
    Debug.Print lib.Add(12, 13)

    I got the exact same pop up error Run-time error '429':
    ActiveX component cant create object.

    Hi Mikael,

    I encounter almost the same problem as Shrikant, only difference is the Excel version (2010 v.s. 365).

    Can you give some advice on how to solve this issue? Thanks.

    ReplyDelete
  7. I advice you to raise the issue in here : https://groups.google.com/forum/#!forum/exceldna.

    ReplyDelete
  8. Wayne, I had the same issue. I resolved it by switching from the debug folder to the release folder.

    Mikael, this article is a little old, I suggest you update it to use NUGET at the start, you just need to create a C# class library and add the nuget packages. The dna file etc get auto added and the xll files get created on build.

    ReplyDelete
  9. Can the 429-error has something to do with 64-bits Excel?

    ReplyDelete
  10. I also got the Run-time error '429' and found out that I need to double click the XLServer.xll-file to get it working.

    ReplyDelete
  11. Great tutorial! This is one of the best laid out and easy to follow tutorials I've gone through in years. A quick note for others using VS2015, to compile the Type Library go to Start -> Visual Studio 2015 -> Developer Command Prompt for VS2015.

    ReplyDelete
  12. When I try to run the Type Library Converter, I always get an error: Type library exporter encountered an error while processing 'XLServer.ExcelAddin, ExcelInterface'. Error: Type library exporter cannot load type 'XLServer.ExcelAddin' (error: Could not load file or assembly 'ExcelDna.Integration, Version=1.0.7056.37028, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

    ReplyDelete
  13. ArcherBird,
    I had the same issue as you have mentioned. Here's how I was able to solve it
    The cause is that the file Excel.Integration.dll is not in the same \Release folder. This file is part of the Excel DNA installation and can be obtained by doing the following. In the VS project folder, in solution explorer, expand the Project->References and select Excel.Integration. Below, in the Properties Window you would see this file's path in the 'Path' field property. Copy this path and go to this folder in Windows explorer and copy this file and paste it into the same \Release folder. Try to now rebuild the .tlb from command prompt and it worked for me.

    ReplyDelete
  14. Thank you, I've just been looking for information approximately this subject for a while and yours is the best I have discovered till now. But, what about the conclusion? Are you sure concerning the source?

    ReplyDelete