VBA for Projects - Using TimeScaleValues with tasks, assignments and resources

Advanced users of Microsoft Project will know that Project can be quite flexible in the way that task assignments are handled.  For example, it is possible to manipulate the hours worked across different days so that they are not equally spread.  This is really useful if a resource can only work certain days of the week on a particular task.  To enable these different allocation fluctuations to be examined and manipulated in Microsoft Project we have the TimeScaleValues collection that contains TimeScaleValue objects.

TimeScaleValue Object

To create a collection of TimeScaleValue objects we need to use the TimeScaleData method which is callable from the Task, Assignment or Resource objects.  The idea of the TimeScaleValue object is that you can specify a time period and time slice and return a list of TimeScaleValue objects for each timeslot in your range.  By doing this, you can examine work and cost data on the different days.  You an either do that on the task level, the resource level or the assignment level.

This is obviously very powerful but can take a while to get used to when working with this method.  The method takes the following parameters.

Name	Data Type	Notes
Start Date *	Variant	The start date of where we are going to look at for your data. You might want to use for example ActiveProject.ProjectStart if looking across the whole project or some other date. If you are looking at weeks and specify a Thursday the actual start date will be rounded back to the beginning of the week (which depends on your locale).
EndDate *	Variant	Like the start date, this date is the end of your interval and you may wish to set it to the ActiveProject.ProjectFinish date.
Type	Long	This is the type of data that we want to be returned in our TimeScaleValue objects. The default is Work which is the same as specifying pjTaskTimescaledWork. The other available options include things like pjTaskTimescaledCost for looking at cost and pjTaskTimescaledActualWork. Follow this link for a full list of PjTaskTimescaledData that can be used.
TimeScaleUnit	Long	This is the length of time that each chunk of your TimeScaleValue objects will cover. The default is to look at weeks which uses pjTimescaleWeeks but you might want to choose days using pjTimescaleDays or any of the other possible values listed here: PjTimescaleUnit
count	Long	This is the number of chunks to group together. In other words, if your TimeScaleUnit is pgTimescaleWeeks and your Count is 2 then two weeks will be grouped together in the results

Example using Tasks

The following example uses the TimeScaleData method for the Task object.  It looks at the Work fore the period from the project start date to the project finish date on a daily snapshot.

For Each t In ActiveProject.Tasks
    If Not t Is Nothing Then
        For Each tsv In t.TimeScaleData(ActiveProject.ProjectStart, _
                                        ActiveProject.ProjectFinish, _
                                        pjTaskTimescaledWork, _
                                        pjTimescaleDays)
            If CInt(Val(tsv.Value)) > 0 Then
                Debug.Print "Task: " + t.Name + _
                             " Date: " + CStr(tsv.StartDate) + _
                             " Time: " + CStr(tsv.Value)
            End If
        Next tsv
    End If
Next t

Download Full File

The full file for the code below can be downloaded from here (It uses a proof of work formula before opening):

MSP VBA - Part 3 - Using timescaled data with Projects and VBA

About Me

If you like this post then please comment below.  Please also make a small contribution so that I can post more code snippets and samples here.

Bitcoin:      1MhaZvTqftdebwNihisrAbz22zaSG6tFcN

Ethereum:  0xe69b176A1A5492f19E3595389EA094C732723b69