VBA / VB6 mscorlib's ArrayList drop-in replacement with proper memory management and orders of magnitude faster than mscorlib.ArrayList, making use of twinBASIC's new language features and memory management techniques from VBA-MemoryTools.

Initially, this drop-in replacement for mscorlib.ArrayList was just to get rid of the 423 MB VMem overhead added to VBA projects when using its ArrayList implementation. But it also turned out to exceed the speed performance of mscorlib.ArrayList by far, along with a proper memory release/deallocation when destroyed or it goes out of scope. Which can't even be manually achieved with mscorlib.ArrayList as setting it to Nothing or .Clear 'ing it doesn't free any memory.

• IntelliSense support.

• Takes less than 0.35 MB of VMem to load on first use instead of the 423 MB taken by mscorlib. VBA apps in Win32 are limited to 2 GB, if you also add the non-existing memory deallocation of mscorlib.ArrayList, continued operations on mid to large datasets are a dead-end in using mscorlib.ArrayList.

• As a drop-in replacement, it is expected to provide the exact same output and functionality as when using mscorlib.ArrayList whithin VBA. Static members such as .Adapter or .Repeat can't be used from VBA so they're not included, nor the Type parameter in .ToArray(), which can't be used either. Additionally, all other members that can could be called or accessed even though they are totally useless from the VBA side, are included but hidden, as in duplicated members with similar names to overcome the missing method overloading feature in COM, such as .Sort_2.

• Unlike mscorlib.ArrayList, it allows plain VBA Arrays and other enumerable objects as input in parameters expecting a collection-like object (Of ICollection Type in mscorlib's ArrayList.cs).

  ' Example:
  .AddRange(Array( _
      "String at index 0", _
      Array(34, "Lorem Ipsum"), _
      Array(Now(), "Hello World!"), _
      256))

• Provides an advanced Enumerator allowing the use of For Each within subranges, backwards enumeration, custom iteration steps and direct access to the backing enumerator instance allowing an even wider set of possibilities while iterating the Enumerator.

• The Enumerator class is publicly accessible so you can reuse it anywhere else in your code.

• Using multidimensional arrays as elements is not supported by mscorlib.ArrayList but ArrayList seems to have no reason for that, they just work like any other value or reference types when being added as elements. ^{(If anyone encounters with such problems in ArrayList please post an issue)}

• ArrayList docs are available here but, as a drop-in replacement, you can also use the ones from the official .NET Documentation which has lots of usage examples, just ignore static members as they can't be called from VBA. You might also like to have a look directly at the source code of mscorlib.ArrayList in CSharp.

Percentages are calculated against mscorlib.ArrayList's timings from corresponding Win64 or Win32 results.

The lower the percentage, the better. 20% equals to a 5 times faster performance while 500% would be 5 times slower than their corresponding Win64 or Win32 execution time in mscorlib.ArrayList.

Where .Add ^(250x5000) equals to the following code.

For e = 0 To Iterations - 1     ' Iterations = 250
    For i = 0 To UBound(t)      ' UBound(t) = 5000 - 1
        .Add t(i)
    Next i
Next e

That's a total of 1,250,000 calls to .Add, taking only 134ms (Win64) instead of 3,579ms in mscorlib.ArrayList.

While the .Add method of VBA.Collection has similar performance as in our ArrayList, reading/accessing their values is potentially slow and gets exponentially worse depending on the number of elements it contains. A simple sequential read of 5,000 items in a VBA.Collection takes 59 ms, reading 10,000 items takes 266 ms, which is almost 5 times more with just twice the size but reading 100,000 items, takes 36,069 ms, 135 times slower just increasing 10 times it's size. Our ArrayList only takes 9 ms to read 100,000 items, increasing linearly, taking 94 ms to read 1,000,000 items.

So in order to include VBA.Collection in the table above, tests are iterated multiple times over 5,000 items instead of using bigger sizes.

• To @CristianBuse's VBA-MemoryTools, from which I discovered a whole new level in VBA programming, is what runs the most performance-critical parts behind ArrayList, and himself for his amazing and extensive support.

• LibMemory from VBA-MemoryTools is released under the MIT License.

• Everything else is released under The Unlicense into the public domain.