Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208

    Control Arrays (sort of)

    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no need for old style arrays (archaic).

    tho there already are some:
    for each ctl in controls

  3. #3
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by ranman256 View Post
    no need for old style arrays (archaic).

    tho there already are some:
    for each ctl in controls
    No, it's not archaic - I have no idea where you got that bizarre notion. It is a construct in routine, daily use everywhere. I don't even know of a programming language any more that doesn't have arrays.

    And your counter-example is only valid if you want to go through every control on the form, and do the same thing to every one. I have trouble imaging such a scenario.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi
    Thank you for your detailed explanation and code examples. I agree with you that its a perfectly valid approach.

    You could simplify your code still further to handle a group of controls together using the Tag property.
    See Set Controls - Mendip Data Systems for an example app shoring how this works.

    Example code with four groups of controls each tagged A,B C or D

    Code:
       'reset all controls   
       EnableControls True, "A", "B", "C", "D"
       ShowControls True, "A", "B", "C", "D"
       LockControls False, "A", "B", "C", "D"
    If you wanted to do so, you could combine that code with your control state check though its something that's never worried me in practice
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Thank you, that is an interesting example. However, I prefer my own approach. Mine shows in the code which controls will be affected. Of course, using the tag property makes adding a control to the mix simpler. Like everything, each approach is some sort of compromise.

    But I would still add the state check, if I were to go with this. As I wrote, the speed difference is enormous, and it eliminates a great deal of annoying screen flicker.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Impressive but I'm sure I'll never have a need for it - at least not from here on in.
    Did you ever turn off screen updating in your old method to see how much time it saved?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's fine. Use whichever approach you prefer.

    However, I have used the tag approach widely in numerous apps over many years.
    I have rarely noticed screen flicker being an issue. If it is a problem e.g. with overlapping controls, I just turn off screen updating temporarily
    Its particularly useful in forms with a very large number of controls. It is both quick to write the code and fast in operation.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by Micron View Post
    Impressive but I'm sure I'll never have a need for it - at least not from here on in.
    Did you ever turn off screen updating in your old method to see how much time it saved?
    I did just now, as well as several other tests. Your comment made me curious.

    Turning off screen updating does help, sometimes. Several other things help, to varying degrees:
    Having the form open in normal mode, rather than design mode helps.
    Having the properties window closed (in design mode) instead of open helps.
    Having the list of displayed properties short rather than long helps (the Data tab rather than the Format tab).

    There may be other things that help as well. If someone wants to suggest some and they're not too complicated, I'll try them out and post what I find.

    However, nothing helps nearly as much as the test for the value already being set. I bumped the count up to 100,000, to give myself a better baseline. The best run time I could come up with for alternate methods was 15 seconds for 100,000 iterations - not bad, but still considerably less improvement than the 4 seconds for 100,000 iterations when the property is tested first, rather than just blindly set. Apparently, Access will simply not be talked out of doing a fairly large amount of unnecessary internal gymnastics. Interestingly, I several times had LONGER run times by turning off screen updating, than by leaving it on. Not in all scenarios and not by much - roughly 10% - 25% more, but it did happen, reliably and repeatably.

    The only sensible way appears to be to test the property manually, and avoid setting the condition unnecessarily. Since I have such tests reliably stashed in each of my Subs, so I can't forget them, and I can list any number of controls in one line of code that calls the Sub, I think I will stick with what I have. For what I am doing now, it is my best option.

  9. #9
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by isladogs View Post
    Its particularly useful in forms with a very large number of controls. It is both quick to write the code and fast in operation.
    This comment interests me. Since the code iterates over ALL controls on a form, why do you write that it is particularly useful because it is fast in operation with a very large number of controls? I would expect that as the number of controls grows, such an approach would be worse.

    Maybe you simply meant that is is so fast, the number of controls is irrelevant, and so the ease of use is paramount? I had to run my tests up into the thousands to even get a noticeable lag, far more than can ever be on a form, so maybe chasing execution speed here is mostly an academic exercise.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry if I wasn't clear

    If there are only a few controls in a form that need to be made visible/hidden, enabled/disabled, locked/unlocked... then it is quick and easy to write code to do that for each control individually
    However as many of my form are multi-purpose, they may have several hundred controls.
    One multipurpose form has almost 700 controls.
    The theoretical limit is 754 (over the lifetime of a form) but the actual limit is around 1000 depending on the Access version

    Writing code to manage each of those individually would be very time consuming especially as the code might need to be repeated in several places.
    Also each new control would need to be added to the code in each and every place where it needs to be enabled/hidden etc

    Grouping the controls and using the tag property makes doing this trivial.
    Any changes occur instantaneously (even without your control state check) ...and almost always without flicker

    NOTE: Things that can cause flicker include overlapping controls & unassociated labels, mouse move events and timer events
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    That must be some form, to have so many controls. I see why you would not want to address them individually. But you must have many tests, and many different sets of conditions, no? What happens when you want to set properties of only some of them, according to what you are doing at the time? Would you have a separate letter in the tag for each possible condition that might change a control's property?

    If you have a condition that requires controls A, B and C to be enabled, and elsewhere, you require controls D, E and F, and still elsewhere, you need A, C and E, then A, B, E and F - how do you keep track of which ones you want to manipulate at any given time? The number of possible combinations is huge, and constructing strings in the Tag to cover every possibility seems daunting.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    My multipurpose form frmSelectOption is an extreme example but it works well

    I think you may have misunderstood.
    The individual controls aren't called A,B, C or whatever.
    There may be 10 controls all with a tag value X. These are controls that I always want to change in the same way so they form one group.
    Another 7 controls may have a tag value Y and so are another group

    If I want to make all 10 controls with tag=X hidden at the same time I just write ShowControls False, "X"
    If I want all those 17 controls to be disabled at once, its EnableControls False, "X", "Y"

    I only give the same tag value to controls that ALWAYS are treated the same way.
    But I can manage several 'tag groups' with one line of code e.g. LockControls True, "A","C", "D", "Q","Z"

    ...etc, etc...
    NOTE: Instead of a single letter, the tag value can be more descriptive if preferred
    If you haven't looked at my example app yet, it would be useful to do so
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've just read post #8 which I missed earlier
    So you are saying that the code duration reduced from 15s to 4s for 100,000 iterations by using your status checks.
    Whilst that may seem impressive, that means the time for a single run reduced from about 0.00015 s to 0.00004s.
    Neither of those time delays would be noticeable in practice. Is it really worth the effort?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    pdanes is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Sep 2019
    Posts
    208
    Quote Originally Posted by isladogs View Post
    I've just read post #8 which I missed earlier
    So you are saying that the code duration reduced from 15s to 4s for 100,000 iterations by using your status checks.
    Whilst that may seem impressive, that means the time for a single run reduced from about 0.00015 s to 0.00004s.
    Neither of those time delays would be noticeable in practice. Is it really worth the effort?
    I think so, since it also eliminates flicker. And it's hardly a major effort - a single IF clause before each assignment, which is inside a Sub, so it's zero extra coding effort now. Also, I'm not absolutely certain that I constructed a realistic test case. In practice, I've occasionally noticed slight hesitations in forms that had no state tests, and that has vanished, completely, since I added the state tests. But it's an entirely subjective feeling, that I cannot document, so I hesitate to make much of it. It's simply an overall feeling, that the forms respond more smoothly, and that does not seem would be noticeable if the test case times accurately reflected reality.

    As for your app, I did download and look at it, but I have not dissected it all that thoroughly yet.

    Code:
    The individual controls aren't called A,B, C or whatever.
    Of course they are. Obviously not A, B and C - that was just for illustration. But they are called something, and my point was what do you do about the many possible combinations that can arise. And my question is still valid, I think. Maybe I didn't express it properly.

    You have some set of control properties that you want set under certain conditions, so you give all those controls same Tag value. Then you have another set with a different tag. Now along comes a new control and a new set of conditions, that will sometimes have your control acting in the same way as the first group, and sometimes as the second group. Now what? You can't add it to either group. You must come up with a new letter, and all places where you previously manipulated either group, you must add or not add that letter to the mix of parameters sent, no? And you limit your parameters to six (I think, I don't have it in front of me right now), by naming them individually. If you suddenly have more letters, you have to modify all of your routines. Using an array lets you pass unlimited amounts, now and forever.

    And do you not find it confusing and hard to remember using only a single letter for an indicator? How do you remember, down the road, what group "X" has in common to be treated identically, and how do you remember when coding, that you want to do something with group "X" rather then group "K"?
    Last edited by pdanes; 03-30-2021 at 11:40 PM.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've already answered some of the comments in your last post

    You have some set of control properties that you want set under certain conditions, so you give all those controls same Tag value. Then you have another set with a different tag. Now along comes a new control and a new set of conditions, that will sometimes have your control acting in the same way as the first group, and sometimes as the second group. Now what? You can't add it to either group. You must come up with a new letter, and all places where you previously manipulated either group, you must add or not add that letter to the mix of parameters sent, no?
    I've been using this system with numerous commercial/client/example apps for over 15 years. My forms are carefully planned so its unusual for me to need to deal with a new set of conditions that weren't foreseen at the start

    And do you not find it confusing and hard to remember using only a single letter for an indicator? How do you remember, down the road, what group "X" has in common to be treated identically, and how do you remember when coding, that you want to do something with group "X" rather then group "K"?
    No - because I use certain tag values in the same way in each database where tis idea is used e.g. tag = A - always visible, X = always hidden, M = map controls etc. But as already stated, a descriptive phrase can be used instead if preferred e.g. MAP.

    you limit your parameters to six .... Using an array lets you pass unlimited amounts, now and forever.
    In the past I experimented with using parameter arrays rather than a specified number of arguments. Client feedback was that the arguments approach was preferred so I reverted to that.
    The number of arguments can easily be increased from six in the unlikely event that's not sufficient for a particular procedure OR you can just add a second line of e.g. ShowControls True tag values

    It works for me and many other people. You don't seem convinced. That's fine. Don't use it
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sort query a by a calculated control
    By gg80 in forum Queries
    Replies: 4
    Last Post: 10-01-2015, 05:40 PM
  2. sql query arrays
    By drewdrew in forum Programming
    Replies: 3
    Last Post: 07-03-2014, 03:35 AM
  3. Replies: 1
    Last Post: 02-27-2012, 06:22 PM
  4. adding a sort control button
    By darklite in forum Forms
    Replies: 5
    Last Post: 01-24-2011, 03:32 PM
  5. a-z sort for control button
    By darklite in forum Access
    Replies: 7
    Last Post: 05-28-2010, 04:49 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums