« This is not going to fit in the minivan... | Main | Aftermath »

Recursion in Project VBA

The Fifth in a Series of Short Notes about Using Project VBA
Recursion is a programming techique which is similar to the process of taking a video of your television when the television is displaying the video output of your video camera. The result - an endless tunnel of pictures of your television.

So how can this be useful in programming, and more specifically in programming Microsoft Project? Well, recursion is also well suited for dealing with parent/child relationships or dependencies, both of which are essential parts of Project. Recursion allows you to easily get the subtasks of the subtasks of the subtask of a task and because it continues indefinitely (or until it hits a limit) it will get to the last task in the project without you having to keep track of how many levels deep it needs to go.

It can be difficult to grasp the concept without a concrete example so let's start with one right away and explain the details as we go along. Let's say that you have a number of tasks which may be viewed individually (perhaps in project server) and they will no longer show the heirarchy which is in the file. Some may even have the same name as each other, just like you can have two John's who are unrelated and different. The solution to this confusion is to use a text field to show the entire path to the task. That path is made up of all the names of the parent tasks of the individual task.

One way to do this is brute force:

Dim mytask As Task
Dim myoutlinelevel As Integer
myoutlinelevel = 1
While myoutlinelevel < 10
For Each mytask In ActiveProject.Tasks
If Not (mytask Is Nothing) Then
If mytask.OutlineLevel = myoutlinelevel Then
mytask.Text2 = mytask.OutlineParent.Text2 & " | " & mytask.Name
End If
End If
Next mytask
myoutlinelevel = myoutlinelevel + 1
Wend
End Sub

The trouble with this approach is that it runs through the entire set of tasks one time for each level of heirarchy that you want to name. And, you have to define how many levels deep you want to go. Even if you have only one level of heirarchy this code will still read and check each task 10 times. And if you have more than 10 levels, the tasks beyond the 10th level will not get labeled correctly.

The solution is to use recursion. With recursion we ask the program to name all the children of a task and then name all the children of that task all the way down until there are no more children. We do this by having a procedure which calls itself. Here we are using a procedure called "kids" which calls the same procedure for all of the child tasks - when it runs using those child tasks it will get all their child tasks etc. etc. etc.

Sub kids(ByRef t As Task)
Dim kid As Task
t.Text2 = t.OutlineParent.Text2 & " | " & t.Name
For Each kid In t.OutlineChildren
kids kid
Next kid
End Sub

Pretty simple. Now the only question is how to get it started off. We can't put the code to start it inside the procedure or it will keep restarting itself. So we write a procedure which sets the starting task and then calls the kids procedure:

Sub recursionExample()
Dim t As Task
Set t = ActiveSelection.Tasks(1)
kids t
End Sub


Sub kids(ByRef t As Task)
Dim kid As Task
t.Text2 = t.OutlineParent.Text2 & "-" & t.Name
For Each kid In t.OutlineChildren
kids kid
Next kid
End Sub

That is all there is to it. I have an example of how recursive techniques can be used to trace dependencies on my website which adds some additional logic so it can trace forward or backward or only critical tasks, but the basic principle is the same.

One thing to be aware of before you use recursion is that whatever you are recursing through does require some limit or stopping point. In this case it stops when there are no further children. In the Trace macro it stops at the end of the chain of dependencies. However, if you are not careful you can construct something that will continue indefinitely. To avoid this, try setting a breakpoint so you can step through the code the first few times to make sure it doesn't break. And always back up your files before you start.

RELATED POSTS

Comments (4)

Nigel:

Many thanks. It is a while since I last coded and I was wrestling with a problem that I knew could only be solved by recursion. But, I thought, can VBA handle recursion or will it self destruct (I should have more faith).

Alex:

Thanks for posting this! I also assumed that VBA couldn't handle recursion. C'mon, you have to use that special ByRef keyword and all, I was almost right.

You saved me, thanks!

@ Alex ... I have to chuckle reading your comment, because you don't need ByRef to recurse in VBA. The main reason for the chuckle is I learned ByRef AFTER building recursive routines, and it would have been awful handy at the start.

Pedro Aquino:

This is a very interesting and useful article. Thank you for this big contribution to Project Management.

Post a comment

(Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)

About

The previous article is This is not going to fit in the minivan....

The next article is Aftermath.

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