Main

Examples Archives

May 9, 2005

Project Examples Act I

You probably arrived here because you are looking for some Microsoft Project examples. You are in the right place, but you are looking for the wrong thing. It is the right place because I have some ideas which may help you to develop a solid project plan. You are looking for the wrong thing because each project is at least partly unique and so are the people who are involved in those projects.

Project Scheduling and the Bard

Shakespeare would be disgusted to hear that I could compare a project schedule to something like King Lear. And indeed there are a number of projects which end up looking more like Waiting for Godot. It is unlikely that your schedule has the intrigue or audience appeal (unless perhaps it is Steve Batt's infamous "Purple Schedule") But the basic idea is actually rather similar.

The first task is in developing the plot. In a project schedule getting to the finished project is usually sufficient to warrant some applause, unless it has taken so long that the audience has gone home. The point here is that you need to be very clear about what it is you are undertaking and what you hope to achieve before you start working out the schedule. Defining the scope of the project is what you are after here. It must be clear to everyone involved what that goal is. The schedule you are going to put together is not going to help much if people have different interpretations about what the work is. I'd suggest that each of the major milestones along the way are also documented and more importantly discussed so that they are clear and well understood. You can modify them along the way if you need to, but unless you have the key team members in agreement and understanding about them, you will truly get nowhere.

Five Acts

Once the goals of the project are set, we can take another lesson from the stage. It is rare, and painful to sit through a single act which stretches over a couple of hours. Attention is stretched to a breaking point and it becomes difficult to understand the entire thing. So, since the time of the Greeks tragedies have been structured with 5 acts. There is no inherent limit to the phases in a project, but it is useful to have at least one major milestone periodically so that progress can be measured and more importantly as a near term goal for the people working on the project. And when they meet that goal it is time to call out the chorus and celebrate a bit.

So what sort of plan would you have at this point if you entered it into project? Actually a quite sparse one. Just a handful of milestones, but doing this work before you set into building the schedule will help you avoid problems later. The schedule is more than just a tool to account for the time spent on the project. It is a tool for communicating the plan to people who are executing the plan and to the people who are responsible for managing it. It must be clear and logical.

One of the benefits of breaking it down into phases is that working out the details of each phase is an easier task than doing the whole thing at once. Additionally, you can use a technique called "rolling wave planning" in which the near term activities are highly detailed while those later in the project (and which you may have much less visibility into) can be less detailed. As you near the end of a phase, the schedule for the upcoming phase is refined further as you gain higher certainty about it.

Plagarism

Now that you have the phases mapped out (including what you are actually going to produce or accomplish in each phase) it would seem like an example project would be helpful. You could just paste in a generic flow that you copied from somewhere. And of course you could. But there are a number of reasons not to do this. The first is that except for highly repetitive projects, there are usually some peculiarities about certain elements of the work, or site conditions that need to be taken into account, or people or materials which are not completely interchangeable. Furthermore, if you are working off of an example from somewhere you have no idea of what sorts of assumptions about productivity or dependencies were made by the original author. This is why I say that example projects are largely useless. On top of that, there is a level of understanding of the work that comes from putting the plan together. This building of a mental model and testing your assumptions gives YOU an understanding of the project which is invaluable. In years of scheduling, I have seen many large schedules being transferred from one person to another over the life of the project. In almost every case, when the schedule changes hands it goes through major remodeling as the new owner remakes it in a way which makes sense to them.

That being said, there is no reason to throw out sections of plans from previous projects.

I'll cover the rest of this analogy in later installments. Meanwhile there are refreshments in the lobby.

May 11, 2005

MS Project Examples

Added a new example covering grouping and conditional formatting in Project to my website. From my log it seems that examples are a popular topic.

June 8, 2005

Project Examples Intermission

Previously I noted that examples are useless because projects and project teams are unique. This is only half true. There are things which can be learned/plagarized from existing projects. To this end Microsoft kindly provides a number of built-in templates for a number of different types of projects. Project 2002 offers the following list and I believe 2003 expands on this list.


  • Commercial Construction template
  • Engineering template
  • Home Move template
  • Infrastructure Deployment template
  • Microsoft Active Directory Deployment template
  • Microsoft Exchange 2000 Deployment template
  • Microsoft Office XP Corporate Deployment
  • Microsoft SharePoint Portal Server Deployment
  • Microsoft Windows XP Deployment template
  • MSF Application Development template
  • New Business template
  • New Product template
  • Office Move template
  • Project Office template
  • Residential Construction template
  • Software Development template
  • Software Localization template
  • A brief look at the templates for the work I'm familiar with shows them to be rather basic and they are certainly missing some things I consider important, however for general format, for how resources should be assigned, and for how tasks can be linked they are decent examples to copy or start from.

    You can find the templates by going to the "file" menu, choosing "new" and then selecting the new from template option which you prefer. You can also find the templates on the web at http://office.microsoft.com/en-us/templates/CT011359421033.aspx.

    I'd say they are definitely worth checking out if you are just learning project and want to see what your project schedule should end up looking like. I'd also check out the deployment templates as those are something that Microsoft does have some expertise in.

    July 1, 2005

    Peanut Butter Sandwich Scheduling

    Recently I questioned if it is possible to make scheduling tools and processes "friendly" so they would not be resisted. Today I'm going to show one easy way to do this using MS Project and Excel.

    The analogy here is that the technique I'm going to show is about as simple as you can get. As far as project management utility is concerned it is the equivalent of the peanut butter sandwich. Filling and somewhat nutritious but most importantly easy to fix and acceptable by a large number of finicky eaters. You can draw conclusions about how project server is a daily dose of brussels sprouts if you desire, but I didn't say that.

    Anyway the basic principle is this. The easiest way to tell when you are going to get somewhere is to measure how far from the finish line you are. Measuring how far from the starting line is less useful because you might not be heading directly towards the finish. You may have just gone in a circle for the past month. So the best indicator of when you are going to finish is based on the remaining tasks. In this case we are going to go the whitebread route and even ignore work (The HORROR!). You can add it quite simply if you like.

    Now, a nutritionist will insist that you measure calories and weigh your portions, but in this case we can figure that out later. It is trivial to determine this to the first order by counting the number of people working on the project and then multiplying how long the project is. We don't need to spend a lot of time doing this when we are just trying to run out the door.

    So what is this technique? Here are the basic steps.

  • Build a schedule model. Do all the right things here - limit constraints, use dependencies to sequence activities. Get the durations right and keep the durations to a week or three.
  • Save a baseline. This is your starting point, your map so to speak. There is an additional step here I'll explain later.
  • Work for a week.
  • Adjust the Remaining Duration on all of the tasks to match your current projections. If the task is done then set remaining duration to 0. Start with in progress tasks and then selectively check any forcast tasks that might need adjusting based on what you have learned doing the week.
  • Set the status date to the appropriate reporting date.
  • Click the magic "Update Project" button. DID HE REALLY SAY THAT?!!
  • Export current and new forecast data to Excel using a macro. The previous two steps can be combined into this as a single button click.
  • Work some more and repeat.

    Now it can't be that easy. Didn't I just cheat? How does this tell me how my project is doing? The answer is that well, it doesn't. This part is just the bread. You need to add peanut butter. Let's let Excel=peanut butter and use a bit of VBA as the knife.

    First the VBA. What we want to do with the VBA is to get certain data out of the project so we can see it in excel. You can do all these steps manually, but it is a bit repetitive and so a macro is easier. I'm keeping it as simple as possible. Here is the code. Lines beginning with an apostrophe are comments and are not executed.

    Sub burnDown()
    Dim t As Task
    Dim remDur As Long
    Dim stat As Date
    Dim xlRow As Excel.Range

    Set xlApp = New Excel.Application
    xlApp.Visible = True
    AppActivate "Microsoft Excel"

    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets.Add
    xlSheet.Name = "foo"
    Set xlRow = xlApp.ActiveCell

    remDur = 0
    stat = ActiveProject.StatusDate
    With ActiveProject
    numweeks = (.ProjectFinish - .ProjectStart) \ 7 + 1
    End With
    For i = 0 To numweeks
    UpdateProject All:=True, UpdateDate:=stat, Action:=1
    For Each t In ActiveProject.Tasks
    If Not t Is Nothing Then
    If Not t.summary Then
    If t.Flag1 Then
    remDur = remDur + t.RemainingDuration
    End If
    End If
    End If
    Next t
    remDur = remDur / (ActiveProject.HoursPerWeek * 60)
    xlRow.Value = stat
    Set xlRow = xlRow.Offset(0, 1)
    xlRow.Value = remDur
    Set xlRow = xlRow.Offset(1, -1)
    stat = stat + 7
    Next i
    End Sub

    What this does is export the remaining duration to excel. It steps through the project week by week and recalculates what would be remaining if all went well. Then when you have this data in hand you paste it into excel in the proper place for your chart. Let's move onto the chart now.
    burndownchart.jpg

    The chart has three data series which are interesting. The first is the baseline. This is your plan. We are always measuring against the plan. Note that the baseline remaining work goes to zero at a certain point in time. When there is no remaining work you are done. The intersection between baseline and the x axis is your finish date. This baseline gives you some important feedback. What shape is it? If is is fairly flat that indicates that you expect to make steady progress. If there is a steep drop somewhere can you explain how the people you have working on this are going to accomplish that? Is it the result of multiple tasks going on at the same time? If so, how are you going to get that all done in a week? Are there appropriate flat spots where holidays or vacations are going to interrupt work? This is a first check on your schedule model. If you take the next step and use work rather than simple remaining duration you will get even better visibility. If the curve of the baseline is acceptable and understandable then go on. If it looks wierd, then check your model.

    The next important series is what I call the "Actual" remaining duration. It is the remaining duration at the date of the report. Each week you add another data point to this series. This is important because it tells you how you actually performed. By looking at the difference between remaining duration from last week and the current week you can see how much closer you have gotten to your goal. Sometimes you may have MORE remaining duration. This can happen if tasks are added or re-estimated. Now you will say that people will always say that a task is on track until the end, at which time they say that it will be done "next week". You have a point there. However, the tasks in your model should be a week to three long, so in the worst case it will just be a couple of weeks before you see remaining duration go flat. In the best case you see it the same week you are reporting. It can't hide for very long. At this point eyeball the chart. How far are you from baseline? If the two are diverging is the gap getting wider? If so, draw a line connecting the "Actual" remaining duration datapoints that you have. Extend it down and to the right until it hits the x axis. That is about when you will finish if you don't fix things. If it never hits the x axis going forward, you should probably call for help.

    The third series on the chart is one which the macro derives for you. It shows your current forecast of remaining duration. If your "Actual" remaining duration is trending later then baseline then this series is probably your best case prediction. If you are under baseline, then you can reasonable expect to do a bit better than this (barring the case in which you have been ditching tasks each week) The important thing to do here is to make sure it is not unrealistic. Compare it to the baseline and eyeball it against your "Actual" line. If it is similar then you can feel confident. If it doesn't have a family resemblence to the baseline or your "Actual" line and if it drops like a rock just before the target finish date you are lying to yourself. It is too good to be true.

    How does it match up with that line you extrapolated from the "Actual" data? Some variation is OK. The best estimate for when you finish is likely to be somewhere between where the two lines intersect the x axis, the point where no work remains to be done.

    So you can see, this technique quickly and easily projects a finish. Project itself gives you a single projected finish date without going through the additional steps of exporting and charting the data, but the value here is that the series on the chart show you whether your plan is realistic. They show you your weekly progress in a very simple form. They give you a trend which can be used to make a projection. They give you a forecast which you can compare in a glance with both your original plan and your actual progress. For the amount of time spent, this is the most efficient schedule forecast method I can think of.

    Now once you have graduated from soft foods and want to try something more exotic it is simple to extend this indicator. Assign resources to the tasks and track work rather than duration. This gives an incremental improvement in the accuracy. Primarily it has the effect of straightening out the lines. Want a deeper look into the workings of the project? Use an outline code or text field to categorize specific tasks in your schedule and extend the code to produce a series for each of the categories of tasks. Want a bit more detail in the historical data? Instead of just updating the remaining work, update the actual work too. This are incremental steps you can take when you and your team are ready. Steps you can take if you find that additional data to be useful. Sometimes a peanut butter sandwich is all you need though.

  • August 2, 2005

    Working with Task and Assignment Fields VBA

    One common problem people face with project is that there are three classes of custom fields; task fields, assignment fields and resource fields. If you are in a resource view and you are looking at the Text1 field it won't have the same information as if you are looking at the Text1 field in a task view. This is true with reports as well. The solution is to copy over the items from the one field to the other. This is painful unless you automate it. So, to reduce the pain here is VBA code which does it for you:

    Sub CopyTaskFieldToAssignment()
    'This macro copies information in the task text5 field
    'into the assignment text5 field so that is can
    'be displayed in a usage view or in a report.
    'Modify the line noted below to fit your needs
    Dim t As Task
    Dim ts As Tasks
    Dim a As Assignment
    Set ts = ActiveProject.Tasks
    For Each t In ts
    If Not t Is Nothing Then
    For Each a In t.Assignments
    'change the following line to use
    'for a different custom field
    a.Text5 = t.Text5
    Next a
    End If
    Next t
    End Sub

    Pretty easy. This one should have no problems because each assignment only has a single task that it references. However, going the other way could be a problem as each task can have several assignments. To sidestep the issue we can simply concatenate all of the text from all of the assignments. The code would then look like this:

    Sub CopyAssignmentFieldToTask()
    Dim t As Task
    Dim ts As Tasks
    Dim a As Assignment
    Set ts = ActiveProject.Tasks
    For Each t In ts
    If Not t Is Nothing Then
    t.Text5 = ""
    For Each a In t.Assignments
    'change the following line to use
    'for a different custom field
    t.Text5 = t.Text5 & ", " & a.Text5
    Next a
    End If
    Next t
    End Sub

    The line t.Text5 = t.Text5 & ", " & a.Text5 appends whatever is in the assignment field to whatever is already existing in the task field.

    Some simple modifications can make it work to copy from the resource fields.

    August 16, 2005

    MS Project Tip - Working with Float

    In Microsoft Project everything is scheduled "As Soon as Possible" by default. This is generally a good thing, though it is a bit misleading at times. It is very common for people to look at the schedule and see that something did not start on the "start" date and get concerned. Fortunately there is an easy way to tell whether a task starting late is a problem or not.

    The way to do this is to look at the Total Slack (also referred to as Total Float) for the task. This value shows you the amount of time that a task can move later in time without affecting the finish of the project. Project calculates this automatically for you by performing a forward pass calculation - adding up the durations of tasks going forward to determine how early they can finish, and then a backward pass calculation - the same thing but from the project finish date which was calculated in the forward pass. As a result of these calculations you get an early finish date and a late finish date for every task. The difference between the two is the total slack or float.

    The problem with slack is that it is not always visible in the gantt chart. To make it visible is simple. First go to the "Format" menu and select "Barstyles. A dialog box comes up. Add two new barstyles as shown here:

    barstyles.jpg
    You note that I have two barstyles added and that they both use "finish" and "late finish" except in the opposite order. The reason for this is that Project draws all bars from left to right. The first bar will only be drawn if finish is earlier than late finish. The second bar will only be drawn if late finish is earlier than finish. This is a useful technique to remember for drawing other sorts of bars where you are comparing against another date (such as baseline, deadline etc.)

    The result of adding these barstyles is shown in the next diagram. You can see for the first group of tasks that Task D has total slack of one week. So if you are in the second week of the project and contemplating whether Task B or Task D is more important to get started on, the answer should be clear that it is Task B.

    The Neg Float bar comes into play when your tasks are already too late. In this case I set a "Must Finish On" constraint on the Finish milestone. In effect this makes Tasks A, B & C late by one day. You can see the red bar show up indicating that they are 1 day late. Task D is still showing 4 days of total slack.
    floatexample.jpg
    By making this information visible on the Gantt chart it is much easier to see what tasks are the ones which will affect your project completion and which tasks are less critical.

    August 29, 2005

    Writing Project VBA macros using the Macro Recorder

    One of the easiest ways to learn how to use Microsoft Project VBA is to use the macro recorder. It does not always produce reusable output, but it does output the correct syntax and it identifies the objects, properties and methods which are involved in what you want to do. Let's work through a simple example like zooming the timescale to show only the selected tasks.

    Start with turning on the macro recorder by going to the tools menu / select "macros" / select "record new macro". Give it a title and note where it is going to be saved.

    Now select some tasks in your project. Then go to the "view" menu / select "zoom" / select "selected tasks" and click OK. Now we turn off the macro recorder either by going back to the tools menu and choosing "stop recorder".

    Now you can look at the code. It should look something like this:

    Sub Macro1()
    ' Macro Macro1
    ' Macro Recorded 8/29/05 by yourusername.
    SelectRow Row:=-6, Height:=2
    ZoomTimescale Selection:=True
    End Sub

    This code is OK, but it is not reusable because each time you run it, it will select two rows which are 6 rows above where ever your cursor is. Chances are you don't want that. So we edit it and remove that row.

    Sub Macro1()
    ZoomTimescale Selection:=True
    End Sub

    This code works fine, IF you have a valid selection. Try running it on a blank row and you get an error. So we need to make one more modification to it.

    Sub Macro1()
    If Not ActiveSelection = 0 Then
    ZoomTimescale Selection:=True
    End If
    End Sub

    Now if we have a task or tasks selected this code will zoom the view to show the entire duration of the longest task. An obvious next step is to assign this to a toolbar button so you can zoom the selection with a single click.

    A more complicated example is exporting a file to excel. I can never remember the exact syntax off the top of my head, but turning on the macro recorder and exporting makes it easy. Here is the code I get while creating a map and saving a file. Note: for formatting reasons I've added several line continuation characters "_" so that the long lines will fit on the screen correctly.


    Sub Macro2()
    MapEdit Name:="Map 1", Create:=True, _
    OverwriteExisting:=True, _
    DataCategory:=0, _
    CategoryEnabled:=True, _
    TableName:="Task_Table1", _
    FieldName:="Name", ExternalFieldName:="Name", _
    ExportFilter:="Critical", _
    ImportMethod:=0, _
    HeaderRow:=True, _
    AssignmentData:=False, _
    TextDelimiter:=Chr$(9), _
    TextFileOrigin:=0, _
    UseHtmlTemplate:=False, _
    TemplateFile:="C:\...\Centered Mist Dark.html", _
    IncludeImage:=False
    MapEdit Name:="Map 1", _
    DataCategory:=0, FieldName:="Finish", _
    ExternalFieldName:="Finish_Date"
    MapEdit Name:="Map 1", DataCategory:=0, _
    FieldName:="% Complete", _
    ExternalFieldName:="Percent_Complete"
    MapEdit Name:="Map 1", DataCategory:=0, _
    FieldName:="Resource Names", ExternalFieldName:="Resource_Names"
    FileSaveAs Name:="C:\foo.xls", FormatID:="MSProject.XLS5", _
    map:="Map 1"
    End Sub

    You can see that the macro recorder makes this a lot easier than typing this in from scratch.

    October 24, 2005

    Telling Time - ProjDateDiff, VB DateDiff and Application.DateDifference

    Date subtraction in VB, Project VBA and Project custom field formulas is one of the more common activities. Unfortunately there are a number of slightly different functions available. This article briefly describes the main three.

    It all starts with the VB DateDiff function.
    The syntax is as follows:
    DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
    interval is required and is the time unit you want the result returned in:

  • yyyy = Year
  • q = Quarter
  • m = Month
  • y Day of year
  • d = Day
  • w = Weekday
  • ww = Week
  • h = Hour
  • n = Minute
  • s = Second

  • date1 and date2 are required and are the two dates you are working with.
    firstdayofweek and firstdayofyear are optional and will change the defaults from Sunday and the week which contains January 1 to whatever else you might choose.

    Now this is a pretty powerful and useful function, but when you are calculating schedule dates it becomes a problem. The issue is easily illustrated with the example of a weekend. Suppose you want the working hours for a task between now and two working days from now. DateDiff would work fine if both days are in the same workweek, but once the interval spans a weekend then the calculation is wrong. To resolve this, Project VBA has the DateDifference function. DateDifference is considerably simpler. Here is the syntax:
    Application.DateDifference(StartDate, FinishDate, Calendar)

    StartDate and FinishDate are required. They are the start and finish dates used.

    Calendar is optional. It can be a resource or task base calendar object. The default value is the calendar of the active project.

    The result of this function is the duration in minutes. You can convert the minutes into days by dividing by 480 (for an 8 hour day or more accurately dividing by 60 * HoursPerDay. HoursPerDay is a Project property which reflects the current definition of workdays in Project. You can also divide by the HoursPerWeek and DaysPerMonth functions if you want to use longer timescales.
    Application.DateDifference is what you would use in VBA code.


    In Project custom field formulas the situation is almost exactly the same. However instead of being called DateDifference, they named the function ProjDateDiff. The arguments are the same:
    ProjDateDiff( date1, date2, calendar )
    and the result is also returned in minutes. Converting this into usable time periods IS different though. Custom formulas offer the [Minutes Per Day] and <[Minutes Per Week] constants to do conversion to days and weeks. There is no month conversion available.

    Here are a couple of examples:
    Project VBA DateDifference example:
    Sub projectduration()
    MsgBox CStr(Application.DateDifference(ActiveProject.Start, ActiveProject.Finish, standard))
    End Sub

    Custom Field Formula Example:
    ProjDateDiff([Project Start],[Project Finish],"standard")
    Note that the custom field formula requires quotation marks around the calendar name and the VBA example does not.

    November 8, 2005

    Working With Microsoft Project Events - On Open

    Working With Events

    Often one wants Project to do something when something changes in the project file. An example of this is having some sort of macro run when the project is opened or when it is saved. Project offers a number of Project events which allow this. They include:

    • Project_Open (which acts like the On_Open event you may be familiar with),
    • Activate,
    • BeforeClose,
    • BeforeSave,
    • Calculate,
    • Change,
    • Deactivate

    These events apply to the project and are fairly simple to implement. There are also a number of application level events which allow you to specify actions based on changes in individual fields. They are somewhat more difficult to implement, but if you can follow the example below you will have no problems.

    The list of application events includes:
    • ProjectAfterSave, ProjectAssignmentNew,
    • ProjectBeforeAssignmentChange, ProjectBeforeAssignmentChange2,
    • ProjectBeforeAssignmentDelete, ProjectBeforeAssignmentDelete2,
    • ProjectBeforeAssignmentNew, ProjectBeforeAssignmentNew2,
    • ProjectBeforeClearBaseline,
    • ProjectBeforeClose, ProjectBeforeClose2,
    • ProjectBeforePrint, ProjectBeforePrint2,
    • ProjectBeforeResourceChange, ProjectBeforeResourceChange2,
    • ProjectBeforeResourceDelete, ProjectBeforeResourceDelete2,
    • ProjectBeforeResourceNew, ProjectBeforeResourceNew2,
    • ProjectBeforeSave, ProjectBeforeSave2,
    • ProjectBeforeSaveBaseline,
    • ProjectBeforeTaskChange, ProjectBeforeTaskChange2,
    • ProjectBeforeTaskDelete, ProjectBeforeTaskDelete2,
    • ProjectBeforeTaskNew, ProjectBeforeTaskNew2,
    • ProjectCalculate,
    • ProjectResourceNew,
    • ProjectTaskNew,
    • NewProject,
    • LoadWebPage,
    • ApplicationBeforeClose.

    Using a Project Event


    This is the simplest form of event.

    Open your project file. Hit the ALT+F11 keys to open the visual basic editor. In the upper left you will see a window with a typical windows tree view. Click on the + signs until the project it expanded and you see the "ThisProject" object. It should look like the diagram on the right, although with a different project name.
    Double-clicking on that item will bring up a code window. In that window you can paste the following:

    Private Sub Project_Open(ByVal pj As Project)
    MsgBox "Project Just Opened"
    End Sub

    You can replace the MsgBox code with whatever you want to happen when you open the project. For example it could call a macro which you have already written.  Similar macros can be written to take action before printing or saving. For example you may want to copy certain data into a custom field before saving the file so that you can restore it later if necessary.



    Application Events:

    The example above only requires pasting some code in a single place. However using application events requires a few more steps. The first step is to create a new class module and declare an object of type Application with events.
    Creating the class module is done by going to the insert menu and selecting "ClassModule" as shown here:

    When you have done this, double click on the class module and declare the object by using the following code:

    Public WithEvents App As Application

    After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

    Writing the procedure is similar to writing any macro. The image below shows a simple example using the ProjectBeforeTaskChange event.

    Note that NewVal holds the value that the user has input. The original value can still be referenced in the standard way (t.Name). The code to cut and paste is shown next.

    Public WithEvents App As Application

    Private Sub App_ProjectBeforeTaskChange(ByVal t As Task, _
    ByVal Field As PjField, _
    ByVal NewVal As Variant, _
    Cancel As Boolean)

    If Field = pjTaskName And NewVal = "foo" Then
    MsgBox ("you can not change the name to foo")
    MsgBox ("The old name was " & t.Name)
    Cancel = True
    End If

    Note that a space followed with an underscore is used to break a single line of code. This is called a line continuation and I use it to keep code readable when there is a long line. Now that you have written the code, there is one final step to undertake before using it. Before the procedures will run you must connect the declared object (in this case the one we called "App") in the class module with the Application object.
    It sounds complicated, but it is really rather simple. First we declare a new object based on the class module. In this case our class module is named TEvent.
    Code to do this would be something like:

    Dim X As New TEvent

    Now that we have this object we need to initialize it. Basically we need to tell it what the "App" is.

    We do this with the following code:
    Set X.App = Application

    After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Project Application object, and the event procedures in the class module will run when the events occur.
    Most of the time you will want to do the initalization when your project opens so the events will work from the start, but you can put this information in any typical module which holds some of your macros.
    The screenshot below shows what it would look like if we want it to initialize when the project file opens.

    This example shows how events can be in a specific project file. You can also have this code in the global.mpt file so that things occur whenever you open Project as an application. The Project_Open event is also useful to distribute macros or other standard formatting. For example, you could have a Project_Open macro which sets up an environment for you (copying views tables etc.) using the organizer. When a user opens the file, those items could be copied into their global.mpt file. (Note: You might notice this is familiar. That is because I'm porting the material from my old Microsoft Project Macros website to this one bit by bit so that everything can be found in one place)

    December 5, 2005

    MS Project Tip - Creating User-defined Filters

    One of the most common activities with large schedule files is filtering them down to show the most important tasks. In most cases, important means immediate and many industries (the construction industry for example) a 3-week look ahead schedule showing current and upcoming activities is very common.

    Project's built-in filters allow you to filter based on a date range, but the date range filter asks for user input and you need to select the time periods. There is no way to easily automate this in VBA short of recreating and applying the filter, but in the more recent versions of project there is a very simple way to do this by using a custom field formula.

    I've described how to set up custom field formulas before. (Please click the link and read about them if you are new to the topic). For filtering purposes, the simplest way is to use a Flag field. Flag fields have a value of either "Yes" or "No". You can set them manually, but here we are going to use a formula. The formula I am showing performs a few simple tests. First it checks for all tasks that begin before a date three weeks in the future. Then it removes any which are already complete. Of these tasks it selects all of those which are not finished yet. If the task meets those criteria, the field is marked "Yes". If it doesn't then the field remains as "No". Here is the formula:

    IIf(([Start]<(Now()+21) And ([% Complete]<100)) And ([Finish]>Now()),"Yes","No")

    If you paste this formula into a flag field (for example Flag1) then all you need to do is create a filter which shows any tasks for which Flag1 is "Yes". Note that the formula as written doesn't show any incomplete tasks which are supposed to be completed in the past. Theoretically if you have an updated schedule you won't have any tasks like that, but you can modify the logic to add that condition. Using this technique you should be able to get around almost any interactive filtering challenge.

    May 2, 2006

    Microsoft Project 2007 Example Project Templates

    Lidiane's Microsoft Project 2007 Blog announces some of the new templates to be included in Proj 2007. From the titles you can see that the targets are getting deeper into HR, Marketing, IT and Legal areas. I hope these templates are more useful than the ones in earlier versions. My opinion is that they are so basic that they might almost be dangerous - dangerous in the sense that an inexperienced user might mistakenly follow them line for line. But, I'll not talk anymore about that. I'm an inexperienced users with many other software tools. Here is the list so far announced:

    • Annual Report Preparation
    • Develop Skills Needs - Hiring Plan - Hiring Forecast
    • Finance and Accounting System Implementation
    • Human Resources Information System Implementation
    • Marketing Campaign Planning
    • Marketing Event Planning and Execution
    • Performance Reviews
    • Request For Quotes (RFQs)
    • Vendor Request For Product Development Planning
    • Product Evaluation Post Launch
    • Six Sigma DMAIC Cycle
    • SOX Compliance and Technology Options
    • Strategic Merger or Acquisition Evaluation
    • Vendor Evaluation and Consolidation
    • Customer Feedback Monitoring
    • Financial Service Offering Launch
    • Internal Readiness Training
    • ISO 9001 Management Review
    • Managing Incoming Proposal (RFP) Solicitation

    October 26, 2006

    Project Scheduling with Excel

    For the "I Hate Microsoft Project" crowd some new hope glittered today with a couple of posts on making Project schedules and charts in Excel. The secret is using an xy chart with error bars (my version uses formulas and conditional formatting and can do resource profiling but doesn't look nearly as clean. The post and example files are at Process Trends

    Then Dick Kusleika chimed in and added progress lines. His example is here Daily Dose of Excel. A couple other approaches are referenced in his comments as well.

    Excel is immensely useful... but it isn't a substitute for a real scheduling tool. Sorry Project haters.

    January 29, 2009

    Date Math - DateAdd, ProjDateAdd in Microsoft Project

    I just ran into someone having trouble making a custom formula in Microsoft Project. They were adding a duration to a date by using a + sign. For example:

    [Finish1]+[Duration1]

    The result they got was years in the future and was unexpected. The reason for this is that Duration is stored in minutes. The duration of an 8 hour day is 480. Dates, like Finish1, are stored as a serial number which is counting from 1/1/1900, making today 39842. When you add those two numbers together, each minute is treated as a day.

    Fortunately MS Project has some functions which will do the date math for you. DateAdd will do simple date addition, and ProjDateAdd will do date addition with any of the Project calendars taken into account. If you have a duration of 5 days stored in the Duration1 field and today is Thursday, then the different functions would give the following results:

    Now()+[Duration1] would give a date about 7 years from now.

    DateAdd( "d", 5, now()) will give a date five days from today (Tuesday).

    ProjDateAdd(Now(),[Duration1],"Standard") would give a date one work week from today. (Thursday)

    About Examples

    This page contains an archive of all entries posted to Project in the Examples category. They are listed from oldest to newest.

    Microsoft Project 2010 is the next category.

    Many more can be found on the main index page or by looking through the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34