3

I have a loop that adds userform controls to a collection.

As the collection is needed in multiple places, I 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.
I would have run the loop once, but the collection would have persisted in memory.

I found that 32bit Excel has a memory limit of 1.75Gb (although this can be extended, and there's the 64bit version) and that VBA excel is single threaded.
It seems 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.

Giving the collection module level scope opens it up to inadvertent changes.

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?

Improve this question
6
  • 2
    How many controls does this userform have? It would take an insane amount of controls in order to notice any real performance issues just from a For . . . Next loop. Commented Jul 22, 2017 at 16:20
  • The loop's from a bit of code that deals with control visibility and form resizing. It could be slotted into any project that wanted that functionality - so we could be talking about as many controls as you can get on a form. That being said, I can't imagine a form that would cause performance issues. There are circumstances that it could be of benefit, though. I've found this as an example - softwaresalariman.blogspot.co.uk Also, I do wonder about potential energy savings across large organisations. Ofc, it would take a lot more than this Commented Jul 22, 2017 at 17:44
  • I'm more concerned about validation in that case. If the user modifies the form after creating the collection, errors will happen. The only way to 100% not do that is to get the collection at run time. Commented Jul 22, 2017 at 19:05
  • I think I see where you're coming from. Wouldn't that only be an issue if controls were being added/removed? Commented Jul 22, 2017 at 19:18
  • True. But if you (or someone else) decides to make changes to your forms in the future, you could have some problems. Seems like a minor boost in performance at the cost of security IMO. Commented Jul 22, 2017 at 19:58

3 Answers 3

Reset to default
0

I don't think that there is any way to create a scope consisting of functions which is smaller than a module (which is what you seem to want).

If you are worried about accidental modification then you can create a class which implements a read only array. An instance of this class can be made module level, but once the items are set then they can be neither modified nor replaced by new values.

In a class module:

'Class ROA (Read Only Array) Private A As Variant Private initialized As Boolean Public Property Get Item(i As Long) As Variant Item = A(i) End Property Public Property Let Items(data As Variant) If Not initialized Then A = data initialized = True Else Debug.Print "Illegal attempt to modify data" End If End Property

Testing code (in a standard code module):

Dim A As ROA Sub test1() Set A = New ROA A.Items = Array(3, 1, 4) 'set items once End Sub Sub test2() test1 Debug.Print A.Item(0) 'prints 3 A.Items = Array(5, 6, 7) 'no effect on A Debug.Print A.Item(0) 'still prints 3 End Sub

When test2 is run the output is:

 3 Illegal attempt to modify data 3

You could, of course, create a new instance of the class and assign it to A. The object is read-only, the variable itself isn't. This should be more than enough to prevent accidental modification.

The above code is mostly proof of concept. You could improve it so that e.g. it throws an error if you pass it data which isn't an array. You could also throw an error rather than do nothing when an attempt is made to change the data rather than simply logging it. Also, you might want to experiment with this clever way to make Item() a default method so that you could just use A(0) rather than A.Item(0).

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

3 Comments

You could set the array in Class_Initialize and only define Get and not the Let ?
@RobinMackenzie That is definitely a good idea. Unfortunately, good ideas and VBA don't always go together. Somewhat surprisingly, VBA doesn't allow parameters to Class_Initialize (though there are work-arounds: stackoverflow.com/q/18065523/4996248 ).
I've found another way to do what I want, which I've posted. Have a look and see what you think :) I can see real merit in your method, though, and it might a) be the better way to do what I want, and b) solve something else I've been playing with. Thanks for taking the time to help me
0

After a little bit of tinkering I think I've found a way to do what I want. I'm not sure if it's the best method, though...

Option Explicit Function testColl(ByVal uf As Object, ByVal createColl As Boolean) As Object Static Coll As New Collection Dim ctrl As Object If createColl = True Then For Each ctrl In uf.Controls Coll.Add ctrl Next ctrl End If Set testColl = Coll End Function

And for the buttons to test it.

Option Explicit Private Sub CommandButton1_Click() Dim test1Coll As Collection Set test1Coll = testColl(Me, True) MsgBox test1Coll(1).Name End Sub Private Sub CommandButton2_Click() Dim test2Coll As Collection Set test2Coll = testColl(Me, False) MsgBox test2Coll(2).Name End Sub

As far as I can tell the collection is only being created when createColl is true, but once it's created it stays in memory like it has module level scope.

Improve this answer

2 Comments

This is a nice solution. Static variables are a natural choice to maintain persistent data in a controlled way.
I'm embarrassed to say that I only just discovered static variables :blush: Instead of using a function I can pass the collection into a sub byref. It seems a little neater than using the function (I don't have to set a collection equal to the function that way), is there any reason I should avoid doing it?
0

i will tell you how i usually do this. First i use a dictionary instead of a collection, more flexible. The dictionary is public at userform level scope, so it can be accessed from everywhere inside the workbook. If the controls need coding for events i would throw in a class.

Since i do not like calling forms by their name, i have a function that loops in all userforms loaded and gives me its index in the vba.userforms collection.

To conclude, my dictionary would be called like this : vba.userforms(i).DictCtl(controlName)

You can use the if dict.exists() method to check if a control is already in it, without the need of looping through the whole existing dictionary.

Improve this answer

1 Comment

When you say that the dictionary is public at userform level scope, does that mean that I can declare it in the userform and use it in a module without passing it? Putting that aside, to do what I want, wouldn't I have to make the dictionary static to limit it's scope? Also, if I can do what I need to with a collection, what would I gain by using a dictionary?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.