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

Question

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.

VBA is not VB.Net. It does not support .Net so cannot deal with List<>. You say you are calling the Web Service via a custom c# DLL. Can you not alter the DLL to convert the List to an Array? — Jonathan Willcock
– Jonathan Willcock, Commented Oct 12, 2021 at 16:46
Try declaring result As ArrayList. In order to do that, please add a reference to mscorlib.tlb from "Framework version 3.5". In fact, I will post a piece of code able to automatically add the necessary reference... — FaneDuru
– FaneDuru, Commented Oct 12, 2021 at 16:50

FaneDuru · Accepted Answer · 2021-10-12 16:53:30Z

1

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.

answered Oct 12, 2021 at 16:53

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

4 Comments

Ahmed Aboumalek Over a year ago

Should this work instead? Dim result ----- Set result = CreateObject("System.Collections.ArrayList") ----- result = MyObject.getInterventions("", "", "123456789", "", "", "")

Ahmed Aboumalek Over a year ago

Also, the whole point of this is avoiding to have to edit the DLL because that won't be very convenient for our client. I'm not sure if that would be possible however.

FaneDuru Over a year ago

@It will not make to much difference if you do not adapt the dll. It is early binding. I suggested the reference, only to have access to intellisense suggestions. The idea is that there is no VBA object able to deal with the C# list object result. At least, I do not know such one. I am afraid that the client will be less satisfied if the existing dll will not work. You will offer an update, changing the previous dll. But, didn't you check it before delivering it to the customer?

Ahmed Aboumalek Over a year ago

This wasn't a part of the initial specs which were checked. The client decided to change his mind during the testing phase and now we're trying to figure out a way to make it happen. Unfortunately, it seems like the only way to do this would be adding a new method for this purpous in the WebService which will require updating the DLL too. Inconvenient but it is what it is. Thank you very much for your help.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related