3

I have upgraded from Windows 7/Visual Studio 2015 to Windows10/Visual Studio 2019. I wish to create a C# COM object which I can call from Excel(365) VBA code.

Using Win 7/Visual Studio 2015 the following very simple code compiles as a C# class library and creates a COM object that I can call successfully from Excel 2013 VBA:-

using System.Runtime.InteropServices;
namespace TestLib {
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class C_Hello {
        public string Hello() {
            return "Hello";
        }
    }
}

In the Assembly Info I have ticked the checkbox "Make the Assembly COM Visible" and in the Build info I have ticked the checkbox "Register for COM interop"

The Excel 2013 VBA code is equally simple:

Sub Test()
    Dim x As TestLib.C_Hello
    Set x = New TestLib.C_Hello
    ActiveSheet.Range("C3").Value = x.Hello
End Sub

where TestLib is the C# COM module created in Visual Studio.

In the Win 7/VS2015/Excel 2013 environment everything works fine, but under after transferring and compiling under Win 10/ VS2019 the Excel 365 VBA code errors at the line:-

Set x = New TestLib.C_Hello

with the error message

Run Time error '-2147221164 (80040154)'

Class not registered

Can anyone tell me what I have to do to get this working in the Windows 10 / VS2019 / Excel 365 environment.? Is it Windows 10 or Visual Studio 2019 that is doing things differently?

Tony H
  • 61
  • 3
  • Have you registered the assembly? – GSerg Jun 01 '20 at 14:45
  • I haven't registered the Assembly, under Win7 /VS2105 I did not have to do this, and I thought that ticking the checkbox Register for COM interop was the equivalent of doing this. I'll try it. – Tony H Jun 01 '20 at 14:49
  • 1
    It should be (provided you are actually compiling from VS on that machine). Then the question is, what is the [bitness](https://stackoverflow.com/a/3534693/11683) of your project and of Excel? – GSerg Jun 01 '20 at 14:56
  • I've just tried to register TestLib.dll using regasm. It made no difference. – Tony H Jun 01 '20 at 15:02

2 Answers2

1

Make sure the bitness of excel matches your build in VS. You will likely need to add an x86 or x64 build for your project.

It is possible to register an AnyCpu assembly, but visual studio does not do so with the “Register for COM interop” for both. You can manually do so by running both the 32- and 64-bit versions of regasm manually or from a post-build script.

Failing that, you might use ProcMon to check what registration or file Excel is looking for and not finding. Warning, though, that unless you are familiar with the “normal” errors which occur when running it is easy to troubleshoot a red herring. I would recommend filtering by “Path” contains “TestLib.C_Hello” then chase it from there.

Mitch
  • 21,223
  • 6
  • 63
  • 86
  • When I go into the object browser in Excel and look for the library TestLib it displays the C_Hello class and its members correctly - as I would expect. – Tony H Jun 01 '20 at 15:13
  • I’d still recommend verifying the bitness matches. An easy test is with the 32- and 64-bit versions of PowerShell: `New-Object -ComObject Progid.Goes.Here` – Mitch Jun 01 '20 at 15:15
  • As an XY solution, perhaps consider using ExcelDNA if it fits your use. It does permit excel addins without the frustrations of COM. (Though I am an ardent COM defender) – Mitch Jun 01 '20 at 15:16
0

The solution in the Win10 / VS2019 environemnt was to change the Platform Target in VS's Build Tab from Any CPU to x64, and then it worked.

In the Win7 / VS2015 environment a Platform Target of Any CPU worked fine.

Tony H
  • 61
  • 3