In my VBA the second line:

Dim numericColumnNames As Object
Set numericColumnNames = CreateObject("System.Collections.ArrayList")

triggers this error:

Run-time error `-2146232576 (80131700)`:

Automation error.

And I have already found the solution (link). But is this the only possible solution? Could I use any different object instead? What could be done to avoid installing additional packages? To "install additional packages" is really frustrating for the end users.

CodePudding user response：

You cannot just add any .NET object to a VBA project. You can only use COM visible objects in VBA. You could write a COM wrapper around any .NET methods you want to use but then you need to register that DLL on each system where your VBA code will be used. It's a lot of work, and in your case for nothing.

Even if you could, the documentation tells you not to use this object at all:

Important

We don't recommend that you use the ArrayList class for new development. Instead, we recommend that you use the generic List class. The ArrayList class is designed to hold heterogeneous collections of objects.

So the answer is to use a VBA Collection object.

Dim numericColumnNames As New Collection