• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Do ... Loop Until False repeats!

B

BobBridges

Active Member
Here's an odd one that I fear might be a bug in in the VBE itself. I have a collection of objects, and I have a loop that looks at the collection and selects some of them to put in another collection, like this:
Code: 
    For Each omgr In ADMgrs
      vk = omgr.Manager.ID
      Do 'block
        If Not Exists(osus, vk, osu) Then Exit Do 'if he's not in the osus collection, I'll select him
        If Not osu.bAgent Then GoTo IterateMgr 'if he's in osus I still don't want him if he's not an agent
        If Not osu.bASO Then GoTo IterateMgr 'if he's in osus and an agent I still don't want him if he's not ASO
        Loop Until False
      of.Add omgr, vk
IterateMgr:
      Next omgr
This past spring it worked fine. Now it works until the 243rd item of the ADMgrs collection; after adding that one to the new collection, the Loop-Until-False statement loops back around and starts the Do block again!, resulting in an infinite loop.

I added an extra test after the spring run, but a) I don't remember which one, and anyway b) I can't imagine how anything could cause that particular behavior. Any ideas, anyone...please?
 
Wait a minute, maybe it's easy. Shouldn't that Loop statement say "Loop Until True" or "Loop While False", either one? In the spring when I did this I had one less test, so I didn't use a Do block, I just wrote "If Exists... Then If Not ASO Then GoTo Iterate". Maybe the 243rd item in the collection is the very first one that passed all the tests. Sorry about that, guys...
 
Hey Bob !​
​
As a reminder my english is rough even when I do not want to be, so do not be angry 'cause of my next words as this is not my will …​
​
So you use a classic Do … Loop until a condition is true.​
So your Until False has no sense 'cause the constant False can't never be True obviously ‼​
So why it should be a NES ! Never Ending Story ‼​
​
If you replace False with True so at the end of the first iteration the loop ends so like without this Do Loop so useless ‼​
​
You can better use for example a boolean variable - B for example - which is equal by default to False​
so continue to loop Until B and somewhere within this Do Loop block set this variable to True in order to exit this loop …​
​
Another way is a Do Loop without any condition and just control the exit with the statement Exit Do (like you did) …​
​
Code: 
Sub Demo1()
        L& = 1
    Do
        Debug.Print L
        L = L * 2
    Loop Until L > 99
End Sub

Sub Demo2()
    Dim L&, B As Boolean
        L = 1
    Do
        Debug.Print L
        L = L * 2
        B = L > 99
    Loop Until B
End Sub

Sub Demo3()
        L& = 1
    Do
        Debug.Print L
        If L > 99 Then Exit Do
        L = L * 2
    Loop
End Sub
​
Since VBA birth as an OOP coding language the Goto 'label" has no sense except to be compatible with the old BASIC code (1984)​
so can be easily avoided … Then your 3 If codelines can be inverted and combined as one in order to avoid the ugly Goto 'label' …​
 
Last edited:
Ton anglais est beaucoup meilleur que mon français, alors je ne m'en plains pas. Yes, quite right, I should have looped either "Until True" or "While False"; I just got confused when I first wrote it, and didn't test it right away, so three months later I didn't understand why it wasn't working. My bad.

An If statement might seem the more natural choice for this bit of code. But VBA doesn't provide a way to Exit from an If group, and when I use Do instead, I can "Exit Do". So I routinely use a single-iteration Do block, and close it with either "Loop Until True" or "Loop While False" (but always marking it with the "block" comment at the top to remind me what I'm doing). I tend to prefer Select Case, but for some reason the Do seemed better to me this time.
 
As a reminder the best way to exit an If block is to not enter within … Or to enclose another If …​
​
 
Sure, but look at what would have been required for an If block in this case:
Code: 
I have a Manager object
Get the manager ID
Does the manager ID appear in collection-A?  If so, get the Status object from collection-A.
Condition #1: If False (that is, if the manager ID is not in collection A), I can use this object; no need to check other conditions
Condition #2: If True, does the Status object show that the manager is an agent?  If not, skip this Manager
Condition #3: If true, does the Status object show status ASO?  If not, skip this Manager
If it passed all the previous tests, I can use this object
I decided this would get complicated if I tried it to do it with If statements. A Do block with Exit statements to use the Manager object, and GoTo Iterate statements to skip it, is much simpler.

Select Case would have worked too:
Code: 
For Each omgr In ADMgrs
  vk = omgr.Manager.ID
  Select Case True
    Case Not Exists(osus, vk, osu): bUse = True 'if he's not in the osus collection, I'll select him
    Case Not osu.bAgent: bUse = False 'if he's in osus I still don't want him if he's not an agent
    Case Not osu.bASO: bUse = False
    Case Else: bUse = True
    End Select
  If bUse Then of.Add omgr, vk
  Next omgr
 
Last edited:
BobBridges said:
Get the manager ID
Does the manager ID appear in collection-A? If so, get the Status object from collection-A.
Condition #1: If False (that is, if the manager ID is not in collection A), I can use this object; no need to check other conditions
Condition #2: If True, does the Status object show that the manager is an agent? If not, skip this Manager
Condition #3: If true, does the Status object show status ASO? If not, skip this Manager
If it passed all the previous tests, I can use this object
Click to expand...
So : If False Or (True And (Status = Agent Or Status = ASO)) Then use Object …​
​
 
I guess where you're saying "False" and "True" you mean "Not Exists" and "Exists". But that requires me to do the Exists search twice. Or you could do it this way:
Code: 
bAOS = Exists(osus, vk, osu)
If Not bAOS Or (bAOS And osu.bAgent And osu.bAOS) Then 'use object
I think I like the Case construction best, though; seems the sort of thing that would be clearer, both to me six months later and to some other poor schlob who has to maintain my code.

That Exists function, by the way, looks up a key in a collection and returns True or False. It can deal with numeric or string keys. The optional third argument is the result if the key was found in the collection; it can be a scalar variable or an object. There's an optional 4th argument that I almost never use, that is the result returned if the key is not found in the collection. Collections don't have the Exists method, as dictionaries do, so this is very handy.
 
I use both and as Dictionary is under Windows only so on MAC no choice with only Collection well working as a Dictionary.​
As a Collection can do all the Dictionary can, as a Dictionary can't do all a Collection can​
and as on huge data the Collection becomes faster than a Dictionary …​
The Exists Dictionary can be easily done with a Collection just managing the error.​
​
On some project I use a Class module to use a Collection like a Dictionary :​

A Dictionary for Mac & Windows …

Hello, this is a dictionary like the Windows ActiveX Scripting.Dictionary working whatever on Mac or under Windows with more features just with VBA basics ! First download the below text file attachment. Once on your local hard drive, rename it just removing the .txt extension in order it's...
chandoo.org
 
Exactly, my Exists function uses On Error to work with both objects and strings, string and numeric keys, and a few special cases that apparently don't work the same way as collections—if I ask for a worksheet that doesn't exist, for example, the error code returned is different from that of a normal collection.

Have you ever worked out a generic sort routine for collections? I have a function that sorts a collection of objects, but the object has to have a property named Key. I haven't figured out yet how to do any better than that.
 
For some purpose I directly sort the Collection with inserting a new Key, ok if not too many keys or may become slow …​
If slow and if the final data go to a worksheet then better to just add keys and use the classic VBA Range.Sort method.​
​
 
Right, definitely better to sort within an Excel worksheet—must faster, and less work too. But it can't always be done. Sometimes I need them to be in the right order before I place them. And I have one report I generate that has user IDs across the top, each user taking up two columns. I can sort columns, of course, but not when they take up two cols apiece.

Sometimes if a sort is complicated, I create a sort column and construct a key in it, then sort on that column, then delete the column afterward. But I'd still like to be able to sort a collection sometimes.

You mention "inserting a new key", and I'm not sure what you mean. I sometimes create a second collection that has all the same items as the original, but keyed on a different field. But neither collection is sorted on the key; I just just go to that key directly. What I want is for them to come out in a particular order when I do a For Each Whatever In Collection. Or are you saying collections have a method for that? I don't think so.
 
Yes when adding an item you can insert an item before or after an existing item like you can see in VBA help of Collection.Add method …​
​
 
No. I mean, "yes", I can add a new key. But that isn't sorting the collection, and it doesn't enable me to access the items of the collection in any particular order. Unless you know something I don't.

Like this: I start a collection, and I add items to it one by one, each with its own key. The first item uses key "NOW", the second "IS", the third "THE", and so forth, up to say 200 items. Now I want to list all the items in the collection in alphabetical order, sorted by the key. Aside from writing a sort routine, how would I do that?

I've written a general function that sorts collection items, but it depends on each item having a property named Key. I'm hoping I can do better than that.
 
According to my previous post the Collection directly collects data sorted by inserting rather than adding according to VBA help.​
With 200 items it may be slow. Under Windows there are some ActiveX which sort like ArrayList or SortedList …​
​
 
A sorted Collection VBA demonstration sorting column B to column E :​
Code: 
Sub Demo1()
        V = [B1].CurrentRegion
        [E1].CurrentRegion.Clear
    With New Collection
    For Each W In V
        For R& = 1 To .Count
             X = StrComp(.Item(R), W)
         If X >= 0 Then
            If X Then .Add W, , R
            Exit For
         End If
        Next
            If R > .Count Then .Add W
    Next
        For R = 1 To .Count:  V(R, 1) = .Item(R):  Next
        [E1].Resize(.Count) = V
    End With
End Sub
 

Attachments

  • Demo Sorted Collection .xlsb
    14.8 KB · Views: 0
Indenting properly to help me read this code:
Code: 
V = [B1].CurrentRegion
[E1].CurrentRegion.Clear
With New Collection
  For Each W In V
    For R& = 1 To .Count
      X = StrComp(.Item(R), W)
      If X >= 0 Then
        If X Then .Add W, , R
        Exit For
        End If
      Next
    If R > .Count Then .Add W
    Next
  For R = 1 To .Count:  V(R, 1) = .Item(R):  Next
  [E1].Resize(.Count) = V
  End With
What's this B1 and E1 in square brackets? Do square brackets have meaning in VBA, or are you just saying "any range called B1 and E1?

Anyway, I think I see what's going on here: You're reading values from a worksheet range, copying them in sorted order into another worksheet range, then loading the sorted order into a collection. Is that right?

If so, I guess I can see why you'd say it would take a while to sort 200 values using this method. But I'm more likely to need 400 or a thousand, for this particular application. And worksheets, as you already know, are slow. This can be made much faster by turning off ScreenUpdate and automatic calculation. But it'll go much faster doing a manual sort on the data once it's loaded into RAM. I was just hoping to save a little work, but I guess not.
 
Square brakets are the short syntax of VBA function Evaluate, it could be a named range but here not as just cell address …​
​
Wrong : allocating a variable array from a source range, inserting the variable values in a Collection in sorted order​
then allocating back the variable array with the Collection sorted values in order to allocate the result range at once​
so all is done in RAM the reason why desactivating Calculation & ScreenUpdating is useless here …​
​
 
Ah, arrays; yes, that would be much faster. And I see now, you didn't say "Set V =", but just "V =", so it would be an array rather than a worksheet range. But then you say "For Each W in V"; does that work with array elements? That's a new one on me.

Something new for me to do timing tests on, then.
 
You must log in or register to reply here.
Back
Top