Tag Archives: excel2003

How to show trends in MS Project

In short, you can’t ;-). MS Project is good at showing all sorts of info on your project for any given point in time. It does not, however, contain historic data to calculate trends. But there is a workaround. In each reporting cycle (say every week), save your project plan to a new file. Then use Excel and a macro to read all past project plans and chart the trend. Use pivot tables to drill down to deliverable or sub-projects. Here is how to chart work compared to baseline work over time. Continue reading

Dump actual work from MS Project into Excel

As you may know, MS Project may occasionally behave divaesque. To make matters worse, older versions (like the one, I’m using) know only one (!) undo step. In order to check if some of my changes have screwed up actual work of the past, I dump all actual work from project start to project finish into an Excel file and analyze it with a pivot table. I can now easily compare actual work before and after I’ve made changes to the project. Continue reading

How to add VBA code to your computer

First of all, all VBA examples on this website use MS Excel 2003 2010 and/or MS Project 2003 2010. Most of the stuff may run with later versions, YMMV. Also, keep an eye on formulas in Excel: you may need to replace each colon with a comma (or tweak your regional settings in Windows).

Add code to MS Excel

  • I store all my macros in a central place (personal.xls), so that I can use them in all spreadsheets.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\Excel\XLSTART
  • Read, how to add macros to your personal.xls.
  • Hide personal.xls when you’re not working on your macros (via Window | Hide).

Add code to MS Project

  • I store all my macros in a central place (global.mpt), so that I can use them in all projects.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\MS Project\11\1033
  • You can move macros and other settings between different project files and global.mpt using MS Project’s organizer function, see Tools | Organizer.

Don’t forget to backup both files on a regular basis.