Skip to content

VBA / VB6 ArrayList implementation w/ proper memory management and orders of magnitude faster than mscorlib.ArrayList

License

Notifications You must be signed in to change notification settings

Theadd/ArrayList

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

39 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Github Repository Badge Issues Badge Releases Badge Unlicense Badge

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.

Features / Improvements

  • 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)

Documentation

  • 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.

Performance Tests Results

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.

  ArrayList Class mscorlib.ArrayList VBA.Collection
  x64 x32 x64 x32 x64 x32
.Add
(x5000)
0 ms 6% 0 ms 4% 17 ms 23 ms 0 ms 6% 1 ms 4%
.Add
(250x5000)
134 ms 4% 181 ms 3% 3579 ms 6302 ms 186 ms 5% 184 ms 3%
.Clone
(50x5000)
3 ms 100% 5 ms 167% 3 ms 3 ms 2773 ms 92433% 2674 ms 89133%
.Insert Index:=0
(20x1000)
9 ms 4% 10 ms 6% 209 ms 181 ms 3 ms 1% 4 ms 2%
.Insert Index:=RND
(2x5000)
37 ms 49% 43 ms 41% 75 ms 104 ms 147 ms 196% 124 ms 119%
.Item
(Read) SEQ (20x5000)
9 ms 2% 10 ms 2% 421 ms 644 ms 1125 ms 267% 1052 ms 163%
.Item
(Read) SEQ+RND (20x5000)
19 ms 3% 21 ms 2% 735 ms 1135 ms 2198 ms 299% 2142 ms 189%
.RemoveAt Index:=0
(x5000)
5 ms 22% 4 ms 13% 23 ms 31 ms 1 ms 4% 1 ms 3%
.RemoveAt Index:=RND
(x12000)
70 ms 34% 42 ms 20% 203 ms 209 ms 910 ms 448% 675 ms 323%
.RemoveAt Index:=LAST
(x12000)
1 ms 3% 2 ms 3% 33 ms 58 ms 508 ms 1539% 340 ms 586%
.GetRange Index:=RND
(1000x5000)
2 ms 18% 3 ms 16% 11 ms 19 ms
.AddRange Range
(x1000)
77 ms 31% 176 ms 103% 249 ms 171 ms
.GetEnumerator
(For Each) (10x100000)
111 ms 14% 114 ms 17% 775 ms 659 ms 25 ms 3% 25 ms 4%
.Sort
(100000)
167 ms 293% 181 ms 503% 57 ms 36 ms
.Sort w/ Comparer
(100000)
494 ms 37% 453 ms 18% 1320 ms 2473 ms

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.

Acknowledgments

  • 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.

License

About

VBA / VB6 ArrayList implementation w/ proper memory management and orders of magnitude faster than mscorlib.ArrayList

Topics

Resources

License

Stars

Watchers

Forks

Packages

No packages published