Transferring objects from C # to VBA using COM Interop

Question

Is it possible to pass a custom object (like MyClass []) from C # to VBA using COM?

If not, is this the best solution for it to work?

Answer 1

I assume you are talking about Excel VBA in C # ...

here's the minimal C # class that does this in the default project 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 is VBA to try the class:

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

. , . , VBA "" w, . VB .NET, (, ) . , "" - , ... , . ( ) . TestClass, , , AutoDual , () VBA ( Intellisense).

, .