The TraceDependencies Macro

 < Day Day Up > 

The TraceDependencies Macro

VBA is most useful when it does something for you that is really difficult or time-consuming for you to do by hand. The TraceDependencies macro discussed in this section demonstrates some useful control structures and concepts.

When schedules get large, it can become difficult to trace the relationships between activities. You can either try to trace the lines in the Gantt chart or look at the Network view. Both these options can be challenging at times.

It would certainly be useful to be able to filter the display so that only the related tasks are shown. With the advent of overhead projectors and electronic collaboration allowing schedule review and analysis in real-time, it is also important to be able to do this quickly. The TraceDependencies macro is meant to solve the problem.

First, let's clearly define the problem. You want to find all the tasks related to a selected task. You would like to be able to look at all the predecessors and successors. In some cases, only one or another of these is necessary, so you want to give the user a choice. On occasion you might want to narrow your view even further and just see the tasks that are on the critical path .

Earlier in this chapter, you learned that sometimes the summary task provides necessary context for figuring out what a certain task is if there are many similarly named tasks. Other times, they just clutter things up. You would like the user to be able to choose. Because the display will shift, the user should be able to find the task that he or she had originally selected, even if it has moved on the screen.

The code for the TraceDependencies macro is shown in Listing 1:

Listing 1. TraceDependencies MACRO
 '   This macro filters the project to show the '   predecessors and/or successors of a '   selected task depending on the user input '   This macro works best if assigned to a button on the toolbar '   It uses Flag5 to store information - '   Please be sure that this field is NOT being used for other purposes '   Note:   It does not trace across external (Cross project) links. Option Explicit Dim strFanType As String Dim boolSum As Boolean Dim gboolboolCrit As Boolean Dim T, TT, Tselect As Task 'This is the master macro Sub Trace() 'If selection is more than one task or a blank line warn and then quit the macro If ActiveSelection.Tasks.Count <> 1 Or ActiveSelection.Tasks(1) Is Nothing Then MsgBox "You must have just one task selected for this macro to work"     Exit Sub End If 'Assign the variable for the selected task Set Tselect = ActiveSelection.Tasks(1) 'If the selected task is a summary task, warn and then quit macro If Tselect.Summary = True Then     MsgBox ("You have selected a summary task. " _     & "Select a task or milestone and try again")     Exit Sub End If 'This sets flag used later for tracing paths. strFanType = InputBox(("Please Enter Fan Type" & vbCr _             & vbCr _             & "P   (Predecessors)" & vbCr _             & "S   (Successors)" & vbCr _             & "A   (All)") & vbCr _             & "Leave Blank to quit here", "Fan-out Dependencies") 'Convert the input into correct case if necessary strFanType = UCase(Left(strFanType, 1)) 'Quit if no information is typed If strFanType = "" Then     Exit Sub End If 'Clear the flag used to show tasks ClearFlags 'Set the flag which determines if only critical tasks are shown gboolboolCrit = False If Tselect.boolCritical = True Then     If MsgBox("Do you want to display only boolCritical Tasks?", _     260, "Display boolCritical Tasks Only?") = vbYes Then         gboolboolCrit = True     End If End If 'Use the input about what the user wants to trace 'to determine what action to take Select Case strFanType     Case "P"         'Traces Only Predecessor Tasks         FanBackward Tselect, gboolboolCrit     Case "S"         ' Traces Only Successor Tasks         FanForward Tselect, gboolboolCrit     Case Else         ' Traces All Tasks - one pass for successors, then one for predecessors         FanForward Tselect, gboolboolCrit         FanBackward Tselect, gboolboolCrit End Select 'Run a subroutine to filter the activities FilterMe 'Make sure that the original task is still selected Find Field:="ID", Test:="equals", Value:=Tselect.ID ', 'Next:=True End Sub 'Set all tasks Flag5 to false Private Sub ClearFlags()     For Each T In ActiveProject.Tasks         If Not (T Is Nothing) Then             T.Flag5 = False         End If     Next T End Sub ' Walks through all successors to a task and marks their flag5 as true Sub FanForward(T As Task, boolCrit As Boolean) Dim TT As Task T.Flag5 = True     For Each TT In T.SuccessorTasks         If TT.Flag5 <> True Then             If Not boolCrit Then                 FanForward TT, boolCrit             End If             If boolCrit And TT.boolCritical Then                 FanForward TT, boolCrit             End If         End If     Next TT End Sub ' Walks through all predecessors to a task and marks their flag5 as true Sub FanBackward(T As Task, boolCrit As Boolean) Dim TT As Task T.Flag5 = True     For Each TT In T.PredecessorTasks         If TT.Flag5 <> True Then             If Not boolCrit Then                 FanBackward TT, boolCrit             End If             If boolCrit And TT.boolCritical Then                 FanBackward TT, boolCrit             End If         End If     Next TT End Sub ' Subroutine which will Filter with or without summary tasks Private Sub FilterMe() Dim V As View Dim Vis As Boolean Vis = False 'Ask the user if they want to show Summary tasks as well If MsgBox("Do you want to display Summary Tasks?", _     vbYesNo, "Display Summary Tasks?") = vbYes Then     boolSum = True Else: boolSum = False End If 'Construct the filter FilterEdit Name:="_Trace", TaskFilter:=True, _     Create:=True, _     OverwriteExisting:=True, _     FieldName:="Flag5", _     Test:="Equals", _     Value:="Yes", _     ShowInMenu:=False, _     ShowSummaryTasks:=boolSum 'Check to see if view exists For Each V In ActiveProject.Views If V.Name = "Trace" Then                     Vis = True End If Next V 'If it doesn't then create it If Not Vis Then ViewEditSingle Name:="Gantt Chart", _     Create:=True, _     NewName:="Trace", _     Screen:=1, _     ShowInMenu:=True, _     HighlightFilter:=False, _     Table:="Entry", _     Filter:="_Trace", _     Group:="No Group" End If ViewApply Name:="Trace" OutlineShowAllTasks End Sub 

TIP

This code starts with a set of comments that describe what it does, which fields it uses, and some limitations. These code comments are often the only documentation available to you and to users, so spend some time to use comments to record any particulars or instructions if they aren't obvious.


Notice that several of the control structures in this macro are the same as those from the previous examples. Also notice that there are several subroutines here, for two main reasons:

  • To help you write the code Just as eating an elephant is best done one bite at a time, writing and debugging code works best if you break the task into smaller parts. After you get small parts of the code written and working, you can add additional parts .

  • So blocks of code can be reused either within the same module or in some other place If there is a function you use twice or more in the same macro, it is worth making it a separate subroutine and then calling on that subroutine whenever you need it. That way you don't have to type the same code twice, and if you have to fix it, you need to fix it in only one place instead of searching for it in the various places you have used it and fixing it in each place.

The following sections walk through the code of the Trace Dependencies macro, looking at the new structures and concepts.

Public Versus Private Variables

Several variables are defined before any of the subroutines begin. This happens because some of these variables need to be available for more than one of the subroutines. If a variable is defined within a subroutine, it is not visible to other subroutines or procedures. If you define these variables at the module level, they are available within the module. If you preface them with the keyword Public , they are also available to other modules. By default they are private to the module. You can be explicit about this and declare them as private by using the Private keyword. At the module level, definition of any variables must occur before any subroutines in the module. Again, grouping variables in the same location so they can easily be found will make things easier for you.

Subroutines within a module are by default public and are accessible to other modules, unless they are defined as private. Generally, it is good practice to keep variables as private as possible, to avoid any problems because similarly named variables appear in other modules.

Note that several task variables are declared with a single dim statement; their names are separates with commas:

 Dim T, TT, Tselect As Task 

Some Boolean (true/false) and string variables are declared as well. Variable types, including definitions, can be found in the Microsoft Project Visual Basic help.

The Main Subroutine

The main subroutine is the one that you would call by name if you wanted to run the macro or assign it to a toolbar button.

The first thing it does is make sure that you have a task, and only one task, selected. This macro could be modified to handle more than one selected task, but at this time it does not support that.

The following line combines two conditions as a first test:

 If ActiveSelection.Tasks.Count <> 1 Or ActiveSelection.Tasks(1) Is Nothing Then 

Because of the OR , only one of the conditions needs to pass in order for the macro to move to the next operation. Once again, you must check whether the task is Nothing , or the macro will fail.

ActiveSelection is a collection of tasks. If you want to refer to an individual task within that collection, you need to refer to it by name or by its index number. Because you don't know the name, you refer to the first task in the collection by using the index 1. Because the Is Nothing test works on only the first task in the selection, it would not be sufficient by itself, but because you are also testing whether there are more or fewer than one tasks, the only time it needs to work is when one task is selected; therefore, the use of 1 for an index will always be safe.

If either of these tests is positive, the rest of the code in the If statement is executed, and a message box stating the problem is displayed. The next statement quits the macro:

 Exit Sub 

Because continuing with a selection that the rest of the macro can't handle would cause an error, exiting the subroutine is a sound error- avoidance practice. If this test is positive, the next line:

 Set Tselect = ActiveSelection.Tasks(1) 

sets the variable Tselect to the task that is selected in the ActiveProject file.

Requesting User Input by Using the Input Box

When a valid task is selected, you need to ask the user what he or she wants to do. You could have asked this earlier, but it saves the user trouble if you first check the input to make sure it is okay before the user takes the time to enter choices.

The following code solicits input from the user:

 strFanType = InputBox(("Please Enter Fan Type" & Chr(13) _             & Chr(13) _             & "P   (Predecessors)" & Chr(13) _             & "S   (Successors)" & Chr(13) _             & "A   (All)") & Chr(13) _             & "Leave Blank to quit here", "Fan-out Dependencies") 

To get input from the user, you can use an input box. An input box is similar to a message box, except that it has a space for the user to enter text. You use the string variable strFanType to hold the user's response. Notice that the text to be displayed as a user prompt is fairly long. The line continuation character ( _ ) is used to continue a statement from one line to another. This allows you to view the code within the VBE code window without having to scroll. When you put a line continuation character after each carriage return, the prompt text is shown similar to the way it would be in the input box itself.

You have set strFanType to be whatever text is typed by the user in the box. Of course, the user might type gibberish or have Caps Lock on, so the response might be different from what you expect. You can handle capitalization problems by converting the case of whatever the user types to uppercase. If the user types nothing, the macro once again exits the subroutine.

Requesting User Input by Using the Message Box

An alternative to using an input box is to use a message box. Earlier in the chapter you used a message box to display a message, but it is also useful for asking users to make a choice about something. A message box displays a message, waits for the user to click a button, and returns an integer indicating which button the user clicked.

The following code creates a message box that has Yes and No buttons and that asks if the user wants to display only the critical tasks:

 If Tselect.Critical = True Then     If MsgBox("Do you want to display only Critical Tasks?", _         260, _         "Display Critical Tasks Only?") = vbYes Then         gboolCrit = True     End If End If 

The number 260 indicates the type of buttons, including which is the default or highlighted button. You can find details about the values that can be used in place of 260 in the Microsoft Project Visual Basic help. Note that this box is displayed only if the task the user has selected is a critical task. If the task is not critical, then it should have few, if any, critical tasks that are dependent on it, so the code avoids an unnecessary choice by asking this question only when it is relevant.

Calling a Subroutine Without Parameters

After you have checked the input and verified that the user wants to actually do something, you can start doing some work. You need to clear the flag that you will be using to identify the tasks that are linked to the selected task. You could clear them all at the beginning, but because that will take some computation and might take some time, it is better to do it after you have checked for valid input and user intention .

Calling a subroutine is very easy. You simply enter the name of the subroutine. The one in the TraceDependencies macro is simple because you are not passing any parameters so the statement is as follows :

 ClearFlags 

If you look at the code for ClearFlags , you will see that it uses a For Next structure, just like in the TaskSummary macro. It is declared as private because you want to keep it local to the module. Because clearing fields is a common thing to do, you might have other ClearFlags subroutines elsewhere to clear other fields.

After this subroutine runs, control returns to the main subroutine. An Exit Sub inside this subroutine also returns control to the main subroutine because it is nested inside.

Using a Case Statement

One of the easiest ways to allow code to branch if there are more than a couple possible choices is to use a Case statement. A Case statement evaluates or reads a variable and executes different statements for each of the cases you have defined.

In the following example, you store the user's response in a variable called strFanType :

 Select Case strFanType     Case "P"         'Traces Only Predecessor Tasks         FanBackward Tselect, gboolCrit     Case "S"         ' Traces Only Successor Tasks         FanForward Tselect, gboolCrit     Case Else         ' Traces All Tasks - one pass for successors, then one for predecessors         FanForward Tselect, gboolCrit         FanBackward Tselect, gboolCrit End Select 

The Select statement reads the value of strFanType , and if the value matches any of the cases, the code for that case is executed. P traces predecessors and S traces successors, and if the input is anything other than S or P , the code for the case Else , which traces both predecessors and successors, is executed.

The keyword Else is used to catch anything other than the two cases that were defined before it. You could be stricter in checking the input and then you could construct a Select statement with only the three choices allowed, but because you are less strict, a user's typing error does not cause the macro to fail; it still runs and shows all the dependent tasks.

You can see that even with only three cases, this structure is easier than writing three separate If Then statements, and because all the choices are in the same place, it is easier to read, debug, and maintain. One thing to watch out for in a Select statement is that the cases are in the correct order. A Select statement runs the first case that is true, so you need to use care if you are selecting from ranges.

Calling a Subroutine with Arguments

After you have captured the information you need to begin the work of tracing dependencies, it is time to do the tracing. You actually have two subroutines that trace the tasks. One traces successors and one traces predecessors, but other than that, they are very similar. Unlike the ClearFlags subroutine, this subroutine has two additional variables, called arguments .

The following code calls the function FanBackward ; the variables following FanBackward are the arguments:

 Select Case strFanType     Case "P"         'Traces Only Predecessor Tasks         FanBackward Tselect, gboolCrit 

Arguments are values that are passed along to a subroutine in order for it to do its work. In the following subroutine, you pass the task you want to use as a starting point and a value that tells the subroutine whether you are only tracing critical tasks:

 ' Walks through all predecessors to a task and marks their flag5 as true Sub FanBackward(T As Task, boolCrit As Boolean) Dim TT As Task T.Flag5 = True     For Each TT In T.PredecessorTasks         If TT.Flag5 <> True Then             If Not boolCrit Then                 FanBackward TT, boolCrit             End If             If boolCrit And TT.Critical Then                 FanBackward TT, boolCrit             End If         End If     Next TT End Sub 

On the first line of the subroutine, instead of an empty pair of parentheses, you have two items within the parentheses. These are the arguments that the subroutine uses as input. The first is defined as a task, so you can pass any variable that is a task variable. The second, boolCrit , is defined as Boolean. You can pass any variable that is a Boolean type to this subroutine. The ability to write subroutines with arguments makes your code more flexible and reusable. In this example, it is essential that you use arguments, because you are passing the predecessors of the selected task to the macro and you don't know in advance what the tasks in the chain are going to be.

The other advantage of using subroutines that take arguments is that they can be used from other subroutines without requiring you to rename the variables you are using. As long as the variables are of the same type as the arguments, you can pass those objects or values along. In the TraceDependencies macro, you pass Tselect and gboolCrit to the subroutine. Within the subroutine, they are initially referred to as T and Crit .

Recursion

You should recognize most of the control structures in the FanBackward subroutine, but there is one new element. This subroutine calls itself while it is still running by using this line:

 FanBackward TT, boolCrit 

This process is called recursion. A recursive procedure is one that calls itself, and it is very useful if you are trying to trace a hierarchy. In this case, we are tracing predecessors. We select a predecessor and then call the function to select each of its predecessors. You use recursion here because you want to do the same thing to each of the predecessors that you are doing to the initial task. You also want to do the same thing to each of the predecessor's predecessors and so on.

TIP

You need to be careful with recursion because it is possible to create a recursive procedure that does not have an end. Each time the procedure is run, a certain amount of memory is reserved for it. If the process continues to run thousands or millions of times, it will eventually run out of memory and cause the application to fail or crash. To prevent this you use a "base case," where the procedure stops calling itself. Because the procedure in the FanBackward subroutine executes once for each predecessor to a task and you know that the predecessors are finite in number, you can be certain that the procedure will stop.


Recursion can be a bit confusing, so let's look at what happens, step-by-step:

  1. First, Flag5 for the task we are working on is set to true:

     T.Flag5 = True 

    This is the field you will be using to filter the project on later.

  2. You step through each of the selected tasks' predecessors. You use the variable TT to hold the predecessor task that you are working on:

     For Each TT In T.PredecessorTasks 
  3. You test to see if Flag5 for that task has already been set to true:

     If TT.Flag5 <> True Then 

    If it is true, you have already traced that path, perhaps while tracing the predecessors to another activity. To be efficient, you can skip the branches that have already been traced.

  4. After testing to see whether you are looking for only critical tasks, you call FanBackward again, passing along the task TT this time:

     If Not Crit Then     FanBackward TT, Crit End If 

    The subroutine begins again and follows all the predecessors of that task, and as it goes through them one by one, it traces all their predecessors until it runs out of predecessors to trace. Then, because of the For Each statement, it goes to the next predecessor in the collection and does the same thing.

As you can see, recursion can be a powerful tool when you're tracing dependencies, objects with a parent/child relationship, or any other sort of hierarchy. For example, the following code fills in the Text5 field with a string that shows all the tasks' parents and the parents' parents:

 Sub Inherit(T as Task) T.Text5 = T.Name Genes T End Sub Sub Genes(T As Task) Dim TT As Task For Each TT In T.OutlineChildren    TT.Text5 = T.Text5 & "_" & TT.Name    Genes TT Next TT End Sub 

Controlling Filtering and Views

After you have correctly set Flag5 for all the tasks in the hierarchy, you are almost ready to set the display to show only those tasks. To do that, you use the FilterMe subroutine, which gathers input from the user and then constructs and applies a filter. The following is the FilterMe subroutine:

 Private Sub FilterMe() If MsgBox("Do you want to display Summary Tasks?", _         vbYesNo, _         "Display Summary Tasks?") = vbYes Then     boolSum = True Else: boolSum = False End If 'Construct the filter FilterEdit Name:="_Trace", _         TaskFilter:=True, _         Create:=True, _         OverwriteExisting:=True, _         FieldName:="Flag5", _         Test:="Equals", _         Value:="Yes", _         ShowInMenu:=False, _         ShowSummaryTasks:=boolSum 'Check to see if view exists For Each V In ActiveProject.Views     If V.Name = "Trace" Then         Vis = True     End If Next V 'If it doesn't then create it If Not Vis Then     ViewEditSingle Name:="Gantt Chart", Create:=True, NewName:="Trace", _     Screen:=1, ShowInMenu:=True, _HighlightFilter:=False, _     Table:="Entry", Filter:="_Trace", Group:="No Group" End If ViewApply Name:="Trace" OutlineShowAllTasks 

The first part of this subroutine should be familiar to you because it is similar to the message box you worked with earlier in the chapter. Next, in the subroutine, you expand the view to show all tasks. This is an important step because Project filters based on the visible tasks. If some of the task summaries are collapsed , the tasks within those summaries will not be shown.

Next, the subroutine creates a filter. The syntax for creating a filter can be a bit complex, so this filter was created by recording a macro to get the basic structure and then putting the variable boolSum in to pass the value that you get from the user. The subroutine creates the filter each time you use it and overwrites the previous version because it must include the new value for boolSum , which controls the display of summary tasks.

graphics/new_icon.jpg

One of the new objects in Project 2003 is the View object. You want to have a separate view to display the filtered tasks so that the user's original view is preserved and he or she can easily flip back and forth between the traced tasks and the complete project. The following code checks to see whether a view named Trace exists, and if it does not exist, it creates one:

 'Check to see if view exists For Each V In ActiveProject.Views     If V.Name = "Trace" Then         Vis = True     End If Next V 'If it doesn't then create it If Not Vis Then     ViewEditSingle Name:="Gantt Chart", Create:=True, NewName:="Trace", _     Screen:=1, ShowInMenu:=True, _HighlightFilter:=False, _     Table:="Entry", Filter:="_Trace", Group:="No Group" End If ViewApply Name:="Trace" OutlineShowAllTasks End sub 

The check for existing views is important because you might run this macro many times, and you don't want to create a new view each time. (Note that the view is defined with the _Trace filter, which you created earlier as one of the arguments.) When you have finished this, you apply the view. Then you expand the outline to show all outline levels to make sure no tasks are hidden. The subroutine then exits and control returns to the main TraceDependencies macro.

The final statement in the macro is used to once again select the original task:

 Find Field:="ID", Test:="equals", Value:=Tselect.ID 

You can use the Find method to find tasks in different fields and to select or highlight the task in the display.

 < Day Day Up > 


Special Edition Using Microsoft Office Project 2003
Special Edition Using Microsoft Office Project 2003
ISBN: 0789730723
EAN: 2147483647
Year: 2004
Pages: 283
Authors: Tim Pyron

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net