Excel 2010 64 bit can't create .net object

.net, c#, excel-2010, vba

Solution

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

Problem

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?

Original source