Work with the Timescaled Data Object in Project 2007

UPDATE:

I have updated the code in the file download link below. I don't have time to rewrite the post to explain the changes in full but I found an issue with the code that caused a problem. Basically, if the start date of an assignment slipped into the future then the code would not act on the original dates because it was setup to use the Assignment start date as the earliest point for acting on the assignment values. Well if the assignment moved (the baseline data was 'in the past') then the code would just not do what it was designed to do. The new code at the link below will work better. Again ALWAYS test your code in a bunch of different situations. This is a great object lesson in how a developer can get tunnel vision and write code for a specific situation that leaves out other possible situations. I was writing based on a quick situation I could create and recreate very quickly during the coding process and it obviously did not include the assignment start date moving. It only included the assignment finish date moving. :-)

Like all good VBA macros we have to start with a problem that needs to be addressed or a function that needs to be added. Our problem today will be the fact that if you add a new task to an existing baseline and there is a resource assigned to this task the Baseline Work for that assignment is not rolled up to the Resource level Baseline Work field. Once the task is added to the baseline the Baseline Work for the Resource does NOT show the additional Work added to the baseline by the new assignment. This is also true of the Resource level timescaled baseline work data. The VBA code we will review here addressed this issue and gives us an opportunity to look at how to use the TimeScaledData method to access the timescaled data for tasks, resources, and assignments.

The steps to reproduce this issue are as follows:

1. Create a task and assign a resource
2. Baseline the project
3. Create another task and assign the same resource
4. Add just this second task to the project by clicking Tools | Tracking | Set Baseline and selecting "Selected Tasks"
5. Go to the Resource Usage view and insert Baseline Work into the timescaled area

The example below shows how at the resource level Dwight still only has 64 hours of Baseline Work when in reality there should be 64 + 32. The timescaled area also shows only 8 hours of baseline work at the resource level for 19^th, 20^th, 23^rd and 24^th when there should be 16 for each of those days.

What is the TimeScaledData Method?

This method returns a collection of data that represents a specific type of data (Work, Cost, Baseline Work, etc) for a specific period of time (the duration of a task or assignment for example) that will be provided broken down by the specified time unit (days, weeks, months, etc). This collection can then be worked with to either read specific data about specific time slices in the collection or even to set specific data for a time slice in the collection. It is a very powerful feature of VBA but one that is not widely understood.

You can download the full macro here.

We will now review each section of the code and go over what it does:

The Variables

Dim R As Resource
Dim A As Assignment
Dim aBL As TimeScaleValues
Dim rBL As TimeScaleValues
Dim Counter As Integer

This section defines (or dimensions) the variables we will use.
R is an object variable that will represent one or more resources
A is an object variable that will represent one or more assignments
aBL is an object variable that represents timescaled data. It gets 'loaded' with data by using the TimeScaledData method. This object will represent the assignment timescaled values (hence the 'a')
rBL is just like aBL but it will represent the resource timescaled values (hence the 'r')
Counter is a variable that will be used to loop through the members of the collections when we need to access those values.

The Resource For…Next Loop

For Each R In ActiveProject.Resources
If Not (R Is Nothing) Then

Because we established above that 'R' was to represent a resource object we can use this variation of a For…Next to loop through 'each' resource (R) in the Resources collection of the active project.

The If…Then statement is testing to make sure that there are no blank lines in the resource sheet of the project. Blank lines cause a problem for VBA when dealing with the Tasks and Resources collection. If the loop above encountered a blank line an then the macro tried to access any property (the name, work or any other field for example) the macro would get an error. The test in the If…Then statement makes sure that the code 'under' it will only run if the object represented by 'R' is an actual resource.

Setting the Values to Zero

R.BaselineWork = 0

For Each A In R.Assignments
Set rBL = R.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjResourceTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)

For Counter = 1 To rBL.Count
rBL(Counter).Value = 0
Next Counter

Next A

The rest of our macro below will be recalculating what the Resource level baseline and timescaled baseline work data SHOULD be after a task is added to the baseline. This section of code is setting those values to 0 so that the code below can calculate the values correctly. This section shows us the first use of the TimeScaleData method.

The For..Each…Next here is looping through all the Assignments (all the 'A' objects) in the Assignments collection for the resource represetted by 'R'. It is then setting the 'rBL' variable to equal the collection of timescaled data defined in the R.TimeScalData method. It sets the start date of the collection to equal the start date of the current assignment (A.Start) and the finish date to equal the finish date of the current assignment (A.Finish). The collection will contain Baseline Work because we are setting the Type to equal pjResourceTimescaledBaselineWork. The timescale will be days and the Count will be 1, meaning that each object in the collection will represent 1 day. If count were set to 2 then each object (or if you prefer 'time slice') would be 2 days.

Next we have a For…Next loop that sets the value for Counter to be 1 through the number of objects (time slices) in the 'rBL' collection we just created. This loop is letting us move through each slice so that we can do something to it, either read it or write to it. The line that reads 'rBL(Counter).Value = 0' is setting the value for Timescaled Baseline Work for the time each timeperiod in the assignment (in this case days) for the resource represented by 'R' to equal zero. Let's say that the assignment represented by 'A' started on Day1 and finished on Day 5. This would mean that the 'rBL' collection would have 5 members (time slices) each representing 1 day. This means that the For Counter = 1 to rBL.Count would loop through 5 times. On the first loop through the rBL(Counter).Value represents the value of timescaled Baseline Work for the resource for Day1. On the second loop through it is Day 2 and so on. This sets the values for resource timescaled baseline work to 0 for the duration of the assignment represented by 'A'. This will get repeated for each assignment for the resource represented by 'R'.

You may ask yourself why the code above is setting the Resource timescaled values to 0 since we are using the Assignment start and finish dates. You might wonder why we are not just setting the Assignment values to 0 since the code above would have us in many cases setting the same days values to 0 several times (in the case of overlapping assignments.) This is a good question. The answer is that the problem in Project that we are solving is one where the assignment values for baseline work are correct but the resource values are incorrect. If we reset the assignment values to 0 then we would have no idea how to set the resource values. We need the assignment values intact. The price we pay for this is needing to loop through some days at the resource level a few extra times.

Adding up and Setting the Correct Values

For Each A In R.Assignments

R.BaselineWork = R.BaselineWork + A.BaselineWork

Set aBL = A.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjAssignmentTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)

Set rBL = R.TimeScaleData(StartDate:=A.Start, EndDate:=A.Finish, _
Type:=pjResourceTimescaledBaselineWork, TimescaleUnit:=pjTimescaleDays, Count:=1)

For Counter = 1 To aBL.Count
If Not (aBL(Counter).Value = "") Then
rBL(Counter).Value = rBL(Counter).Value + aBL(Counter).Value
End If
Next Counter Next A

This section is in large part a repeat of the previous section with the addition of the setting of the 'aBL' collection that will allow us to read the assignment level timescaled Baseline Work data and insert it into the 'rBL' collection. So to that end you see the Set 'aBL' and Set 'rBL' lines. They are setting the collections for both assignment and resource based on the assignment start and finish because we are reading from the assignment. Next we have a slightly expanded 'For Counter' loop from the last section. It contains an If…Then statement we use to test to make sure that value of the aBL object currently being evaluated does not contain a null value. Project sets the Baseline Work for the default nonworking days of a calendar (Saturday and Sunday) differently than non-default nonworking days (days the user sets to nonworking via the Change Working Time dialog.) Saturdays and Sundays get set to Null values while nondefault nonworking days get set to 0. Testing to make sure that the value of the rBL object is not null saves us from getting an error.

The next line is the key to the whole thing. It looks at the value of the Assignment timescaled Baseline Work and adds it to the current value of the Resource timescaled Baseline Work. It does this for each timeslice in the aBL collection. This is repeated for each assignment at which point the resource timescaled baseline work is equal to the sum of the timescaled baseline work for each of the assignments for the resource.

Closing Out

End If
Next R
End Sub

The End If here is the closing of the "If Not (R Is Nothing) Then" test at the beginning of the routine.

The Next R is the Next for the "For Each R In ActiveProject.Resources" line in the beginning. Once the execution hits this line it has completed the rest of the code for one resource and if there are more than one resource it will repeat until it has run through for each resource.

The End Sub ends our macro.

Expanding on the Basics Above

The example above works with assignment and resource timescaled baseline work. But the same concept can be used to work with any of the timescaled data in Project with just a few changes to the code we worked with. These concepts can be applied to working with task timescaled data as well. For example if we wanted to access task timescaled cost data the code might look like this:

Set taskBL = T.TimeScaleData(StartDate:=T.Start, EndDate:=T.Finish, _
Type:=pjTaskTimescaledCost, TimescaleUnit:=pjTimescaleDays, Count:=1)

This code would return a collection (in the object variable taskBL) of timescaled task cost data where each member of the collection was 1 day in duration starting with the start of the task and ending on the finish date of the task. If we wanted it in months instead of days we would change 'pjTimescaleDays' to 'pjTimeScaleMonths'. If we wanted it just for the first day of the task we would set the StartDate and EndDates to both be equal to T.Start.

One of the great things about VBA is that you don't have to remember all of the parameters or constants you need to use to make this kind of method work. The VBA Editor prompts you if you used named parameters. As soon as you enter the open parentheses after TimeScaleData the editor tells you the parameters for the method.

You could just type A.Start in the first position and then a comma and A.Finish but good coding practice dictates that you type StartDate:=A.Start. If you do this it makes your code easier to read and gives the advantage of being prompted with the constants for things like Type and TimeScaleUnit like below. This way you do not need to remember the many, many constants available to you.

So Why Would I Use This?

You might ask yourself why this would be important? Always a good question. This kind of code is essential if you want to look at or edit specific time slices of data about tasks, assignments or resources. This would include code to integrate a timesheet system with Project. For sure most companies that are integrating systems like SAP will be using Project Server and using the PSI but a 'timesheet system' might be as simple as a set of Excel spreadsheets you distribute to your resources for them to enter status data. If they do it in a timescaled way (number of hours of work done per day or per week then this code is exactly how you would base a macro to pull that data from Excel and put it into Project in a timescaled way. The TimeScaleData method is also the best way to create your own custom exports from your Project files into other systems that need data on a day by day basis. These methods are shown here using VBA native to Project 2007 but the same concepts can be used within Visual Basic to create more robust applications.

Disclaimer

This code is presented here for educational purposes only. Before such code is used in a production environment it should be seriously tested for all possible situations to ensure that it performs as expected. It's presentation here does not imply any guarantee or warranty on this code. Further, this code is not supported by Microsoft in any way. I am presenting it here as a means of education only.

Comments

[Murtuza]

I have a problem in Using Project Professional 2007 with Project Server 2007. I am importing a project plan to Project Server. The wizard starts and everything goes fine. But on the last step of saving... the message in the status bar shows... Saving Job 0% Completed... Expected wait time 120 sec. and it remains 0% all the time. I have waited around 1 hour but nothing proceed and i cannot see that project on Project server as well. Even when i tried publishing it behaves in the same way.

I hope there is some way around it...