Module 19: Working with Timephased Data (VBA Programming for Microsoft Office Project Versions 98 Through 2007)

  • Article

This article is an excerpt from VBA Programming for Microsoft Office Project Versions 98 Through 2007, by Rod Gill, from Soho Corp. (ISBN 0-9759828-7-7, copyright Rod Gill 2006, portions copyright Soho Corp. 2006, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Learning Objectives

After completing this module, you will be able to:

  • Understand how the system handles timephased data

  • Read and write timephased data

Contents

  • Understanding Timephased Data

  • Reading Timephased Data

  • Exporting Timephased Data to a .csv File

  • Hands On Exercise: 19-1

  • Writing Timephased Data

  • Hands On Exercise: 19-2

  • Additional Resources

Understanding Timephased Data

Timephased data is the information you see in Microsoft Project on the right side of the Task Usage and Resource Usage views. Using Project VBA, you can read and write timephased data in exactly the same way you do it manually in either of these Usage views. Once you can handle timephased data programmatically, you can update projects with data from timesheet systems and create all sorts of time-based reports.

Project VBA handles timephased data using the TimeScaleValue object. A TimeScaleValue object holds data for a particular time slice in your schedule, which is the same as a particular cell in a Usage view. TimeScaleValues is a collection of TimeScaleValue objects, and therefore, holds timephased data for a specified date and time range. You fill TimeScaleValues collections using the TimeScaleData method.

NoteNote

In Project 98, the TimeScaleData method was called TimeScaledData. This can still be used for backwards compatibility, but for future compatibility, you should only use the TimeScaleData syntax.

The TimeScaleData method is available for the following:

  • Tasks

  • Resources

  • Assignments

  • Any timescaled data, such as Work, Cost, etc.

  • Any date and time range

  • Any time units, such as hours, days, weeks, etc.

When providing dates for the TimeScaleData method, Project VBA rounds them up to a whole time unit. For example, if you specify a weekly time interval, but provide a start date halfway through a week, the system returns the value for the whole week.

So, if you want only the last 3 days of a week to start your date range, you either need to use daily intervals and subtotal daily values into weeks or do two separate TimeScaleData calls, one for the first three days and the other for the rest of the time frame in weeks.

Reading Timephased Data

The sample code below reads data for a particular task and resource for every day of the project by day:

Sub TimePhasedDataTest() 
Dim tsv As TimeScaleValue 
Dim tsvs As TimeScaleValues 
   'Timephased for Task with a UniqueID of 1 
   Set tsvs = ActiveProject.Tasks(1).TimeScaleData( _ 
      StartDate:=ActiveProject.ProjectStart, _ 
      EndDate:=ActiveProject.ProjectFinish, _ 
      Type:=pjTaskTimescaledWork, _ 
      TimeScaleUnit:=pjTimescaleDays, Count:=1) 
   For Each tsv In tsvs 
      Debug.Print "Start: " & Format(tsv.StartDate, _ 
         "Long Date"), "Work: " & Val(tsv.Value) / 60 & "h" 
   Next tsv 
   Debug.Print 'Blank line 
   'Timephased for Resource "Res" 
   Set tsvs = ActiveProject.Resources("Res").TimeScaleData( _ 
        StartDate:=ActiveProject.ProjectStart, _ 
      EndDate:=ActiveProject.ProjectFinish, _ 
      Type:=pjResourceTimescaledWork, _ 
      TimeScaleUnit:=pjTimescaleDays, Count:=1) 
   For Each tsv In tsvs 
      Debug.Print "Start: " & Format(tsv.StartDate, _ 
         "Long Date"), "Work: " & Val(tsv.Value) / 60 & "h" 
   Next tsv 
End Sub

The line continuations make the preceding code 12 lines longer than it needs to be, so this sample code is actually quite compact. I wrote the code in two blocks: one to read task data and the other to read resource data. I defined two variables named tsv and tsvs. A good naming convention is to name collections of variables always with a plural name such as tsvs. Therefore, tsv is a single object, not a collection of objects.

Set tsvs = ActiveProject.Tasks(1).TimeScaleData creates the timescale collection of timescale objects based on task ID 1. As parameters for the method, I use the Start and Finish dates of the project. With two tasks in a test project, each with a Duration of 5d, linked with a Finish to Start dependency, and with the same resource assigned on each task at 50% Units, the schedule looks like Figure 19-1.

Figure 19-1: Test schedule

19-1: Test schedule

The Resource Usage and Task Usage views both show a total of 4 hours per day for the entire 2 weeks of the project. The TimePhasedDataTest procedure prints first the task data, then the resource data to the Immediate Window with the following results (assuming a start date of 1/1/2007):

Start: Monday, 1 January 2007 Work: 4h 
Start: Tuesday, 2 January 2007 Work: 4h 
Start: Wednesday, 3 January 2007 Work: 4h 
Start: Thursday, 4 January 2007 Work: 4h 
Start: Friday, 5 January 2007 Work: 4h 
Start: Saturday, 6 January 2007 Work: 0h 
Start: Sunday, 7 January 2007 Work: 0h 
Start: Monday, 8 January 2007 Work: 0h 
Start: Tuesday, 9 January 2007 Work: 0h 
Start: Wednesday, 10 January 2007 Work: 0h 
Start: Thursday, 11 January 2007 Work: 0h 
Start: Friday, 12 January 2007 Work: 0h 
Start: Monday, 1 January 2007 Work: 4h 
Start: Tuesday, 2 January 2007 Work: 4h 
Start: Wednesday, 3 January 2007 Work: 4h 
Start: Thursday, 4 January 2007 Work: 4h 
Start: Friday, 5 January 2007 Work: 4h 
Start: Saturday, 6 January 2007 Work: 0h 
Start: Sunday, 7 January 2007 Work: 0h 
Start: Monday, 8 January 2007 Work: 4h 
Start: Tuesday, 9 January 2007 Work: 4h 
Start: Wednesday, 10 January 2007 Work: 4h 
Start: Thursday, 11 January 2007 Work: 4h 
Start: Friday, 12 January 2007 Work: 4h

In the preceding result, notice the 0h for Saturday and Sunday. The last week of working days for the task Timephased data has 0d of work because it is for Task 1 only does not include work for Task 2. The resource data is for the resource Res who is assigned to both tasks; hence, the system contains work for both weeks.

When entering the Type and TimeScaleUnit parameters, IntelliSense displays a complete list of all options, so it is easy to select the one you want and get it right the first time. Note that the Type for the Task block is pjTaskTimescaledWork while the Type for the Resource block is pjResourceTimescaledWork.

A common issue with using timescaled values is that if a timescale range is greater than the source Task, Resource, or Assignment Object, then tsv.Value returns an empty string ("") for time slices outside the object's date range. Val(tsv.Value) converts the empty string to a zero which is much more useful. Timescale value Properties always return Work in minutes, so dividing by 60 gives you hours of Work.

Exporting Timephased Data to a .csv File

In this module, you learn how to export Timephased Data to a .csv file. In Module 21, you will export it to Excel and produce the data for an S-Curve graph. Figure 19-2 shows a sample S-Curve.

In Figure 19-2, if today is week 18 then the project is clearly behind schedule. In fact, it looks like there was no work done for 2 weeks (flat line on the Work graph around week 17). The schedule also shows greatly increased hours per week (the line is steeper) from week 19 onwards and that progress should catch up with the baseline around week 21. The project schedule is finishing 2 weeks late and with more work than predicted.

Figure 19-2: Sample S-Curve report

19-2: Sample S-Curve report

An S-Curve graph is a graph of the cumulative Work or Cost compared against the cumulative Baseline Work or Baseline Cost. You can graph various Earned Value variables as well, but we traditionally use cumulative Cost and Work. The result is a graph that clearly shows progress against the baseline, with the steepness of the curve indicating hours per week usage (the steeper the curve the more hours per week).

This macro exports the basic data and relies on Excel to calculate and plot cumulative hours. Microsoft Project can provide cumulative Work and cumulative Cost data, but not cumulative Baseline Work or cumulative Baseline Cost.

The Code for this macro appears in two parts, with the first part adding two methods to the text Class created in Module 5. The two methods are FileOpenWrite and WriteLine. The added code is as follows:

Sub FileOpenWrite() 
   Open prvPath For Output As #1 
End Sub 
Sub WriteLine(strLine As String) 
   Write #1, strLine 
End Sub

Enter this code into the VBE in the Class Module clsTextFile. By clicking on the Open and Write words then pressing F1 for Help, you can quickly find out what these VBA commands do.

By reusing and adding to the Text Class and using the preceding code for timephased data, the macro to export timephased data for a whole project is as follows:

Sub SCurveCsvFile() 
Dim tsv As TimeScaleValue 
Dim tsvs As TimeScaleValues 
Dim txt As New clsTextFile 
   txt.FilePath = "C:\TimeScaleData Work.csv" 
   txt.FileOpenWrite 

   Set tsvs = ActiveProject.ProjectSummaryTask. _ 
      TimeScaleData(StartDate:= _ 
         ActiveProject.ProjectSummaryTask.Start, _ 
         EndDate:=ActiveProject.ProjectSummaryTask.Finish, _ 
         Type:=pjTaskTimescaledWork, _ 
         TimeScaleUnit:=pjTimescaleWeeks, Count:=1) 
   For Each tsv In tsvs 
      txt.WriteLine Format(tsv.StartDate, _ 
         "Medium Date") & ", " & Val(tsv.Value) / 60 
   Next tsv 
   'Tidy Up 
   txt.FileClose 
End Sub

The preceding code sample includes seven lines of code, plus variable declarations and the Text Class! You can edit the file path of the .csv file to be whatever you want. The ProjectSummaryTask property is a Task Object and can therefore return a TimescaleValues collection. The one above is for Work by week from the project's start to finish. txt.WriteLine takes a string as a parameter so your code needs to build the string for the entire row to be saved to the csv file.

Hands On Exercise: 19-1

Exercise 19-1

Work with TimeScaleData code.

  1. Get the SCurveCsvFile code to work by entering the code or using the Module 19 Sample Code.mpp file.

  2. Add code to export Baseline Work to C:\TimeScaleData Baseline Work.csv.

  3. Add code to export Cost and Baseline Cost to two more .csv files.

Writing Timephased Data

Writing timephased data is slightly more complicated than reading it. If you want to update the timescale values for an assignment that lasts for five days, for example, and you get TimeScaleValues for the five days, then you can easily update those five TimeScale Objects. However, if you have actual data for six days, then a TimeScaleData Object for day 6 does not exist and you need to add one using the tsvs.Add method.

That is the extra complication. Provided you make sure you do not try to refer to a TimeScaleValue Object that does not exist, writing timephased data is as easy as reading it. The following code updates actual hours with the index number of the tsv object, which goes from 1 to 10 for a 10 day task:

Sub TimePhasedDataWritetest() 
Dim tsv As TimeScaleValue 
Dim tsvs As TimeScaleValues 
   'Update Task 1 with Actual Data 
   With ActiveProject.Tasks(1) 
      Set tsvs = .TimeScaleData( _ 
         StartDate:=.Start, EndDate:=.Finish, _ 
         Type:=pjTaskTimescaledActualWork, _ 
         TimeScaleUnit:=pjTimescaleDays, Count:=1) 
   End With 
   For Each tsv In tsvs 
      tsv.Value = 60 * tsv.Index 
   Next tsv 
End Sub

With ActiveProject.Tasks(1) avoids specifying ActiveProject.Tasks(1) for every parameter which makes it simpler to read the code. This code happily assigns Actual Work to Saturday and Sunday, but this is only a test, after all. Day 1 will get one hour, day 2 gets two hours and so on.

If you need to extend the task with two extra days of work, then add the following code at the very end of the procedure:

tsvs.Add Value:=(tsvs.Count + 1) * 60 
tsvs.Add Value:=(tsvs.Count + 1) * 60

Remember that the Value for Work must always be in minutes.

Hands On Exercise: 19-2

Exercise 19-2

Write TimeScaleData code.

  1. Get the TimePhasedDataWriteTest code to work by entering the code or using the Module 19 Sample Code.mpp file. Make sure you have a Task 1 before running the code.

  2. Try creating a .csv file with actual values, and then read it into your project and update a task with it. You will need Unique ID's to identify the correct task and match dates in the csv file with tsv.Start dates.

Additional Resources