How to get data in Access VBA from a C# method that returns an object List-CodePudding

I'm calling a C# WebService method called getInterventions() on a VBA Access 2003 application through a custom DLL. The signature of this method is as follows:

List<Item> getInterventions(string, string, string, string, string, string)

Item is a custom defined class.

When I try to retrieve the result of getInterventions() on VBA Access code, it pops a VBA Runtime error 242 : object required

The following is my code:

        Dim WsObject As Object
        Dim result As Object

        Set WsObject = CreateObject("Namespace1.Path.To.Class") 'This isn't the actual DLL path as I cannot share that
        
        'Set result = WsObject .getSingleIntervention("123, "0", "123456789", "") ' this works
        
         Set result = WsObject .getInterventions("", "", "123456789", "", "", "") 'this doesn't work
        
         If result Is Nothing Then
           'do some stuff
        Else
           'do some other stuff
        End If

getSingleIntervention() is a similar method which returns a single object rather than a list of objects. Returning the result of this method works without issues (see commented line). This proves that both the WS & DLL calls work. This method is defined as follows:

Item getSingleIntervention(string, string, string, string)

I have tested calling getInterventions() directly from the C# code via Visual Studio 2015 with the same parameters I'm passing in my VBA code and it worked. This proves that it's not an issue with parameters or the method content.

My conclusion: I am guessing it's something to do with the fact that I can't simply store a C# List of objects into a VBA Object.

Any help would be appreciated, thank you.

CodePudding user response：

Please, automatically add mscorlib.tlb reference, running the next code:

Sub addMscorlibRef() 'necessary to use ArrayList
  'Add a reference to 'Mscorlib.dll':
  'In case of error ('Programmatic access to Visual Basic Project not trusted'):
  'Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings->
  '         check "Trust access to the VBA project object model"
  If dir("C:\Windows\Microsoft.NET\Framework64\v4.0.30319", vbDirectory) = "" Then _
     MsgBox "You need to install ""Framework version 3.5"".": Exit Sub
   On Error Resume Next
   Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\mscorlib.tlb"
   If err.Number = 32813 Then
        err.Clear: On Error GoTo 0
        MsgBox "The reference already exists...": Exit Sub
  Else
        On Error GoTo 0
        MsgBox """Mscorlib"" reference added successfully..."
  End If
End Sub

Then try declaring Dim result As ArrayList.

The ArrayList is the same one that is used in C#. Then, adapt the dll to use such an object. As you deduced, no any object able to be used in VBA can receive the C# list object content.