Cannot call C# library exposed through COM in Excel VBA

Question

I have tried to create a very basic C# library and expose it through COM interface so that I can access it from VBA, but at runtime it throws an error "Class does not support Automation or does not support expected interface"

I have read all the online tutorials and done exactly as specified so am very confused why this is. My basic class is

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace PDCCOMTest
{

    [ComVisible(true)]
    public interface IPDCCOMTest
    {
        string SubmitRequest(string requestXML);
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    [ComSourceInterfaces(typeof(IPDCCOMTest))]
    [ProgId("PDCCOMTest.PDCCOMTest")]
    public class PDCCOMTest : IPDCCOMTest
    {
        public string SubmitRequest(string requestXML)
        {
            return "hello world";
        }
    }
}

And in the project settings I have gone to Application -> Assembly Information -> Make assembly COM visible (Set to ON) and Build -> Register for COM interop (Set to ON)

I compile and build the solution, then load up Excel and set up a basic button macro sheet. In this, I go to References and under the list of COM objects, my new C# COM assembly appears and I check it. I am then able to discover the types with Intellisense and the SubmitRequest method is available from Intellisense so it is picking all that up fine. The VBA code is as follows

Private Sub CommandButton1_Click()
    Dim oObj As New PDCCOMTest.PDCCOMTest
    Dim text As String
    test = oObj.SubmitRequest("test")
End Sub

When I then run the sheet and click the button, it throws the error on the last line of the Sub above, ie the SubmitRequest() line

"Error 430 : Class does not support Automation or does not support expected interface"

Can anyone please suggest what I have overlooked and why this is happening?

Thanks, James

Probably has nothing to do with it but you have text and test in your vba code. — assylias
– assylias, Commented May 21, 2013 at 12:45
Ah good spot. It hasn't fixed the error no, but thanks for pointing that out — user2405521
– user2405521, Commented May 21, 2013 at 13:16
Remove the ClassInterface(ClassInterfaceType.None) attribute — Simon Mourier
– Simon Mourier, Commented May 21, 2013 at 13:27
You need a GUID for your interface and another one for your class — steveo40
– steveo40, Commented May 21, 2013 at 16:06
Ok looks like I've found the solution. In Excel.exe.config in the program files directory, there is a line <startup useLegacyV2RuntimeActivationPolicy="true"> If I set this to false it works, so clearly some issue with .NET runtime versioning. Any idea why this might be and how I can fix it in my VS2010 project so that I can leave this option as "TRUE" and have it succeed? — user2405521
– user2405521, Commented May 21, 2013 at 16:12

Community · Accepted Answer · 2017-05-23 11:50:10Z

0

This works on my machine so your code is probably fine. If I were to hazard a guess, I would say that it looks like a .NET loading issue, i.e. Excel has loaded some other .NET add-ins and loaded the CLR v1 or such.

Take a look at this SO answer - hope it helps.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered May 21, 2013 at 13:38

Chris Spicer

2,1941 gold badge14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot call C# library exposed through COM in Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related