Project

Most of the Microsoft Project or Excel macros I write include looping through a collection of tasks or resources or assignments and use the a For Each ... Next loop, but that doesn't mean we should neglect the For ... to ...Next statement.

This statement uses a counter which you set to determine how many times it runs. The syntax is as follows:

For counter = start To end [Step step] [statements] [Exit For] [statements] Next [counter]

counter is the number of times that the loop will run. It is a required input to the statement. The most common terms used for counter are the lower case letters starting with i. When dealing with arrays or other sorts of multidimensional data it is common to end up with nested For...Next statements so sticking to a standard convention helps you keep your place. An example of nested statements is:

For i = 1 to 10
For j from 1 to 10
myArray(i,j) = i * j
Next j
Next i

The next part is the start parameter. This is often just an integer like 0 or 1, but you can pass any numeric vaue to it. End is similar, but it is more likely to be a variable something like ActiveProject.Tasks.Count or UBound(myArray()) work nicely.

The final piece of the puzzle is the step value. Step can be any integer both positive OR negative. I've used negative step values when deleting or inserting tasks as the task ID can change if you insert or delete a task, so working from the last task to the first is a good idea. Here is an example of deleting blank tasks working from the last task in the active project and working your way back to the top:

For i = ActiveProject.Tasks.Count To 1 Step -1
If ActiveProject.Tasks(i) Is Nothing Then
SelectRow Row:=i, rowrelative:=False
EditDelete
End If
Next i

The value of the counter i can even be set within the statement, but be aware that this can cause problems. It is certainly possible to create an endless loop this way. It can also be more difficult to debug. Do it only if you have to.