Project VBA - using find and select to navigate project views and change colours

For anyone who is used to the power of using VBA to manipulate the look of an Excel sheet then you are going to be a little frustrated with what you can do with VBA in Project.  You cannot for example uses the Task or Resource objects for finding the cell in a view and manipulating that cell to change its colour.  If you want to change the colour of a cell then the only way is to use Select and Find methods to change the ActiveCell and apply font colour changes to the Cell object.  In other words, the Cell object can only be used on the ActiveCell which will appear a bit limiting.

Using the Find method

To illustrated this, in the following example I have some external dependencies in my Project plan that all have task names with DEP: prepended on them.  I can use the Find method to find the row in the current view where the text "DEP: " is found.  To so this, I am first going to call the ViewApply method to make sure that the Gantt view is displayed.

ViewApply Name:="Gantt Chart"

Find Field:="Name", Test:="contains", Value:="DEP:"

We can then use the FontColor method to set the colour.  In this case purple.  If you want to set a specific colour rather than a standard colour you can use FontColorEx and provide an RGB value.  We can also use the Font32Ex method to change the font to bold.

ViewApply Name:="Gantt Chart"

Find Field:="Name", Test:="contains", Value:="DEP:"

ActiveCell.FontColor = pjPurple
Application.Font32Ex Bold:=True

Notice here that FontColor applies to the Cell object whereas the Font32Ex has to be called via the Application object.  You can also change the font size, style, cell background pattern and colour using Font32Ex.

The limitation of the above code is that when I have multiple dependencies in my project that I want to colour them all it is difficult to find them using the Find method.  The Find method will search from the current ActiveCell but I don't know what row number I am on and there is no way of finding it.  I also can't rely on the ID field to know the row number as that could change.  For example when I apply a filter or if I change by Gantt task view to be ordered by another field.

Currently if I looped this code the Find method will start find the same dependency each time because after it finds it the first time, it will start from that position and the next iteration will find the same line again.

Using Select methods

There are several different select methods that are available that can be used to change the ActiveCell to be a different selection.  We can move both relative to the current position or to absolute row numbers.

For example, I can use the following to move the ActiveCell down 1 row relative to where we are.

' move down one row
SelectTaskField Row:=1, Column:="Name", RowRelative:=True

When we then run the Find method again it will find and colour the next dependency.  How do we know when we have found them all?  This is not easy as the Find method will keep loop back to the top of the view and keep searching and we don't know which row number we are on.

To overcome this and complete the code, we can store the task ID of the first Dependency we find with the above code and then store it.  If we increment the line number after each find with the above select task and then put this in a loop, when we get back to our task ID we know we have been all the way through the view.

Dim firstTask As Long

firstTask = 0

ViewApply Name:="Gantt Chart"
    
Do While Find(Field:="Name", Test:="contains", Value:="DEP:")
    
    ' Record if we are the first task ane exit if we have looped
    If firstTask = 0 Then
        firstTask = ActiveCell.Task.ID
    ElseIf firstTask = ActiveCell.Task.ID Then
        Exit Sub
    End If
    
    ActiveCell.FontColor = pjPurple
    Application.Font32Ex Bold:=True
    
    ' move down one row
    SelectTaskField Row:=1, Column:="Name", RowRelative:=True
 Loop

Further Enhancements

There are a lot of different Select type functions that can be used to select a cell, a range, a column, a row or a field.  We could can modify the above code to select the whole row and then colour that rather than just the task name.

Note: when using colours in Project the RGB value is reversed from normal HTML style RGB values.  That is, red is the last byte not the first.  So lavender rgb(230,230,250) is called in Project as hexadecimal &HFAE6E6

In the below code, I uses the SelectRow method to select the whole row that the Find method found and then use the Font32Ex method on the Application object to set the text and background colour using reverse RGB hexadecimal values.

' Select all of the current row
SelectRow RowRelative:=True, Add:=True

' Colour our row purple and make the text bold
Application.Font32Ex Bold:=True, Color:=&H82004B, CellColor:=&HFAE6E6

Some limitations

This macro does the job but there are a couple of limitations.  First, the screen will flicker is the macro runs through the view and changes the colours.  There is nothing we can do about this as pointed out in the beginning, in Project you can only change the cell colours through the interface view.

Secondly, if you have summary tasks collapsed then this code will only work on the visible rows in the view.  So if you have some dependencies in this manner that are hidden under collapsed summary tasks then this code will never get to them.

Download Full File

The full file for the code below can be downloaded from here in a Microsoft Project file with the VBA Macros already setup and ready to use. (It uses a proof of work formula before opening):

MSP VBA - Part 5 - Using Select-Find-Colour in Projects

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

Handling events in VBA for Microsoft Project

VBA can be used to capture certain events within Microsoft Project.  This could be used, for example, to capture when a field has been changed in the active Project view, when a task is added or when a Project file is saved.  This can therefore be used to automatically update fields or trigger other process through a Macro when these events happen.  As such, it is quite flexible although sometimes a little clunky.

Event Handler Class

The first thing that we need to do is create a Class Module, which in my example I have called myEvents, which will contain the event handling methods.

We need to create an Application and/or Project instance variable WithEvents to capture events.  There are different event handlers available for Application or Project.  Here I have chosen a simple example using the ProjectBeforeTaskChange event handler.  This method needs to be prefixed by App_ which is the variable name for our instance of the Application object.

Option Explicit


Public WithEvents App As Application
Public WithEvents Proj As Project


Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, _
                                        ByVal Field As PjField, _
                                        ByVal NewVal As Variant, _
                                        ByRef Cancel As Boolean)    

    MsgBox ("Event: Project Task Changed")        

End Sub

ProjectBeforeTaskChange Event

I have used this event as an example as it is a good way of understanding how events work and understanding the fields that are passed to the event handler.

The PjField enumeration signifies the different fields that could be changed in the view where the task has been changed.   In other words, these are the different columns available in the standard Gantt Chart view of Microsoft Project. I have chosen the pjTaskWork field which will show as thw Work column but a full list of fields can be found here. I have then used the Task that the Event was triggered for and the NewVal field to work out what the field has been changed to.

If Field = pjTaskWork Then

    MsgBox ("Work changed for task : " + tsk.Name + _
            " to " + CStr(NewVal) + _
            " from " + CStr(tsk.Work / 60))

End If

Notice the event only triggers for the field when it is directly changed.  If you change another field that causes the Work field to be recalculated the event is not triggered.

The fourth variable passed to the Event handler is Cancel.  If this is set to True and is passed ByRef then the returning True value will cause the change to stop.  In this case, the user will be returned to the field which he is editing with the cursor still blinking for the edit to be accepted.

If Val(NewVal) = 0 Then

    MsgBox ("Can't let you change work to zero")
    Cancel = True

End If

Other Event Handlers

There are multiple event handlers that could be used.  The two pages below in the main VBA reference provide a full list

• Application Events
• Project Events

For completeness, there is a simple example using an Event on the Project object.  This Event fires when the calculate project event happens which may be after a task change or depending on your settings when you manually trigger it.  Either way, it might depending on your setup be a good event to trigger your code from.

Private Sub App_ProjectCalculate(ByVal p As Project)

    MsgBox ("Calcualate project: " + p.Name)

End Sub

Download Full File

The full file for the code below can be downloaded from here in a Microsoft Project file with the VBA Macros already setup and ready to use. (It uses a proof of work formula before opening):

MSP VBA - Part 4- Using Events in Projects

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

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

Using VBA with Microsoft Project to access projects and tasks

Introduction

If you are using another Office application like Excel to manipulate Microsoft Project then you will need to first create an application.  Please follow my first  blog post to learn how to create an Application Object and open a Project file.

Part 1: Opening and Using Microsoft Project from Excel

To access projects tasks we need to have the right Project object.  We can get this from two methods.

Project Object

First, we can loop through all the projects that are open against the Microsoft Project application.

For Each prj In App.Projects
  Debug.Print "Project Name: " + prj.Name
Next prj

Alternatively, if we are running our VBA Macro from inside the Project application we may want to automatically select the active project.  To do this, we can use the ActiveProject variable that always points to the  current project which is active withing Microsoft Project.

 Debug.Print "Project Name: " + ActiveProject.name

Project Attributes

There are a lot of properties and methods that can be accessed through the Project object.  There are a few common ones that will be really useful in your scripts that are listed below but for a full set you will need to look at the Project VBA Reference material online.

' The FullName is the path name and file name of the project file
Debug.Print "Project Full Name: " + ActiveProject.FullName
Debug.Print "Project No Tasks: " + CStr(ActiveProject.NumberOfTasks)
Debug.Print "Project Start: " + CStr(ActiveProject.ProjectStart)

Tasks

One of the properties of the Project class is a Collection of Task objects.  We can loop through this collection to access all the tasks in the MSP Project Plan.

Dim t As Task

For Each t In ActiveProject.Tasks
    If Not t Is Nothing Then
        Debug.Print "Task ID: " + CStr(t.ID)
        Debug.Print "Task: " + t.Name
        Debug.Print "Duration: " + CStr(t.Duration)
        Debug.Print "Start Date: " + CStr(t.Start)
        Debug.Print "Finish Date: " + CStr(t.Finish)
        Debug.Print "Work: " + CStr(t.Work)
    End If
Next t

Above I have included again a subset of the many Task properties that you may wish to use in your code.  You can also write to these properties, for example, to update a task name through your code.

Resources and Assignments

When you deal with tasks within Microsoft Project, there are different tables of information that Project uses in the background to resource the task.  The Resource object holds information about the resource (you can view on the Resource Sheet view of MSP) and the Assignment object deals with the assignment of a Resource to a Task to do some work.

The Task object has a Resources collection of Resource objects which you can use to list out the resources assigned to the task.

Dim r As Resource

For Each r In t.Resources
   Debug.Print "Resource: " + r.Name
Next r

In my next blog post on VBA with Projects, I will explore in more detail Assignments and how to work with them.

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 2 - Using projects and tasks in 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

Get paid Monero per link click

Love this functionality from Coin Hive to provision a proof of work miner for accessing a link.  Get a few crypto rather than loads of ads on your site.

I think that anything that removes ads which are clearly broken on the Internet with some other means of site owners generating money - must be a good thing.

Also check their Captcha type catch for proof or work during login.  See more at https://coinhive.com/