Microsoft Project VBA
Visual Basic Macros are posted here as I have time. Most are designed specifically to solve a certain problem that I have come across and once that problem has been solved I stop embellishing the macro. For this reason they are light on error handling and other niceties. I try to make them somewhat user-friendly however. Regardless, they are more useful as examples illustrating particular programming techniques rather than stand-alone solutions.
The basic structure of the macros is useful if you are going to be developing your own. The scrub macro is a good example of the technique of stepping through all the tasks and resources in the project. The filter macro is an illustration of how to create an apply a filter.
Feel free to use these on your own projects. Re-distribution, especially re-distribution as part of a commercial package is not allowed without my express permission.
| Macro Installation: | |
|---|---|
| If you are just getting started with VBA you will probably want to read how to install these code examples in your project or Global.mpt file. | |
| Run on Open and other events: | |
| To run a macro when your project opens (similar to Auto_open) or have Project perform actions when you close or save a file click here: | |
| Macros Which Export or Work with External Data: | |
|
Export hierarchy to Excel - Exports tasks and task information to excel while maintaining the task
hierarchy (outlining). Note: that this macro needs the correct references to the Excel object library set in order to run correctly. If you run it and get an error please try the following: Open Project. ALT+F11 to open the Visual Basic Editor From the Tools menu select "References". Scroll down until you see the Microsoft Excel Object Library (or something similar). Make sure the box next to it is checked. |
|
| Write Properties to a Text File - This macro exports all of the project properties to a text file. Also provides a simple example for doing error handling. | |
| Monte Carlo Simulation Macro - This macro performs Monte Carlo Simulation using Excel on your schedule. Complete information is available on the Blackjack page. | |
| Tracing and Filtering: | |
| Trace Path - This macro Traces the predecessors and/or successors to a selected task. It is useful for tracing a complicated chain of dependencies. It works by using recursion to find the predecessors and successors, marks them and then filters the project so only the marked tasks show. | |
| Filter Selection - This macro will filter the Gantt view to show only tasks which you have selected. It is helpful when you want to present a certain groups of tasks or you want to show a set of tasks that would be difficult to filter for. | |
| Ready to Start - This filters the gantt view to show only those which are "Ready to start" meaning that all their predecessors are at least 80% complete or that they have no predecessors. | |
| Working with Schedules and Constraints: | |
| Follow Earliest Predecessor - This macro allows you to have a task follow the earliest of its predecessor tasks. It does this by finding the predecessor which finishes first, then sets a large enough negative lag between it and the other predecessors so that the earliest predecessor is the driving activity. It is an example of how to work with task dependencies. It would be useful when you have a task which starts after the first of many similar tasks completes, but you do not know in advance which one that will be. As your schedule progresses you can run the macro and it will set the proper relationships. | |
| Move tasks around - A collection of little macros used to nudge constrained tasks one way or another on your gantt chart. Create some tool bar buttons to run each of these and you have a very easy way to move milestones around. It also includes a macro to reset the constraint to As Soon As Possible - use this to reset the task. | |
| Clearing Constraints - Entering dates or dragging tasks can accidentally create constraints. This macro removes constraints from all tasks which have logic (dependencies) to keep them in place. The module also has an "undo" macro which will restore the constraints if you do something wrong.. | |
| Set Recurring Task Period - By default Project only lets you enter recurrance by day or week and won't let you specify something like "every 12 days". This macro lets you set them to any number of days you like. | |
| Working with Task Data: | |
| Clearing Data From a File - This macro will replace the names of your tasks and resources with Unique ID's. It will also reset the name and the title of the project and delete the notes. This is useful when you want to share a project file with someone for debugging, yet you don't want to share the data in the file. Use it to clean confidential data from files before posting to newsgroups for example. (Note: Please only post project files and screen shots to newsgroups if requested. They can be difficult and costly for some people to download) | |
| Copy Task Data to Assignments - Copies task fields from a task custom field to the assignment custom field for all assignments on that task. Useful for getting task information to show up in assignment reports or usage views. | |
| Add Parent Task Names - This macro takes the names of all the parent tasks of a task and stores it in text12. It is useful if you have many tasks which have the same name, but have different parent tasks. The resulting string is the "path" to that particular task and it can be exported to Project Central or used for further identification whenever the task is taken out of context. | |
| Indent Task Names for Reports or Views - This macro adds (or removes) spaces in front of task names so that they will appear with indentation in reports or other areas. You enter the number of spaces to indent. | |
| Add Text - Frequently one needs to prefix some additional text to the names of a number of tasks. This macro prompts you to add text you enter to the names of tasks you have selected | |
| Formatting and Automating: | |
| Format Gantt Chart - Occasionally people want to format individual gantt chart bars. This macro gives an example of how to do it. It turns all your task bars green. Also can be modified to reset all the tasks to their default format or format them according to some value. Very useful when used with a calculated field. | |
Check Fields - This one checks to see which custom fields are being used in the project you have open.
It is a helpful check before you start customizing a field or before you copy data.
There are two versions. One for Project 2000 and another for Project 2002 and above. The 2002+ version uses some new methods introduced in Project 2002.
Specifically it uses FieldNameToFieldConstant and then GetField to go through the large number of custom fields by simply incrementing a number appended to the field name.
It is a big improvement on the previous version and should be used by anyone using Project 2002 or above.
|
|
| Print Individual Gantt Charts - This macro prints out a Gantt chart schedule for each resource in the project. It adjusts the scale to show all of the tasks assigned to the resource. You can modify it to print only those tasks with a specific range by modifying the filtering. It prints to the default printer. | |
| Clear Pending Update Flags and Indicators- This macro clears the flags and indicators that are set when you set workgroup messages. Note that clearing the flags does not affect the pending messages, it merely clears the display of the indicator. | |
| Set Percent Complete Based on Many Inputs- This will set percent complete based on values entered in a number of text fields. Here there are five text fields (text1-text5) and % complete for the task is based on the ratio of "Y" and "N" values entered there. | |
| Set Resource Graph View- This will set your view to show the resource list in the top of the window and a graph showing the total work for all the resources selected in the bottom. | |