64-bit Excel 2010 cannot 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 GetSix () function, and it will return six. This no longer works in Excel 2010 64bit. I get a "429" runtime error: ActiveX component cannot create an object.

I tried changing the target platform to x64 instead of Any CPU, but then my code will not compile unless I uncheck the Register for COM interop option, so that it does not display SimpleLibrary.dll in my macbook because it is larger not registered.

Any ideas on how I can use my library with 64-bit version of Excel 2010?

Answer 1

You did not describe in detail how your .NET assembly was created. However, there are a number of steps required to provide a COM assembly:

• 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

• To register an assembly (all on one line), the following command is required:

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

  This creates a library file of type .tlb, which you will need to reference from your VBA project (Tools -> Links -> Overview ... in the VBA editor)

• Correct the VBA code:

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

Below you will find the instructions described in this article in the Microsoft Support Database:

How to invoke the assembly of Visual Basic .NET or Visual Basic 2005 from Visual Basic 6.0