Project Management

Using Project 98/2000 VBA can make you a better support tech

If your clients use Microsoft Project 98/2000, understanding how to use Visual Basic for Applications will help save them and you valuable time. Learn about the power of loops and the always useful If...Then statement.


In my earlier article, “Use VBA to support MS Project 98/2000,” we covered the basics of Visual Basic for Applications (VBA). This second article digs a bit deeper into VBA and explores how it can be used to support Microsoft Project. The two programming structures used most often when writing macros in Project are Loops and Conditional Statements. This article examines these two structures and demonstrates how useful they can be when troubleshooting MS Project.

For…Next loops
In the last article, we examined objects and their relationship to Microsoft Project. Objects are grouped into collections. For example, the Tasks collection is a grouping of all the Task objects in a given project. Notice that "Tasks” is a collection and “Task” is an object. A Loop is a programming tool that allows you to move through a collection of objects and act upon each of them with your code. The most commonly used loop is the For…Next. It uses a variable to incrementally move through a set of numbers. When used with objects, For…Next will move through the members of a collection.

For example, in the code that follows, we use a variable called "T” to represent an individual task in a collection. We tell Project what we want to do with this with a Dim Statement. This statement creates and defines a variable within the VBA environment.
 
Sub TestingOurMacro()
Dim T as Task
For each T in ActiveProject.Tasks
 ‘Some Code
Next T
End Sub
 

This code will move through the collection of tasks in the currently active project, stop at each one, and allow your code to manipulate the task. You could replace “Some Code” with:
 
T.Start = “9/26/00”
T.Text1 = “Our Macro Was Here!”
 

Now every task in your project has a start date of 9/26/00 and “Our Macro Was Here!” in the Text 1 field.

This illustrates how a loop can help you gather data or set values for every task in a project. However, this loop acts on all tasks, and, in most cases, you will only want your code to act on specific tasks. This is where Conditional Statements come into play. I will cover the most common and simple of the conditional statements here.

If…Then statement
The If…Then statement allows you to run code based on the results of an evaluation. For example, you can run your code only on tasks that contain certain text in a Text field. We could add a statement like the one that follows to our previous code.
 
Sub TestingOurMacro()
Dim T as Task
For each T in ActiveProject.Tasks
 If T.Text2 = “Marked” Then
  T.Start = “9/26/00”
  T.Text1 = “Our Macro Was Here!”
 End if
Next T
End Sub
 

Notice we added the line “If T.Text2 = “Marked” Then,” then three lines down we added “End if.” When this macro is run, Project will move through the Tasks collection and check each task’s Text2 field. If it equals the word “Marked,” the macro will run the two lines of code between the “If...Then” and the “End if” lines. In this case, it will change the start of the task to “9/26/00” and the Text1 field to our value. For a real macro, we could put several lines of code within this conditional statement.

More If…Then examples
This macro checks to see if a task occurs between two specified dates and if it does, changes the Task Priority field to a new value.
 
Sub ChangePriority()
Dim T As Task
Dim EarlyDate As Date
Dim LateDate As Date
 
EarlyDate = "10/2/00"
LateDate = "10/10/00"
 
For Each T In ActiveProject.Tasks
 If T.Start > EarlyDate And T.Start < LateDate Then
  T.Priority = 900
 End If
Next T
 
End Sub
 

This next macro checks the Task Type of each task, and if it equals a certain value, changes it to a different value. This is useful for very large projects that use several different task types and effort-driven settings.
 
Sub ChangeText()
Dim T As Task
 
For Each T In ActiveProject.Tasks
 If T.Type = pjFixedDuration And T.EffortDriven = True Then
  T.Type = pjFixedUnits
  T.EffortDriven = True
 End If
Next T
 
End Sub
 

Loops within loops
You can also have loops within loops. The Task object contains several other collections within it. One of these "sub-collections” is the Assignments collection. It represents all of the Assignment objects for a task. Once you have a loop that takes you through all the tasks, you can then add another loop that will take you through all the assignments for each task in the first loop.

As an example, here’s a macro that will take a value from a Task field and place it into a field for each of the assignments on that task. This can be helpful when trying to filter assignments based on the properties of the task to which they belong.
 
Sub TaskDataToAssignmentData()
Dim T As Task
Dim A As Assignment
 
For Each T In ActiveProject.Tasks
 For Each A In T.Assignments
  A.Text1 = T.Text1
 Next A
Next T
End Sub
 

Until next time…
Macros are very helpful for this kind of maintenance. Users often have incorrectly entered data that needs to be fixed. Their first reaction frequently is to type it all in again by hand. With a little background in VBA, however, you can assist your users and save them a great deal of time. The key to expanding your understanding of VBA is to get in there and play with it. Use the Macro Recorder to see how things are done (we covered this in the last article), and then try using loops to repeat your macro for several tasks. Try putting conditionals into your macro to control when the code executes. Learning a little VBA can make your life a bit easier.

Should support techs use VBA?
Now that you've read the article, let us know what you think. Should support techs use VBA to troubleshoot MS Project? Post a comment or send us an e-mail.

 

Editor's Picks

Free Newsletters, In your Inbox