« VBA to VSTO Tutorial Part Two - Adding a Command Bar and Buttons | Main | Long Now Orrery »

VBA and Visual Basic For ... to ... statements

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
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.

  • Setting Microsoft Project Level Custom fields using VBA
  • Iterating through Microsoft Project Subprojects
  • Microsoft Project Undo Levels and Macros
  • VBA to VSTO Tutorial Part Two - Adding a Command Bar and Buttons
  • Making the move from VBA to VSTO in Microsoft Project
  • Office VBA for Mac After 2008
  • Analyze Microsoft Project Resource Usage Data In Excel
  • VBA Writing to a text file (MS Project, Excel)
  • MS Project VBA - Trim Function
  • Using a ComboBox in a Microsoft Project Userform

  • About

    The previous article is VBA to VSTO Tutorial Part Two - Adding a Command Bar and Buttons.

    The next article is Long Now Orrery.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34