Sunday 15 April 2018

Project VBA - Storing values between re-openings of Microsoft Project

Some times it may be useful to store variable values so that when you next open Microsoft Project you can carry on from where you left off.  There are a few ways that this could be done but one of the easiest and most effective ways is to use document properties.

Reading Custom Document Properties

By default, Microsoft Project will already be storing some values in Document Properties that can be read by accessing the CustomerDocumentProperties collection.  For example, MSP stores "Scheduled Start" and "Scheduled Finish" so that it can be read by other applications e.g. SharePoint.

We can loop through these like any collection as shown below.


Dim docProps As Office.DocumentProperties
Dim docProp As Office.DocumentProperty

Set docProps = ActiveProject.CustomDocumentProperties

For Each docProp In docProps
    Debug.Print docProp.Name + ": " + CStr(docProp.Value)
Next docProp


Storing a Custom Document Property

We can store a new property by using the Add method on CustomDocumentProperty object.  We can define different types for the value that we want to store.  The different types can be used by passing the Type parameter to the Add method where the different types are from MsoDocProperties enumeration.  This enumeration values are listed below and these are common across the different Office applications (you can see from the Dim line above that these are standard Office objects).


Name Value Description
msoPropertyTypeNumber 1 Float
msoPropertyTypeBoolean 2 Boolean
msoPropertyTypeDate 3 Date
msoPropertyTypeString 4 String
msoPropertyTypeFloat 5 Float


docProps.Add Name:="A Key", LinkToContent:=False, _
             Type:=msoPropertyTypeString, Value:="A value"


If you try to add a key Name that already exists then VBA will throw an error.  As such, you will need to search for your key to make sure it doesn't exist before adding again.

Full Example

The below example sets a key MY_KEY to a value that is provided in an InputBox and can be changed.  Try setting it something, saving your MPP file and then re-opening.  You will find your previous value has been saved in the custom document properties for later retrieval.


Const MY_KEY = "MY KEY"

Sub AddDocumentVariable()

    Dim docProps As Office.DocumentProperties
    Dim docProp As Office.DocumentProperty
    Dim numProps As Integer
    Dim found As Boolean
    Dim newVal As String
    found = False
    
    Set docProps = ActiveProject.CustomDocumentProperties

    numProps = docProps.Count
    
        For Each docProp In docProps
           If (docProp.Name = MY_KEY) Then
                Debug.Print MY_KEY + ": " + docProps.Item(MY_KEY)
                newVal = docProps.Item(MY_KEY)
                found = True
             Else
                Debug.Print docProp.Name + ": " + CStr(docProp.Value)
            End If
        Next docProp
    
    newVal = InputBox("Store value for key - " + MY_KEY, _
                    "Store Value in Document Properties", newVal)
    
    If found = False Then
        docProps.Add Name:=MY_KEY, LinkToContent:=False, _
                    Type:=msoPropertyTypeString, Value:=newVal
    Else
        Debug.Print MY_KEY + " exits: No of custom document properties: " _
                    & numProps
        docProps.Item(MY_KEY).Value = newVal
    End If
    

 
End Sub



Viewing Document Properties


You can view the Custom Document Properties by selecting the Advanced Properties option on the File->Info menu.


You can then see the key you have added and manipulate it, as shown below, using the .properties box that opens.



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

Saturday 31 March 2018

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

Monday 19 March 2018

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


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


Sunday 11 March 2018

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

Tuesday 6 March 2018

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/

Saturday 24 February 2018

Opening and using Microsoft Project from an Excel VBA Macro

Anyone who uses Microsoft Project regularly will know all to well that although MSP is powerful it is also sometimes a bit clumsy.  There are quite complex data structures behind MSP and can be difficult to view the data in the way that makes sense of it.  Using Microsoft Excel VBA Macros to extract data from MSP and then manipulate it in Excel is very powerful.  This article shows how to create Excel Macros to do that.

Getting Started


We need to create a Application object and the best way to do this is to use the Early Binding approach which requires that we need to select the Object Library.

Step 1: Select the References menu from Tools


Step 2: Find the Microsoft Office Project Object Library and click OK

Selecting a file to open


This bit of code allows you to select a Microsoft Project file to open.  It opens a dialog box and saves the returned file name into object strFileToOpen.  We can then test to see if a filename was selected or the box was cancelled.


    ' Open a file Dialog to find the project file
    strFileToOpen = Application.GetOpenFilename _
        (Title:="Please choose a file to open", _
        FileFilter:="Project Files *.mpp* (*.mpp*),")
        
    ' Return if no file name selected
    If strFileToOpen = False Then
        MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub
    End If

Opening the MSP File


Once we have the File name we can create an MS Project Application object and use to manipulate the Project file.  In this very simple example, we open the MS Project file and add a task called "Hello World", save the file and then close it.


    Dim App As MSProject.Application
    Set App = New MSProject.Application
    'App.Visible = True

    Debug.Print "Opening File: ", strFileToOpen
    App.FileOpen strFileToOpen
    Debug.Print "About to Add Task"
    App.ActiveProject.Tasks.Add "Hello World"
    App.FileSave
    App.FileClose
    App.Quit

I have commented out the App.Visible = True statement but you can use this to bring the MS Project application into foreground.  Without this, the code will manipulate the MSP file in the background without displaying it.


Did we Open the MSP Application


The above code is fine but at the end of it we close the MSP application.  From a usability perspective we may want to open MSP in the background and process our Macro if MSP was not already open.  However, if it was open already,  we want to select the file that we are working on and activate it.  

    weOpened = 1
    ourFile = 0
    
    For Each prj In App.Projects
        weOpened = 0
        Debug.Print "Looking through open projects: " + CStr(prj.FullName)
        If prj.FullName = strFileToOpen Then
            Debug.Print "About to Activate the App we opened"
            App.Projects(prj.Name).Activate
            ourFile = 1
        End If
    Next prj


The above bit of code iterates through the list of projects open in MSP.  If there are any open it marks it that we didn't open MSP.  If it finds the same file name from the filename dialog it calls the Activate method do make it the Project in focus.

Download Full File


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



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



Project VBA - Storing values between re-openings of Microsoft Project

Some times it may be useful to store variable values so that when you next open Microsoft Project you can carry on from where you left off. ...