Archive
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