Excel 2010 64 bit can't create .net object

Question

I have a simple class library that I use in Excel. Here is a simplification of my class...

using System;
using System.Runtime.InteropServices;

namespace SimpleLibrary
{
 [ComVisible(true)]
 public interface ISixGenerator
 {
  int Six();
 }

 public class SixGenerator : ISixGenerator
 {
  public int Six() 
  {
   return 6; 
  }
 }
}

In Excel 2007 I would create a macro enabled workbook and add a module with the following code:

Public Function GetSix()
    Dim lib As SimpleLibrary.SixGenerator
    lib = New SimpleLibrary.SixGenerator
    Six = lib.Six
End Function

Then in Excel I could call the function GetSix() and it would return six. This no longer works in Excel 2010 64bit. I get a Run-time error '429': ActiveX component can't create object.

I tried changing the platform target to x64 instead of Any CPU but then my code wouldn't compile unless I unchecked the Register for COM interop option, doing so makes it so my macro enable workbook cannot see SimpleLibrary.dll as it is no longer regsitered.

Any ideas how I can use my library with Excel 2010 64 bit?

Answer 1

You haven't described in detail how your created your .NET assembly. However, there are a certain number of steps required to expose the assembly to COM:

• Add the following attributes to your code:

  using System;
  using System.Runtime.InteropServices;
  
  
  namespace SimpleLibrary
  {
      [ComVisible(true)]
      [Guid("71F645D0-AA78-4447-BA26-3A2443FDA691")]
      public interface ISixGenerator
      {
          int Six();
      }
  
  
  [ComVisible(true)]
  [ProgId("SimpleLibrary.SixGenerator")]
  [Guid("8D59E0F6-4AE3-4A6C-A4D9-DFE06EC5A514")]
  [ClassInterface(ClassInterfaceType.AutoDispatch)]
  public class SixGenerator : ISixGenerator
  {
      [DispId(1)]
      public int Six()
      {
          return 6;
      }
  }
  
  
  }        
  

• Your assembly must be signed (Project -> Properties... -> Signing, create a strong key file and check the box to sign the assembly

• The following command is necessary to register the assembly (all in one line):

  C:\Windows\Microsoft.NET\Framework64\v2.0.50727\RegAsm.exe 
                    SimpleLibrary.dll /tlb SimpleLibrary.tlb /codebase
  

  This creates a .tlb type library file which you will have to reference from your VBA project (Tools -> References -> Browse... in your VBA editor)

• Adjust the VBA code:

  Public Function GetSix()
      Dim lib As SimpleLibrary.SixGenerator
      Set lib = New SimpleLibrary.SixGenerator
      GetSix = lib.Six
  End Function
  

You will find the steps described in more detail in this article on Microsoft's support database:

How to call a Visual Basic .NET or Visual Basic 2005 assembly from Visual Basic 6.0