Array of VBA user-defined objects from C # library

Question

Reference Information:
I am building a SCADA system that runs on VBA, and I am looking for some C # features. I create a DLL in C # and get the basic data for data exchange 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 as follows:

 [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 level:

 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 VBA static method calls:

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

In VBA, I got the data like this:

 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 to return an Array or List of class "BE_Log"
EDIT: Here I am stuck: http://puu.sh/hnPGe/472ff863d0.png

I am trying to develop some of the Microsoft documentation without much luck.

The initial guide I followed this:
http://www.analystcave.com/excel-use-c-sharp-in-excel-vba/

He states the following, but I do not fully understand him.

If you use an array as an argument, be sure to use C # "ref" to get the link, for example. ref int [] ar

I think it has something to do with "MarshalAs" or the way I read data in VBA

Answer 1

If you use an array as an argument, be sure to use the C # option "ref" get by reference, for example. ref int [] ar

This is true, but you do not have methods that take an array argument, so it does not apply to your situation.

You tried to change your method signature:

 public Log CreateLogBook() 

to the signature that returns the array:

 public Log[] CreateLogBook()