2

I want to use System.Collections.ArrayList in my VBA script in Excel. Now I'm wondering: which .NET Framework version is required for that?

The reason I'm asking: on this site it explains the usage of that class and regarding a possible error message it strongly states (emphasis by me):

This is caused by not having the correct .Net Framework version installed. The correct version is 3.5. It doesn’t matter if you have a later version like 4.7, you must have 3.5 installed.

In contrary to that, Microsoft's offical documentation for ArrayList clearly lists quite a lot of .NET Framework versions under "Applies to".

Now, I clearly want to trust offical documentation more than a single webpage: as much as I googled, I haven't found another site confirming that restriction. But maybe it's just that 3.5 is the only version allowing the usage of that class in VBA.

So, please, can anyone tell me how to find out if I can use the ArrayList (or any other .NET class for that matter, if a generalization could be made) with all of the versions listed in the docs — or is it really just v3.5?

Oh, and just in case that this is relevant: I know about and use late binding to avoid the references that you could add to an Excel workbook, to avoid problems with the version specific references.

Dim arrLst As Object
Set arrLst = CreateObject("System.Collections.ArrayList")
braX
  • 11,506
  • 5
  • 20
  • 33
mh166
  • 361
  • 1
  • 4
  • 13
  • 1
    It seems the website you mentioned might be right. I have .NET Framework 4.8 and get an automation error when trying to use an ArrayList. – Storax Nov 09 '19 at 09:01
  • Typically, to use ArrayList in VBA you need to add a reference to mscorlib. If mscorlib is not already in your list of available libraries you need to browse to 'C:\Windows\Microsoft.NET\Framework\v4.0.30319' (or Framework64). – freeflow Nov 09 '19 at 10:52
  • 1
    @Freeflow: I did that for example with `ThisWorkbook.VBProject.References.AddFromFile "C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"` before I tried to use the array list. It really seems that you need 3.5 installed. – Storax Nov 09 '19 at 11:53
  • In my References the name is given as mscorelib.dll. I have a vague recollection that when I tried adding the .tlb it didn't work, but adding the .dll did. – freeflow Nov 09 '19 at 12:36
  • You have to add `"C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\mscorlib.tlb"` and you will get a reference to `mscorlib.dll`. I just did it and that's better than a vague recolletction, – Storax Nov 09 '19 at 14:23

1 Answers1

4

As said in the comments I did not have .Net Framework 3.5 installed and running 

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

gave an automation error. I installed .Framework 3.5 and the automation error disappeared.

For the installation of .Net Framework I followed the steps outlined in this documentation but you need admin rights on the PC.

For getting information on the installed .Net Framework version I used this powershell script

Storax
  • 11,158
  • 3
  • 16
  • 33