Using VBA To Check Task and Assignment Fields

I’m currently working on a project to create a macro for MS Project. The intention is that this macro will  fire either on Save or on the click of a button and will check to see if certain fields have been filled out for tasks and assignments. Any missing fields will be reported back to the user.

We use Project Server to generate a “Daily Log” where we can record work against tasks that have been assigned to us. In order to make the completion of the daily log as easy as possible for the end users and for the plan owner (not to mention our finance department), we want to be able to check that most of the required information is in place in the first place.

Task fields

It is relatively easy to loop through tasks and check for task level fields.

The easiest thing to do is to work with all the tasks in the current collection of tasks in the active project.

First we need to check that the task is actually a valid task (i.e. not a blank row) and that it is not a summary task.

Then we can populate a number of variables with the required values.

Dim ts As Tasks
Dim t As Task
Set ts = ActiveProject.Tasks
Dim tCPSScope As String     ' Task field In CPS Scope
Dim tAtCPS As String        ' Task field At CPS
Dim tTaskCat As String      ' Task field  Task Category
Dim tBillCat As String      ' Task field  Billing Category

For Each t In ts
    If Not t Is Nothing Then
        If Not t.Summary Then
            ' Check task level fields
            tCPSScope = t.GetField(FieldNameToFieldConstant("In CPS Scope"))
            tAtCPS = t.GetField(FieldNameToFieldConstant("At CPS"))
            tTaskCat = t.GetField(FieldNameToFieldConstant("Task Category"))
            tBillCat = t.GetField(FieldNameToFieldConstant("CPS Billing Category"))

 

Here we can see that VBA has a way for us to get the value of task fields.

Assignment Fields

Reading assignment level custom field values is a bit more tricky. If the name of the custom field is one word (i.e. there are no spaces), then according to various other web sites I have looked at, for an assignment a; “a.fieldname” will do the trick. Unfortunately, if the field name does contain spaces then this method will not work.

Since I was unable to resolve the issue by working in the background I decided I had to work in the foreground (i.e. by working with a view).

To make sure that I covered all the task and could work with all the required fields I wanted to do the following:

• Use a Enterprise view
• Remove any filters
• Display all tasks

This can be achieved using the following code:

'Switch to CPS Timesheet view
ViewApplyEx Name:="CPS Timesheet", ApplyTo:=0
TableApply Name:="CPS Timesheet"
' Remove Filters
FilterApply Name:="&All Tasks"
'Expand to show all tasks
OutlineShowAllTasks

 

We can now loop through all the rows in the view. This is one area where I have used a bit of a hack. I haven’t found an easy way to count the number of used rows in the current view. However, I can count the number of tasks in the current project and I know that we rarely assign more than one person to a task, so the following should give me a high enough limit to count to.

tCount = ActiveProject.Tasks.Count
tCount = tCount * 2 ' Assume no more than 2 assignments on each task

' Loop through all rows
For r = 1 To tCount

 

The view I’m using is a Task Usage view and therefore contains the names of tasks as well as the resources assigned to those tasks. What I need to do is to differentiate between Tasks and Assignments.

Now I can use the SelectTaskField to select a specific cell in row r and column specified by a field name.

SelectTaskField Row:=r, Column:="Name", RowRelative:=False

 

SelectTaskField

A couple of important points about this command:

• Column – this should be the real name of the column as stored in Project, not the name shown in the view. Hence the use of Column:=”Name”, rather than Column:=”Task Name”
• RowRelative – the default value for this is True and will therefore select a cell r rows down from the currently active cell. Therefore, this should be set to False to ensure that it is always row r that is used.

Having selected the cell in the “Name” column of row r we can read the text in the cell using:

sTest1 = ActiveCell.Text ' Read the text in the Task Name column

Task.Name

It is also possible to query the name of the task associated with the currently active cell.

sTest2 = ActiveCell.Task.Name   ' For the current cell get the associated task name

And we can therefore compare sTest1 with sTest2. If they are the same then we are looking at a Task. However, if they are different then we are looking at a resource assigned to a task and the current row is an assignment. We can therefore pick up cell values from other columns in the row and compare them to expected values.

Final Outline

So, the final outline of the code looks like this:

Sub CheckAssignmentsBlogEx()
Dim r As Long    ' Row counter
Dim sTest1 As String ' String to test - taken from Name column
Dim sTest2 As String ' String taken from task name - compare to string 1
Dim iTID As Integer     ' Task ID
Dim FRS As String   ' Report String

' Project Server Custom Fields to test as Assignment level
Dim TBC As String, Billable As String, REx As String, RCR As String
Dim tCount As Integer   ' Count the number of tasks in the project

tCount = ActiveProject.Tasks.Count
tCount = tCount * 2 ' Assume no more than 2 assignments on each task

' Loop through all rows
For r = 1 To tCount
    SelectTaskField Row:=r, Column:="Name", RowRelative:=False
    ' RowRelative is false to ensure the code selects row number r, not r rows below the current one
    sTest1 = ActiveCell.Text ' Read the text in the Task Name column
    
    If sTest1 <> "" Then ' Only proceed if the cell contains some text (i.e. is not blank)
        ' Get the task ID for the selected cell - used for comparing assignments to tasks and creating final report
        iTID = ActiveCell.Task.ID
        If Not ActiveProject.Tasks(iTID).Summary Then   ' Check that the current task is not a summary task
            sTest2 = ActiveCell.Task.Name               ' For the current cell get the associated task name
            If sTest2 = sTest1 Then
                ' the task name is the same as value in the name column. Therefore,
                ' this is not an assignment
                    ' Do what we need to do with task level information
            Else
                ' the two strings are different and therefore,
                ' this is an assignment
                    ' Do what we need to do with assignment level information
            End If
        End If
    End If
Next r


End Sub