Accessing the C# Lists from VBA

[Tanvi]

Hi Team,

Following is the code from C# (from API source code) which returns as List from JSON output.
Now I am trying to access these lists from VBA, but I am not able to view them in VBA.

I've done all the initial setups like creating an interface, GUID, and all namespaces. I am able to access the methods from VBA, but not the lists returned from C#.

Please guide me, as I am still connecting the dots.

public OutputBaseClass getOrderBook()
 {
    OutputBaseClass res = new OutputBaseClass();
    GetOrderBookResponse bres = JsonConvert.DeserializeObject<GetOrderBookResponse>(Json);
    res.GetOrderBookResponse = bres; //here in this response I am getting a list
    res.status = bres.status
 }
 
 public class OutputBaseClass
 {   
     public List<OrderBookData> data { get; set; }
     public GetOrderBookResponse GetOrderBookResponse { get; set; }
     public bool status { get; set; }
 }
 
 public class GetOrderBookResponse
 {
     public List<OrderBookData> data { get; set; }
     public bool status { get; set; }
 }

 

[Skydiver]

In general, if you want to Interop with VBA or COM in general, arrays work better than lists. So copy your list contents in an array and return that instead.

I've heard varying amounts of success using the older .NET Framework 1.1 ArrayList class to doing Interop, but my recommendation is to steer away from the ArrayList. Stick with the modern List<T>>. Just copy to an array for those times when the VBA code needs a set of things, or take an array when the VBA code needs to pass you a set of things.

 

[jmcilhinney]

Note that you can call ToArray on a List<T> and you can either pass an array to a List<T> constructor or call AddRange on an existing List<T>, if you want to work with lists in general but with arrays specifically for Interop.

 

[Tanvi]

Note that you can call ToArray on a List<T> and you can either pass an array to a List<T> constructor or call AddRange on an existing List<T>, if you want to work with lists in general but with arrays specifically for Interop.

so, I've modified the code as below, but when I check in VBA, the Odata is showing like a type mismatch, (I tried changing into a string [] in C#, but in c# it is throwing an error it cannot convert the type), Could you please correct me.

public OutputBaseClass getOrderBook()
 {
    OutputBaseClass res = new OutputBaseClass();
    GetOrderBookResponse bres = JsonConvert.DeserializeObject<GetOrderBookResponse>(Json);
    res.GetOrderBookResponse = bres; //here in this response I am getting a list
    res.odata = bres.data.ToArray();  //trying to convert List to Array() but if I change the type of odata to string[], getting  error cannot convert in C# 
//and if I continued with object[], getting error in VBA type mismatch variant
    res.status = bres.status;
 }

 public class OutputBaseClass
 {
     public GetOrderBookResponse GetOrderBookResponse { get; set; }
     public bool status { get; set; }
     public object[] odata {get; set;}
 }
 public class GetOrderBookResponse
 {
     public List<OrderBookData> data { get; set; }
     public bool status { get; set; }
 }

 

[Skydiver]

That's because you can't magically make a List<OrderBookData> into a string [] merely by calling .ToArray(). When you call .ToArray() on a List<OrderBookData>, it will return a OrderBookData [].

 

[Tanvi]

That's because you can't magically make a List<OrderBookData> into a string [] merely by calling .ToArray(). When you call .ToArray() on a List<OrderBookData>, it will return a OrderBookData [].

Hi Skydiver, again to rescue thanks for the response.
yes, The OrderbookData has all the string variables, so any alternative. But If I declare as object[] in c#, VBA throws an error of type mismatch variant.
I need that list to be accessible from VBA.
is the below statements will help, as I modified the code after checking from another forum. Skydiver, this is the last part of my struggle.
I am searching forums for the last 2 days. I can see we can convert the c# list to collections of VBA. But my bad I am not able to get the inside data.
Also, with my mere skills, I don't want to mess up the source code too much.
Can you please just give me some sample examples with the below functions?

public OutputBaseClass getOrderBook()
 {
    OutputBaseClass res = new OutputBaseClass();
    GetOrderBookResponse bres = JsonConvert.DeserializeObject<GetOrderBookResponse>(Json);
    res.GetOrderBookResponse = bres; //here in this response I am getting a list
    bres.data.ToArray().CopyTo(res.odata, 0); //here its throwing some weird error that a parameter value cannot be null in vba
    res.status = bres.status;
 }

 public class OutputBaseClass
 {
     public GetOrderBookResponse GetOrderBookResponse { get; set; }
     public bool status { get; set; }
     public string[] odata { get; set; }
 }
 public class GetOrderBookResponse
 {
     public List<OrderBookData> data { get; set; }
     public bool status { get; set; }
 }

 

[Tanvi]

That's because you can't magically make a List<OrderBookData> into a string [] merely by calling .ToArray(). When you call .ToArray() on a List<OrderBookData>, it will return a OrderBookData [].

Hi @Skydiver, just got the leads like, I can do like below

String[] Values = bres.data.Select(x => x.text).ToArray();

"text" is one of the values in OrderBookData, so can you help how can I convert all the values in orderbookdata to the array.
is there any 1 liner solution code.