Is there a way to set a variable once and use it in multiple places without giving it module level scope?
I have a loop that adds user controls to a collection. It's nothing exciting, but it got me thinking about how I do it.
Since the assembly is needed in many places, I just pushed it in the module and will name it when I need it. This means that the collection is only needed in memory, but it also means that I loop through every time I want to work with it.
I could provide a collection module level scope and create it the first time I needed it. This would allow me to run the loop once, but the collection would remain in memory.
From my research I figured out that 32bit excel has a 1.75gbs memory limit (although this can be extended as well as 64bit version). And this is vba excel single threaded. As such, it seems to me that I should prioritize CPU efficiency over memory efficiency unless I have a specific need otherwise. The program will not only be faster, but also potentially more energy efficient.
Here rub is a module-level provisioning of the collection that opens it up to unintended 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 it when it's only in memory when needed?
Thanks for reading, hopefully this is not a very stupid question.
source to share
I don't think there is any way to create a scope made up of functions that are smaller than a module (this is what you think you want).
If you are worried about accidental modification, you can create a class that implements a read-only array. An instance of this class can be set at the module level, but once items are set, they can neither be changed nor replaced with 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
Test code (in 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 executed test2
, the output is:
3
Illegal attempt to modify data
3
You could, of course, create a new instance of the class and assign it A
. The object is read-only, the variable itself is not. This should be more than enough to prevent accidental modification.
The above code is basically a proof of concept. You can improve it so that, for example, it will throw an error if you pass data to it that is not an array. You can also make a mistake rather than do anything when you try to change the data rather than just log it. Alternatively, you can experiment with this clever way to make Item()
the default method so you can just use A(0)
it instead A.Item(0)
.
source to share
After tinkering a bit, I think I found a way to do what I want. I'm not sure if this is 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 buttons to check.
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 created when createColl is true, but once it is created, it stays in memory as if it had a module level scope.
source to share
I'll tell you how I usually do it. I use a dictionary instead of a collection first, more flexible. The dictionary is publicly available at the user level, so it can be accessed from anywhere within the book. If the controls require coding of events I would choose in the class.
Since I don't like to call forms by their name, I have a function that loops in all loaded user forms and gives me its index in the vba.userforms collection.
Finally, my dictionary will be called like this: vba.userforms(i).DictCtl(controlName)
You can use the if dict.exists () method to check if there is a control in it, without having to iterate over the entire existing dictionary.
source to share