I have a loop that adds userform controls to a collection. It's nothing exciting, but it did get me thinking about how I do things.
As the collection is needed in multiple places, I've just shoved it in a module and call it when I need it. This means that the collection is only in memory when it's needed, but it also means I'm running a loop every time I want to work with it.
I could have given the collection module level scope and created it the first time it was needed. This would have allowed me to run the loop once, but the collection would have persisted in memory.
From my research I've found out that 32bit excel has a memory limit of 1.75gbs (although this can be extended, and, ofc there's the 64bit version). And that vba excel is single threaded. As such, it seems like I should be prioritizing processor efficiency over memory efficiency unless I have a specific need to do otherwise. Not only will the application be faster, it's potentially more energy efficient.
Here's the rub - giving the collection module level scope opens it up to inadvertent changes. So, is there a way to create the collection once, but not give it module level scope? Better yet, is there a way to do this where it's only in memory when it's needed?
Thanks for reading, hopefully it's not a really dumb question.