Experienced VB users sometimes complain about VBA not having control arrays. I agree, that is one of the missing functionalities that would be nice to have in VBA, but I have come up with something that works pretty well for me. It's not an equivalent, but it lets me handle the things I need to do, giving much cleaner code and insanely faster execution times.
I often need to set properties of controls on a form, such as enabled, locked, visible, and so on. Initially, I would just set them to whatever I need and consider it good. But after a while, I started to notice annoying screen flickers when setting such properties. A bit of experimenting determined that VBA does not have enough sense to recognize when a property is already set. I would expect that the innards would, as the very first step, check to see if the property is already set to the requested value, and if so, bail immediately. It does not – it spends quite a bit of time setting the property again, and often does at least some modicum of screen repainting when it does so, causing screen flicker of the opened form.
I then started putting tests before the set commands, which made the apps smoother and faster, but were a PIA to code and made the code much messier. I would often have large blocks of things like:
Code:
With Form_ProvestSQL
If .cmdFiltrovat.enabled <> True Then .cmdFiltrovat.enabled = True
If .cmdProvest.enabled <> True Then .cmdProvest.enabled = True
If .cmdVybrat.enabled <> True Then .cmdVybrat.enabled = True
If .cmdZavrit.enabled <> True Then .cmdZavrit.enabled = True
End With
It's effective, but untidy, and a nuisance to remember the tests all the time. Recently, I hit upon a better method – calling a Sub with the controls passed as parameters. If you define the incoming set of controls as a Variant, you can pass an array of controls, of any desired size, in one line. Instead of the zoo above, which can sometimes run to dozens of controls on a busy form, I use this:
Code:
With Form_ProvestSQL
gbl_setCtlsEnabled arr:=Array(.cmdFiltrovat, .cmdProvest, .cmdVybrat, .cmdZavrit), stav:=True
End With
And the routine that is called is just this little gem:
Code:
Public Sub gbl_setCtlsEnabled(ByRef arr As Variant, ByVal stav As Boolean)
Dim i&
For i = LBound(arr) To UBound(arr)
With arr(i)
If .enabled <> stav Then .enabled = stav
End With
Next i
End Sub
The test for 'already set' is right in the routine, so I don't have to keep remembering to put it in, the number of controls that can be passed via the array is essentially unlimited, and I can set as many controls as I want with one line of code. If there is a performance penalty for constructing this array at runtime, I was unable to detect it. I tested the above, four controls on a form, in a loop repeated 1000 times. In the original version, simply setting the enabled property, it took 42 seconds. In the improved version, with the tests for 'already enabled' in each line, it took 0 seconds. I had to bump the iteration count up to 10,000 to get even one second of elapsed time. In the one-line version, using my function to deal with an array of passed control references, the timing result was identical – 0 for 1000, 1 second for 10,000.
I wasn't sure whether the performance hit was really so small, or maybe the VBA optimizer is smart enough to recognize the constant nature of the array and sets it up at compile time. So I also tried setting the control names at runtime, to force VBA to assemble the array at runtime. The results were the same – 1 second for 10,000 iterations.
Code:
With Form_ProvestSQL
gbl_setCtlsEnabled arr:=Array(.Controls("cmdFiltrovat"), .Controls("cmdProvest"), .Controls("cmdVybrat"), .Controls("cmdZavrit")), stav:=True
End With
I still wasn't sure – compilers are mighty smart these days, so I went another step:
Code:
z1 = "cmdFiltrovat"
z2 = "cmdProvest"
z3 = "cmdVybrat"
z4 = "cmdZavrit"
For i = 1 To 10000
With Form_ProvestSQL
gbl_setCtlsEnabled arr:=Array(.Controls(z1), .Controls(z2), .Controls(z3), .Controls(z4)), stav:=True
End With
Next i
Still elapsed time of 1 second for 10,000 rounds. But just in case even this was recognizable by the compiler, I did one last test, putting in just <Enter> to the InputBox's prompt:
Code:
z = InputBox("Junk")
z1 = "cmdFiltrovat"
z2 = "cmdProvest"
z3 = "cmdVybrat"
z4 = "cmdZavrit"
For i = 1 To 10000
With Form_ProvestSQL
gbl_setCtlsEnabled arr:=Array(.Controls(z1 & z), .Controls(z & z2), .Controls(z3 & z), .Controls(z & z4)), stav:=True
End With
Next i
Even this, with the string concatenations inside the loop, ran with the same results – 10,000 iterations, 1 second. At that point I gave up – I'm convinced that if there is any kind of performance hit for using this technique, it is so small as to not be worth pursuing further, or at least not in any user interfaces. If it was inside a massive compute-bound loop, I might dig deeper, but for visual indications to a human, this is more than adequate.
You can use this method for setting multiple properties. I have around a dozen such Subs, for setting various combinations of properties, like Visible and Locked, BackColor and ForeColor, even properties of labels associated with text controls, via the .Controls(0) property of the passed control. In all cases, I include the test for 'already that way', even for things like caption text or going invisible. I also have 'single' versions of the subs, for cases when I am only setting one control. It's just as fast and less tedious typing to call, for just one control. The multiple version can also be called with only one control in the array, but even the single control must still must be specified as an array. This is cleaner for single controls, and simple to convert if I later decide to add more controls - the syntax is the same:
Code:
gbl_setCtlEnabled ctl:=cmdNahoru, stav:=lstVyberu.ListIndex > 0
calls:
Code:
Public Sub gbl_setCtlEnabled(ByRef ctl As control, ByVal stav As Boolean)
With ctl
If .enabled <> stav Then .enabled = stav
End With
End Sub
Each such Sub has a very descriptive name, like gbl_setCtlsEnabledAndLocked, describing exactly what it does. Some don't have the Boolean flag variable, because they only make a change one way, like gbl_setNulls. That sets every passed control to Null, and there is obviously no 'opposite' value, so there is no need for a flag variable. Some include additional Boolean or value variables, so the mechanics of deciding what to do are determined in the Sub, based on information passed in parameters. The calling code is thereby much shorter, cleaner and easier to follow. Debugging is also faster and easier – instead of stepping through numerous property set statements, you can just Ctrl/Shift F8 back out of the property set routine, skipping a bunch of visual fluff that does not need to be debugged. There are endless possibilities, but the core of the gimmick is the array of controls passed to a single Variant parameter in the Sub.