Rich Newman

April 15, 2007

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.

Intellisense in Excel

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::

Project Properties for Excel

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

About these ads

91 Comments »

  1. Thanks Rich, I’d been struggling to find a simple guide to this.

    Comment by Adrian Johnson — May 5, 2007 @ 7:21 pm

  2. I’ve also been struggling to do this. Great guide, thanks.

    Comment by Michael Daw — May 8, 2007 @ 8:50 pm

  3. Hi, Nice easy-to-follow example. I am getting some problem trying to use the DLL on a machine where Visual Studio is not installed. The machine in question has .Net Framework 2. I have used “regasm /codebase /tlb” to register it. When I try to use the DLL’s method from MS Excel, I get an error message “Runtime error -2147024984(80070002) Automation error the system cannot find the file specified”

    Any idea what to do next will be appreciated.

    Comment by Ahmed Huq — July 20, 2007 @ 12:53 pm

  4. I am not able to make the above steps work for a VBA project in Access. Are there other considerations I need to make with Access? Thanks in advance.

    Comment by Joe — August 21, 2007 @ 5:28 pm

  5. Joe

    The steps are pretty much identical for Access. You’re not the only person who’s asked about this, so I’ve written a separate article on it at http://richnewman.wordpress.com/2007/08/25/a-beginner%e2%80%99s-guide-to-calling-a-net-library-from-access/

    Comment by richnewman — August 25, 2007 @ 1:15 am

  6. Had quite a bit of trouble. Firstly I needed:

    http://support.microsoft.com/kb/908002/,

    Then I could not debug and judging by:

    http://support.microsoft.com/kb/836668/en-us

    It dosn’t look hopeful.

    Sean

    Comment by Sean Creighton — September 10, 2007 @ 11:33 am

  7. Thank you for this article.

    For those who have problems in instantiating the object in VBA, I just want to emphasize the point “Possible Problem with these Examples”.

    Changing (or creating) the Excel.exe.config file solved this problem in my case and it was hard to find the solution.

    So check your Runtime version in “help\about Microsoft visual Studio” and verify your Excel.exe.config:

    Comment by benumz — September 14, 2007 @ 3:32 pm

  8. Great article – thanks!

    Comment by Yaakov Moser — September 23, 2007 @ 4:17 pm

  9. I am not able to open sheet1 by default when opening excel. It is opening sheet2 since last week i.e. 17/09/07. Pls help me

    Comment by PARMOD KUMAR — September 25, 2007 @ 5:16 pm

  10. [...] 4th, 2007 by gobansaor Developing .NET DLLs that are to be used within an Excel VBA add-in is relatively easy to do. But the overhead of the COM managed interfaces can be a serious performance bottleneck if the .NET [...]

    Pingback by JavaScript as an Excel scripting language via ExcelDNA « Gobán Saor — October 4, 2007 @ 12:26 pm

  11. I can’t get it work. i am using VS 2005. this is the error I got when trying to build it:

    C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.Common.targets(2720,9): error MSB3216: Cannot register assembly “C:\C#\Submit\DotNetLibrary\DotNetLibrary\bin\Debug\DotNetLibrary.dll” – access denied. Access to the registry key ‘HKEY_CLASSES_ROOT\DotNetLibrary.DotNetClass’ is denied.

    If I uncheck the ‘Make assembly COM-visible’ option, I will be able to build it. However, when call it from VBA, error out as: ‘Can’t find entry point’.

    Please comment. Thanks.

    Comment by Hongus — October 19, 2007 @ 9:30 pm

    • On the ‘Build’ tab uncheck the ‘Register for COM interop’ checkbox (towards the bottom: you may need to scroll down).

      Comment by Anonymous — March 18, 2011 @ 3:52 pm

  12. Great Article. All my non static methods show up. However, how would i call a static C# method from VBA from Excel 2003. Any idea, i am struggeling not being the most sophisticated VBA programmer myself. THX! frank

    Comment by Frank — October 25, 2007 @ 9:06 pm

  13. Thanks for this article. It was the most helpful I found to get me calling a VB.Net dll from Excel.

    Comment by Doug Glancy — November 16, 2007 @ 11:15 pm

  14. I’ve been having a heckuva time with getting a managed Excel automation add-in without VBA. So far everything works on my development system but not on a deployment workstation – I found this blog because I’m looking into the Excel.exe.config issue – thanks for the details! My experience is documented on my blog at the link below. Maybe it will help someone here, and any assistance is welcome and appreciated.
    remove.mungeNebula-RnD.com/blog/tech/2007/11/excel-tools1.html

    Comment by TonyG — November 25, 2007 @ 2:49 pm

  15. Hi,

    I found this article very useful and informative. I am facing a problem i.e. I have to pass an array of strings form VBA to .Net assembly but I am getting a runtime error ’450′. Any ideas?
    Thanks in advance

    Comment by Kashif Jamal Soofi — November 28, 2007 @ 8:43 am

  16. Have you tried to fire events in the .NET code and create handlers for them in the VB(excel) code? Any luck on that one?

    Comment by DG — November 28, 2007 @ 4:01 pm

  17. Thanks, a lifesaver (I mean the article), everything was solve with the .exe.config

    Comment by Omar E. Ferrer — December 4, 2007 @ 6:16 pm

  18. Thanks,
    nice and easy tutorial.

    Comment by Horsti — December 12, 2007 @ 11:57 am

  19. Hi ,
    Thanks a lot for this great illustration. I followed the instructions to call the C# component from MSWORD 2003 VBA subroutine and works fantastically well.Thanks again.

    Comment by Usha Ganesan — January 10, 2008 @ 7:24 pm

  20. Rich, A nice article – its simple and dare I say elegant.
    Have you done this for Visual C++
    I want to apply this to to a C++ library that I have written so I tried a small C++ example using a public ref class – a rework of your C# example.

    But the C++ library does not show up in the VBA reference pane. I can get a C# library to pass the call to C++ but cannot see the C++.

    An interesting symptom that I get is – I tried to use ILmerge to combine my C# small dll with my C++ dll. ILmerge complains that the C++ is not marked as managed code.Any thoughts would be very welcome.
    Thanks
    Rob

    Comment by Rob Blackell — January 24, 2008 @ 5:49 am

  21. Very interesting article, many thanks!!
    I would recommending reading this article though:

    http://blogs.gotdotnet.com/heaths/archive/2005/03/09/391358.aspx

    since some of the recommendations made here are not recommended short cuts as it appears here.

    Comment by pejvan — March 4, 2008 @ 6:38 pm

  22. Excellent article – very clear.
    I work in an office environment where we don’t have authorization to drop files into the Excel path. Is there any other way of telling Excel to use the 2.0 version of the framework? This article

    http://msdn2.microsoft.com/en-us/library/y89ktbw6.aspx

    says Excel should use the latest version by default, and you need a config file if you want to use an earlier version. However, this contradicts what I am seeing (and, apparently, the experience of many users on this board.)
    Thanks for any info you can provide.

    Comment by Alan — April 9, 2008 @ 10:42 pm

  23. Alternative to Excel.exe.config
    I actually found a solution to comment 22 (my own question). To recap – Excel really should use the latest installed version of .NET by default. If its not, it’s because something is telling it to use an older version. While using the Excel.exe.config is one solution, it may not be practical for many, and really only disguises the problem. The key as usual, is the registry. Specifically, check for:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\policy\AppPatch\v2.0.50727.00000\excel.exe\{2CCAA9FE-6884-4AF2-99DD-5217B94115DF}
    You should see a value named “Target Version” and Data = “v1.1.4322″
    This is why Excel is using the older Framework. Removing or renaming this key should solve the problem.

    Comment by Alan — April 11, 2008 @ 2:30 pm

  24. I have had to do this several times but infrequently enough to have to look it up each time. This is by far the best guide I have ever seen about this. You have the dubious honour of being adding you to my “Useful” folder in my favourites. Thanks for making the effort to share.

    Comment by steve_randomno — April 11, 2008 @ 4:19 pm

  25. The targeting of .net 1 is unintended behaviour for office 2003 and beyonde according to the following blog: http://mcfunley.com/cs/blogs/dan/archive/2006/02/07/947.aspx

    A patch is available (see blog).

    Comment by steve_randomno — April 15, 2008 @ 5:52 pm

  26. Great article!!!

    Comment by Lior — April 28, 2008 @ 1:04 pm

  27. Thank you for a very helpful post. FYI, I have been struggling with the first section (“A Basic Walk Through”), which mysteriously refused to work for a while, for reasons which seem to be related to .Net 3.5. I finally got it to work, by explicitly targeting .Net 2.0 (I am using Visual Studio 2008 / .NET 3.5 / Office 2003) and adding the Excel.exe.config file with matching version as you describe, and it worked.
    Do you know if there are indeed specific issues related to .Net 3.5, or is it by sheer luck that I resolved the problem :) ?
    Cheers,
    Mathias

    Comment by Mathias — June 16, 2008 @ 12:14 am

  28. I’ve tried this with VS 2008/.NET 3.5/Office 2003 Pro and it still is balking. I’ve tried the targeting of .Net 2.0 and the Excel.exe.config file but still not working. I can see the type with intellisense but when i run the code, I get an object not found error (#91).

    It is a very interesting article, however and I’m sure it is on the right path. I just need to find where I’ve wandered off of it.

    Comment by Roger — July 10, 2008 @ 8:05 pm

  29. Duh. I missed the “NEW” in step seven where the testClass is declared.

    Dim testClass As New DotNetClass

    Comment by Roger — July 10, 2008 @ 10:29 pm

  30. Hi – Thanks so much for writing this article, I have had a hard time getting rid of the automation error I kept receiving due to my PC having several development environments installed. Your comment re. the Excel.exe.config file solved my problem! -Kenneth.

    Comment by Kenneth — July 17, 2008 @ 5:07 am

  31. This is the best article I have found on this subject.

    Your approach is excelent and provides the basic info that thoses of us with just a little experience needs.

    I had tried another example, and they left out the explanation for several lines of code which it appear wasn’t needed anyway, but was keeping me from getting their sample to work.

    Hope you publish more work such as this.

    The better we understand the basics, the more successful our projects will be.

    Comment by Allen — August 17, 2008 @ 12:19 am

  32. Thanks for the article and the links to other articles. The article on guidelines for com interop is particularly interesting.

    I’ve just spent the past few hours trying to solve the same problem as Hongus posted earlier – denied access to the registry. I suspect it’s a VS2008 issue. In case anybody else has the same trouble, I solved it by, on loading VS2008 from the start menu, right-clicking the icon and selecting ‘Run as Administrator’.

    I’m surprised this made a difference as I use an administrator account on my machine and have all the correct permissions for the registry. But it did the trick.

    Also, another snag I encountered is that my VS2008 appears to set the ComVisible assembly attribute (in the AssemblyInfo file) to false, which hides all the code in the assembly from COM. Adding the attribute [ComVisible(true)] to the class overrides this.

    Comment by Adrian — September 3, 2008 @ 8:31 pm

  33. Magnificent article, the Excel.exe.config sorted me out. I had been going mad all day. Thanks!

    Comment by Paul — October 13, 2008 @ 3:35 pm

  34. Wonderfull article! This is all i want to start with. I was stuck for 2 days, referred to a lot of websites to find an answer. I got some idea that was complicated.The VB.NET class attribute ” _” gave a breakthrough. But this is even simpler. I wonder why i dint find this post so far.

    Comment by Sunil — October 22, 2008 @ 10:17 am

  35. Really excellent, a big thanks to your brain and your R&D. Very clearly mentioned the procedure in you blog, again thanks for that.

    Comment by Lisso — October 24, 2008 @ 12:53 pm

  36. Hello,
    Thanks for your article. You explain very well. as for a French :)

    Comment by medee — January 28, 2009 @ 2:29 pm

  37. Error 430 Class does not support Automation or does not support expected interface.

    I have tried every trick in the book. I have followed this article to the T but is still get the above error. I am getting intellisense on the client machine, which means I can see the class, but it does not work.

    Please help!

    Shri

    Comment by Shri — February 13, 2009 @ 10:28 pm

  38. Hi,

    I figured out the problem mentioned above. I had deployed the assembly on the LAN and I had trust issues in the client machine. Now I have a new problem.

    After deploying the dll over the LAN I get a Runtime Error 80131700 Automation Error. I am using Excel 2003

    Comment by Shri — February 14, 2009 @ 10:37 pm

  39. I forgot mention, I am using VS2008 with .Net 3.5 SP1. I also strong named my assembly, so that issue should not be there.

    Comment by Shri — February 14, 2009 @ 10:47 pm

  40. Indeed, the best article in order to get started with C# Excel interaction. However, one minor point is missing. How to handle events if they fired in C#? Solution to that may allow to utilize multi threading in C# and do calls asynchroniously from Excel. So far I found nothing on this subject. Any suggestions?

    Thanks again,
    Sergey

    Comment by Sergey — April 16, 2009 @ 1:34 pm

    • I created a class that holds the array collection. We just need to return the class object after populating the arrays.

      Clase aArr
      {
      double [] abc;

      private setValue(double[] values)
      {
      this.abc = values;
      }
      }

      caller Code

      aArr objArr = new aArr();
      objArr.SetValue(PopulateddoubleArray);
      return objArr;

      Hope this helps you.

      Comment by Kethepalli — September 22, 2009 @ 5:31 pm

      • This answer for Rakesh. Sorry for confusion.

        Comment by Kethepalli — September 22, 2009 @ 5:33 pm

  41. I was able to implement this But the proble is when You try to retrun an array or Object It throw you and Exception on VBA side. Has anybody tried to implement that. Here;s My code.
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Runtime.InteropServices;
    using System.Reflection;

    [assembly: ComVisible(true)]
    [assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
    [assembly: AssemblyVersion("1.0.0.0")]
    [assembly: AssemblyFileVersion("1.0.0.0")]

    namespace ExcelFunc
    {
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class Functions
    {
    public double[] Add(double a, double b)
    {
    double[] ar = new double[1];
    ar[0] = a + b;
    return ar;
    }
    }
    }

    Function add_num()
    Dim f As New Functions
    Dim x() As Double
    x = f.Add(1, 1)
    End Function

    Comment by Rakesh — April 24, 2009 @ 4:32 pm

  42. Here ya go as requested.. hope you enjoy free xxx video Adult Free Video Free Porn Video Free Adult Video

    Redtube

     

    Comment by AdultFriendl — July 13, 2009 @ 6:46 pm

  43. The Excel.exe.config issue had me pulling my hair out! Thanks much!!

    Comment by Bob N — August 20, 2009 @ 3:28 pm

  44. First of all Very good tutorial. It is clear you understand this very well, I’m hoping you also understand my similar problem and can point me in the right direction.

    I am working with Visual Studio 2008 VB .net and Excel 2003 (and 2007 but that’s another problem)
    I would like to know what it would take to build a “Self-Registering” dll which contains an or multiple object which can be placed within the excel sheets as ole objects.

    Thanks in advance.

    Comment by Scott — September 23, 2009 @ 2:17 pm

  45. A very interesting article indeed. Specially you solved my problem by creating the config file for Excel.exe.

    I am having problems with building an installer for this dll to deploy somewhere. if you could help.

    Comment by Sohail Razzaq — October 6, 2009 @ 1:34 pm

  46. It is a nice article. I need help to understand the procedure to handle ActiveX control in a DLL. Actually, VB.NET permits to keep the ActiveX controls and ActiveX components in the same DLL.

    I would like to know if there is any procedure to use the ActiveX control from a DLL in Excel.

    Thanks. Any help is appreciated.

    Comment by Mahendra Verma — May 7, 2010 @ 4:59 pm

  47. nice article

    Comment by test — June 23, 2010 @ 12:53 pm

  48. Hi for those with the automation error. I think you will need to make sure your dll is in the same folder as your tlb and that your other assemblies that are not in the GAC are present in the folder when you run the regasm /codebase *name of dll* /tlb

    Comment by Mark — June 24, 2010 @ 11:20 am

  49. Hi All,

    I am trying to implement this in VS2008 with vb.net. I think that I have translated the code from CS to VB correctly but when I build I get an error message:

    “A project with an Output Type of Class Library cannot be started directly. In order to debug this project, add an executable project to this solution which references the library project. Set the executable project as the startup project.”

    Can anyone suggest how I might implement a vb.net class library in Excel VBA.

    Thanks,
    Bernie

    Comment by Bernie — September 3, 2010 @ 4:02 am

  50. Excel automation

    http://vb.net-informations.com/excel-2007/vb.net_excel_2007_tutorials.htm

    gever.

    Comment by gever — October 10, 2010 @ 11:14 am

  51. Thanks – was great help !!!

    Comment by Amit — November 5, 2010 @ 5:50 pm

  52. Very nice article.
    What about Excel 2010 and .NET 4?

    Comment by Developer — November 24, 2010 @ 3:16 pm

  53. Hi,

    Thanks for the article. I can get everything to work okay, but only if I create the object in Excel VBA as follows:

    Dim libObject As Object
    Set libObject = CreateObject(“CSharp_ExcelLibrary.ExampleObject”)

    If I were to use:

    Dim libObject As New ExampleObject

    then I get a ‘run-time 430′ error (class does not support automation or expected interface), whenever I go to use a method (although I get intellisense, etc).

    To add to the confusion, if I implement the Excel.exe.config file recommendation, then neither approach works (I then get ‘run-time error -214623576 (80131700)’).

    Both of the above phenomenon occur with both Excel 2007 and Excel 2003. I am building using VS 2008 and .Net v3.5 SP1 (in the Excel.config.exe file I just reference v3.5). I have compiled this on two seperate machines, with the same results each time.

    Any help would be greatly appreciated!

    Thanks,

    Alex

    Comment by AlexIoannides — November 29, 2010 @ 11:27 am

    • Alex, thank you so much for this post – I know it’s years old but maybe you’ll see my thanks!

      Did you ever discover *why* this worked this way?

      Comment by Maury Markowitz — November 14, 2013 @ 7:43 pm

  54. Perfect! Thanks so much!

    Comment by Shawn — December 3, 2010 @ 9:06 pm

  55. Thank you Rich. The [ClassInterface] attribute was missing, from the other examples I found, for getting Intellisense to work. FYI this just worked for me in Windows 7 Pro, DOTNET 4, Access and Excel 2007. Judging from the age of the original post we are still finding this useful. Thanks again!

    Comment by Jon — December 9, 2010 @ 3:42 pm

  56. Thank You Very Very Very… Much !!!!!!
    Article is very simple and step by step to understand :)

    Comment by rajesh — January 30, 2011 @ 10:54 am

  57. A great read. Bookmarked! =)
    Thanks!

    Comment by Andreas — June 26, 2011 @ 7:15 pm

  58. nice man save my day

    Comment by Anonymous — July 19, 2011 @ 1:30 am

  59. Works!!!
    I’m using Office 2010 VS2010 C# Express. I’m an engineer, not a programmer, but I’ve written a lot of code for my job.

    1. Adrian’s comment above (shown below) came in handy.
    “I’ve just spent the past few hours trying to solve the same problem as Hongus posted earlier – denied access to the registry. I suspect it’s a VS2008 issue. In case anybody else has the same trouble, I solved it by, on loading VS2008 from the start menu, right-clicking the icon and selecting ‘Run as Administrator’.
    2. Then had to use the Intellisense version of the code that Rich Newman posted.

    How can I create custom functions (built from VS C#) to add to Excel’s collection of functions?
    -Noel

    Comment by Noel — October 16, 2011 @ 7:37 pm

  60. [...] Edit : Solution creating an Excel.exe.config an putting the config in it solved the issue. See A Beginner’s Guide to calling a .NET Library from Excel [...]

    Pingback by WCF : Registering a service AND its config file — November 3, 2011 @ 7:19 pm

  61. Very nice & simple!!!

    Comment by Santosh — January 25, 2012 @ 4:29 pm

  62. Very Informative. Thanks! My C# library runs from VBA.

    But I could not debug it. I set the project Debug properties, external program etc. and when I run it, it does not break at any of my breakpoints in the C# code.

    Any help appreciated!

    Using VS2010.

    Thanks
    Ap

    Comment by Apka — January 25, 2012 @ 10:38 pm

  63. Fantastic! – This really saved me alot. Thanks for sharing the work. Martin S.

    Comment by Anonymous — February 10, 2012 @ 6:01 pm

  64. Doesn’t work on Office 2010 64-bit with .NET framework v4.0.30319. In VBA, I get “Runtime error 429: ActiveX component can’t create object.”

    Comment by Andi — February 12, 2012 @ 8:33 pm

  65. Thank you! Very good!

    Comment by António — April 5, 2012 @ 3:42 am

  66. Thank you, this saved me a great deal of time. Well written guide.

    Comment by Pamela — April 23, 2012 @ 7:45 am

  67. PS I am using Office 2007 on Windows 7, with VS 2010 (.NET framework v4.0.30319).

    Comment by Pamela — April 23, 2012 @ 7:48 am

  68. How to go about packaging & deploying this COM component to other machines? Thanks!

    Comment by Guntas (@peakit_twt) — May 13, 2012 @ 7:49 am

  69. Thanks for this article, it helped me debug into my class library from my Excel client. I struggled for awhile, and finally learned that a space character in a folder name in the .xlsm file pathname was causing my error. Renamed the folder w/o the space, changed my Command Line Argument to the new path name, and it worked like a charm. Check for space characters in your command line argument path if you continue to have problems launching a workbook client from VS.

    Comment by Pam — May 18, 2012 @ 8:22 pm

  70. Echoing the notes of thanks – this worked well for me and one of the projects I’m working on.

    Comment by John Drohan — May 24, 2012 @ 8:27 pm

  71. Thank-you for this article. I’ve been using this approach succesfully for a number of years and the Intellisense bit was new for me. I do have a problem now though – the approach has flat out stopped working since I upgraded to a new machine. I’m running Windows 7 64 bit Professional, VS2010, the 64 BIT version of Excel and .NET4 and I can’t instantiate the C# object from VBA. Everything else works just fine – I can add the reference to my type library and the the class and methods but my VBA code bombs with the dreaded “Active X Component can’t create object” error. There are two changes in my environment – O moved from .NET 3.5 to 4 and I moved to the Office 2010 64 bit app from the Office 207 32 bit (I’m pretty sure Office 2010 32 bit worked just fine too. I have tried the Excel.exe.config and registry key fixes to no effect. Anyone have any ideas?

    Comment by Doug Brennan — October 23, 2012 @ 7:50 am

    • Having the exact problem (64-bit-Office 2010 and with no problem on 32-bit). Still trying to figure out, but was able to achieve a temporary fix:

      In my case, looks like the installer is replacing the tlb but not the dll.. deleting the dll, tlb & ‘Repair’ the program from Control Panel worked.While on some other machines, just manually registering the dll/tlb (Regasm.exe) worked.

      Hope this helps.
      ap

      Comment by Anonymous — October 23, 2012 @ 2:30 pm

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

    Pingback by Links to link VBA to C# | Newton Excel Bach, not (just) an Excel Blog — November 3, 2012 @ 1:43 am

  73. This is the one article on the entire Internet that tells me what I needed to know on this subject!

    Many thanks.

    Comment by dougaj4 — November 3, 2012 @ 1:48 am

  74. [...] using a program called Regasm.exe.  This is described in the “Deployment” section of A Beginner’s Guide to calling a .NET Library from Excel, but that guide appears to have been written in the days before Windows Vista and Windows 7, and [...]

    Pingback by Using Regasm.exe | Newton Excel Bach, not (just) an Excel Blog — November 13, 2012 @ 4:24 am

  75. Having the same issue with 2010/64-bit (“Active X Component can’t create object” error). Any further progress by anyone on this?

    Comment by Anonymous — December 5, 2012 @ 1:52 am

    • For everyone having problem with 64bit Office:
      Are you sure that you used 64-bit RegAsm.exe?

      Comment by Lukas2 — February 2, 2014 @ 2:35 pm

  76. Also having same problem on 2010/64 bit (error 429: Active X Component can’t create object). Still searching for a solution.

    Comment by Michael — March 25, 2013 @ 7:33 am

  77. [...] This is the post I am following to get the solution to work with Excel. http://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-… [...]

    Pingback by VB.NET 64bit COM with c# referenced helper assembly class | BlogoSfera — May 8, 2013 @ 10:02 pm

  78. Using VS Express 2012 and Excel 2010. In Excel VBA References I can browse to DotNetLibrary.dll, but get the error “Can’t add a reference to the specified file”. Any suggestions?

    Comment by Martin Jacobs — October 3, 2013 @ 6:17 am

    • Because you should not browse. You should SELECT it. If it is registered for COM interop – it will be there in the list.

      Comment by Anonymous — April 15, 2014 @ 8:17 am

  79. Could you explain the reason causing versioning problem with early binding? Is the v-table in the new “produced” .tlb file after class redesign not properly created?

    Comment by Anonymous — January 8, 2014 @ 2:16 pm

  80. One of the best article. Well written guide.

    Comment by Isaac — April 16, 2014 @ 5:29 pm

  81. […] Hi there, welcome to the board! Not necessarily. Excel [2010] runs VBA 7.0, and does not tie to the .NET framework, so you really don't have access to that class library. You would if the solution was built in Visual Studio, but not in VBA. There are some tricky ways to reference COM add-ins with VBA, but it must be set as ComVisible, and the ClassInterfaceType must match as well. While I haven't done much of this, I think the logic would be more to create your C/VB add-in, ensuring it's ComVisible, create your class(es) appropriately, then set a reference from VBA to your project as to access your class. Here is an example. And here is a walkthrough example. […]

    Pingback by How to use .NET 3.5 class from Excel 2010 VBA — July 5, 2014 @ 3:43 am

  82. Hi Thanks for the procedure. It helped me to some extent. but i am getting the following error “The system cannot find the file specified”. Please note that i am using DLL in another machine. i am able to use dll in the machine which i performed build but not in other machines.

    Comment by vijay bhargav — September 4, 2014 @ 11:49 am

  83. […] with Register for COM Interop option in Visual Studio, or with Regasm utility (see Newman’s article for a nice […]

    Pingback by Unable to pass excel objects as parameters from VBA to .NET when using NetOffice interop — September 6, 2014 @ 11:24 am

  84. Thanks a lot! Finally a great tutorial!

    Comment by José Henrique — October 16, 2014 @ 12:01 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 82 other followers

%d bloggers like this: