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





2016 automation (7)


I'm going to answer my own question, hopefully to spare others the hours of tedious drudgery I have just endured.

If you get this, it is because the .NET based COM assembly can't find the .NET framework

The solution is simple. Create a file containing the following

<?xml version="1.0"?>
<configuration>
  <startup>
   <supportedRuntime version="v2.0.50727"/>
  </startup>
</configuration>

Call it "Excel.Exe.Config" and place it in the same directory as "EXCEL.EXE"

Problem solved!

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.




Custom C# DLL gives VBA 429 error

Now it's working!

I needed to register the DLL using the REGASM with the option /codebase

C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm /codebase TesteLib.dll 

When I include the option /codebase it generates a warning, but it works: RegAsm warning: Registering an unsigned assembly with /codebase can cause your assembly to interfere with other applications that may be installed on the same computer. The /codebase switch is intended to be used only with signed assemblies. Please give your assembly a strong name and re-register it.

Then, In VS2012 I marked the option Project Properties > Signing > Sign the assembly > New... and run REGASM. The warning faded out and everything seems to be fine now :)

Thanks!




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




RC1, I tested this with your code from VBScript and from within Office 2007's Excel, everything works fine.

Since your able to create the COM object from within a VB6 form we should assume that your .net framework is ok. Can you rule out issues with VBA? Can you create a .vbs file and put this in it:

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

Save the file and double click it. If you get an error, then I would think there is an issue with it being registered, if you don't get an error, then I would look at Office and VBA and see if something is missing or not installed properly.

Another option is to add a reference to COM object and use early binding? I think you might need to export a typelibrary first, but you should be able to add a reference and simple new the object up.







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.




Its probably a little late to help you but the object you want DynamicJSONObject is included in the System.Web.Helpers.dll from the ASP.NET Web Pages package, which is part of WebMatrix.