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