(Solved) – .NET Library class in vba – multi level domain object


I’m using .NET Library class in Excel VBA.
It works. I can compile and register it by RegAsm.
Using the Intercaces in .Net and COM attributes like ([InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]) , generally i see methods and objects
in VBA Intelisence and I can use it

But … I would like to use a complex multi-level object domain. Get it by some method and i have problem with list of child objects as a property of parent object.

My Method in C#

    public object GetParentWithChildList()
        var parent = new Parent
            ParentName = "John",
            Children = new List
                new Child {ChildName = "Tom"},
                new Child {ChildName = "Brian"},
                new Child {ChildName = "Eva"}
        return parent;

    public class Parent
        public string ParentName { get; set; }
        public object[] Children { get; set; }
    public class Child
        public string ChildName { get; set; }

object[] , I think is the best way to return list of objects for VBA/VB6
It works when i have 1 level object. e.g. i would like to return just list of objects.
object[] wokrs better than Child[] or List

And in VBA it works only partially:

(Main object calls LibDataAccess)

Sub GetParentWithChildListVbaTest()
Dim qda As LibDataAccess
Dim parent As parent

Set qda = New QgeDataAccess
Set parent = qda.GetParentWithChildList()

Debug.Print (parent.ParentName)   ' Works OK - it returns John

Dim child As child
Set child = parent.Children(0)     ' This line returns error: Wrong number of arguments or invalid property assignment
Debug.Print (parent.Children(0).ChildName)
End Sub

My questions are:
How to return an object with a list of subobjects as a property (prepared in .NET for VBA ) ?
Do I have the wrong code in C # or in VBA ?

Leave a Reply

Your email address will not be published. Required fields are marked *