Can't instantiate a COM object written in C# from VBA (VB6 ok)


Question

Using VS 2008, here is my COM object

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Windows.Forms;

namespace TestCom
{    
    [Guid("9E5E5FB2-219D-4ee7-AB27-E4DBED8E123E")]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("Test9.COMINT")]
    public class TestComClass  
    { 
        public void Init(string userid, string password)
        {
            MessageBox.Show(string.Format("{0}/{1}", userid, password));
        }       
    }
}

If I build this and register it on a production machine as follows

REGASM /CODEBASE TESTCOM.DLL

From a simple VB6 app this works fine

Private Sub Form_Load()
  Dim o As Object
  Set o = CreateObject("Test9.COMINT")
  o.Init "A", "B" 
End Sub

This exact same code called from VBA in Excel gives

"automation error" (0x80131700)

Everything works fine on a development machine, just not on a production machine with just .NET and MS Office installed.

Update

I think this is something to do with the .NET framework not being initialized properly, when running under Excel. If I use Filemon I can see it skip around looking for MSCORWKS.DLL. When I call the same object from VBScript, it finds MSCorwks.dll fine.

When I called CorBindToCurrentRunTime from VBA to try to forcibly load the CLR, interestingly I get the exact same HRESULT (0x80131700) as when I do CreateObject() in VBA.

Therefore I think it is a framework initialization issue.




Passing objects from C# to VBA using COM Interop

I assume you're talking about Excel VBA to C# ...

here's a minimal C# class that does it, in a project w default name ClassLibrary1:

using System;
using System.Runtime.InteropServices;

namespace Tester
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        public double D { get; set; }  // simple property to get, set a double
        public string S { get; set; }  // simple property to get, set a string
    }
}

and here's VBA to try the class out:

Private Sub foo()

Dim X As New ClassLibrary1.TestClass

X.S = "Hello"
Debug.Print X.S ' prints "hello"

X.D = 12
Debug.Print X.D ' prints a 12

End Sub

and here are the extra things you need to do to make this work:

(1) in C# Project...Properties...Build ==> check "Register for COM interop
(2) in C# Project...Properties...Application...Assembly Information ==> 
    check "Make assembly COM-visible"
(3) in VBA ... Tools ... References, browse to the C# bin output directory and select the "*.tlb" file

Note: this scheme may fail depending on what you add to the class - I don't think VBA will "see" static classes or classes w other than default constructors. You also cannot map VB collections to .NET collections, but you will be able to pass basic types (double, long) and arrays of the basic types back and forth. Also - the "Autodual" option used is a cheap way to get methods exposed ... easy to get started but less efficient and exposes all public methods. Better practice (but more work) would be to set up your own interfaces. If you expand the members of this TestClass to include instances of other classes you have defined, and if you likewise expose those class methods via AutoDual or via hand-coded interfaces, then those classes and their (non-overloaded) methods will likewise be visible in VBA (with Intellisense).

Hope this helps.




How to use a Control created in C# as a form in VB6

I have set the control like this when loading the data:

   Set mctldemo = Controls.Add("Democtl.Control", "ctlDemo", Me)

But forgot to mention the ClassInterface as shown in this link

[ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("Democtl.Control")] 

Thank you all for responding to my question and thank you Nadeem_MK for the link





Tags