VBA array of User-Defined objects from a C# DLL

Question

Background information:
I'm building a SCADA system, which runs on VBA and I seek some of the powers of C#. I build a DLL library in C# and got basic data to flow between the DLL and VBA.

[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class BE_Log
{
    public string DateTime
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string User
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string SCADA
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string Tag
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string Area1
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string Area2
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string Description
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string ValueOld
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }
    public string ValueNew
    {
        [return: MarshalAs(UnmanagedType.BStr)]
        get;
        [param: MarshalAs(UnmanagedType.BStr)]
        set;
    }

    public BE_Log(string DataTime, string User, string SCADA, string Tag, string Area1, string Area2,string Description)
    {
        this.DateTime = DateTime;
        this.User = User;
        this.SCADA = SCADA;
        this.Tag = Tag;
        this.Area1 = Area1;
        this.Area2 = Area2;
        this.Description = Description;
    }

    public BE_Log(string DataTime, string User, string SCADA, string Tag, string Area1, string Area2, string Description, string ValueOld, string ValueNew)
    {
        this.DateTime = DateTime;
        this.User = User;
        this.SCADA = SCADA;
        this.Tag = Tag;
        this.Area1 = Area1;
        this.Area2 = Area2;
        this.Description = Description;
        this.ValueOld = ValueOld;
        this.ValueNew = ValueNew;
    }

}

And I returned the class like this:

[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class TI
{
    private BLL_LogBook bll;

    public TI()
    {
        bll = new BLL_LogBook();
    }

    [return: MarshalAs(UnmanagedType.SafeArray, SafeArraySubType=VarEnum.VT_USERDEFINED)]  //  SafeArrayUserDefinedSubType = typeof(BE_Log)
    public BE_Log[] CreateLogBook()
    {
        List<BE_Log> logs = bll.GetLogEntry();
        return logs.ToArray();
    }
}

My data layer:

public class BLL_LogBook
{
    public List<BE_Log> GetLogEntry()
    {
        List<BE_Log> logs = new List<BE_Log>();
        logs.Add(new BE_Log("05-05-2015", "some user", "scada01", "LA010NDA10CU12XQ12", "Ribe", "Esbjerg", "Some short description"));
        logs.Add(new BE_Log("06-05-2015", "test user", "scada01", "LA010NDA10CU12XB05", "Herning", "KBH", "Some long description"));
        logs.Add(new BE_Log("07-05-2015", "normal user", "scada02", "LA010NDA10CU12YQ01", "Åhus", "Tønder", "Some test description"));

        return logs;
    }
}

The static method VBA is calling:

static class UnmanagedExports
{
    [DllExport]
    [return: MarshalAs(UnmanagedType.IDispatch)]
    static Object TI_Object()
    {
        return new TI();
    }
}

In VBA i got the data this way:

Declare Function TI_Object Lib "<path>\\TJI.dll" () As Object

Sub TestTheTestClass()
    Dim TJI As Object
    Set TJI = TI_Object()

    Dim test As Variant
    test = TJI.CreateLogBook()

    Dim log As Variant
    Set log = test(0)

    Debug.Print log.User
End Sub

Now to my question:
How do I return an Array or List of the class 'BE_Log'
EDIT: This is where I'm stuck: http://puu.sh/hnPGe/472ff863d0.png

I have been trying to work out some of Microsofts documentations, without much luck.

The orginal guide i followed was this one:
http://www.analystcave.com/excel-use-c-sharp-in-excel-vba/

He states the following, however I dont completely understand it.

If you are using an array as an argument be sure to use the C# “ref” get by reference option e.g. ref int[] ar

I think it's something to do with 'MarshalAs' or the way I read the data in VBA

to StackOverflow · Accepted Answer · 2015-04-17 10:25:05Z

1

If you are using an array as an argument be sure to use the C# “ref” get by reference option e.g. ref int[] ar

This is true, but you don't have any methods that take an array argument, so it doesn't apply to your situation.

Have you tried changing your method signature:

public Log CreateLogBook()

to a signature that returns an array:

public Log[] CreateLogBook()

answered Apr 17, 2015 at 10:25

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

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

14 Comments

Rasmus Plats Over a year ago

I did try with both an array and a list public Log[] CreateLogBook() public List<BE_Log> CreateLogBook()

to StackOverflow Over a year ago

"I did try..." - and what was the result?

Rasmus Plats Over a year ago

I cant get VBA to read the data, or maybe I didnt do the "Marshal" right? Can you provide an exampel of how to read an array in VBA, from a DLL?

to StackOverflow Over a year ago

@RasmusPlats - what happens when you try to call the method? Do you get an exception (VBA runtime error), and if so, what error message? Do you get something unexpected returned, in which case inspect it with the VBA debugger

to StackOverflow Over a year ago

Your screenshot shows "<No Variables>" for test(0) and test(1) which are of type BE_Log. I'd expect to see this if BE_Log was a collection type, and the collections were empty. Also you are accessing log.DateTime which isn't a valid property of the Log class you posted. It would help if you posted a complete, consistant sample - see stackoverflow.com/help/mcve

|

Collectives™ on Stack Overflow

VBA array of User-Defined objects from a C# DLL

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related