Guild of Project Controls: Compendium | Roles | Assessment | Certifications | Membership

Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

Looping backwards through a 'For Each' collection

6 replies [Last post]
John Borton
User offline. Last seen 4 years 50 weeks ago. Offline
Joined: 12 Sep 2018
Posts: 8
Groups: None

 

Here is the usual VBA code for looping through all tasks in a project:

  For Each t In ActiveProject.Task

      {code for desired action}

  Next t

This loops through from the top down (as far as I can tell).

Anyone have an ideas as to how I can reverse this and go from the bottom up?

Thanks
JB

 

Replies

Susanna Kate
User offline. Last seen 4 years 3 weeks ago. Offline
Joined: 20 Aug 2019
Posts: 14
Groups: None

Thanks Tom for the code!

John Borton
User offline. Last seen 4 years 50 weeks ago. Offline
Joined: 12 Sep 2018
Posts: 8
Groups: None

// "In "general use," one can't reliably presume a correlation between the ID sequence and the logical sequence." //

Understood.  In this case it's a factory production schedule that by fiat gets used in a particular format over and over so I have pretty tight control on how it's sorted.

Thanks again.

JB

Tom Boyle
User offline. Last seen 4 weeks 3 days ago. Offline
Joined: 28 Nov 2006
Posts: 304
Groups: None

John,

Glad you got it sorted. 

In "general use," one can't reliably presume a correlation between the ID sequence and the logical sequence.  Still, as you've explained, this certainly seems to offer a bit of streamlining for that particular (ZFF) issue.

Rgds, t 

John Borton
User offline. Last seen 4 years 50 weeks ago. Offline
Joined: 12 Sep 2018
Posts: 8
Groups: None

That's it Tom ... THANKS.

The task.count property was the secret and this link didn't list it for whatever reason:
https://docs.microsoft.com/en-us/office/vba/api/project.task

A
s to its usage, I actually just successfully used it to modify your ZFFImpose function.  I've been creating schedules with tasks such that I needed to loop through multiple times to get all the float out of all the faux-ZFF relationships (programatically constraining one task simply moved the float up to the previous task requiring another run through). 

My previous schedule had a known number of these relationship layers (3) so I just looped through your code three times.  Now I have a schedule that the users might add an unknown number and I needed a more elegant solution than my previous clutz.

Going through backwards allows your code to now remove the float as far up the chain as needed in one go-round.

I'm a programming hack, so you might have come up with a cleaner solution, but this works slick as a whistle as written.

THANKS (for both the original code AND the mod)
JB
 

Tom Boyle
User offline. Last seen 4 weeks 3 days ago. Offline
Joined: 28 Nov 2006
Posts: 304
Groups: None

Hi John,  this will do what you ask, though I can think of no good general-use case.  Good luck, tom

Sub BackTasks()

    Dim t As Task

    Dim i As Long

    For i = ActiveProject.Tasks.Count To 1 Step -1

        Set t = ActiveProject.Tasks(i)

        If Not t Is Nothing Then

            'Your Code Here

        End If

    Next i

End Sub

 
Tom Boyle
User offline. Last seen 4 weeks 3 days ago. Offline
Joined: 28 Nov 2006
Posts: 304
Groups: None

[Delete duplicate]