VBA : Adding object to a collection increases memory usage

Question

I don't like asking questions in forums like that, because I believe that every question was asked before me, so I just use the search. But now I feel myself a little dumb cause it's the first time I didn't find any answers.

I have a simple question based on a piece of code :

Dim demo_1, demo_2 As Variant 'declare the variables
Dim DataCollection As Collection 'declare the collection
Set DataCollection = New Collection 'define the collection
demo_1 = import_demo(1, nb_var) 'load first dataset (+250 mb of memory)
demo_2 = import_demo(2, nb_var) 'load second dataset (+250 mb of memory)

So, in total, my program uses 500 mb of memory. Now I want to fill my collection with references to this objects :

DataCollection.Add demo_1 'adding reference to a collection (+250 mb of memory Why??)
DataCollection.Add demo_2 'adding reference to a collection (+250 mb of memory Why??)

So I repeat my question : "Why the ---- ?" Sorry.

Should adding an object to a collection increase memory usage in VBA, because I'm clearly not cloning ?

ThunderFrame · Accepted Answer · 2017-07-16 12:40:00Z

2

It looks like your import_demo(1, nb_var) function is returning an Array. Adding an array to the collection adds a copy of the array. If your function returns a 250MB array, then every copy of that array will add another 250MB.

See this simplified example:

Sub test()

  Dim coll As Collection
  Set coll = New Collection

  Dim arr As Variant
  arr = Array(1, 2, 3)

  'Add the array to the collection (makes a copy)
  coll.Add arr

  'Change the ooriginal array
  arr(0) = 4

  Debug.Print coll(1)(0), arr(0) 'Prints 1,4

End Sub

But it sounds like you want to work with references. For that, your function will need to return an object/class instance, before you add it to the collection.

If your function is intended to return a Range, your assignment to demo_1 = import_demo(1, nb_var) is implicitly calling the Range.[_Default] property (which is equivalent to Range.Value). In order to return the Range object, you need to use the Set keyword: Set demo_1 = import_demo(1, nb_var)

edited Jul 16, 2017 at 12:40

answered Jul 16, 2017 at 12:34

ThunderFrame

9,5162 gold badges31 silver badges64 bronze badges

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

2 Comments

Arsen Over a year ago

Thank you. That was clear. Can you recommend some literature or articles about that kind of information you helped me with ?

Patrick Lepelletier Over a year ago

if you declare variables with the right type instead variant, things get less messy.

Collectives™ on Stack Overflow

VBA : Adding object to a collection increases memory usage

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related