« Dinoganda | Main | Incredible Sea Creatures »

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", _
MapEdit Name:="Map 1", _
DataCategory:=0, FieldName:="Finish", _
MapEdit Name:="Map 1", DataCategory:=0, _
FieldName:="% 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.



The previous article is Dinoganda.

The next article is Incredible Sea Creatures.

Current articles are in the main index page and you can find a complete list of articles in the archives.

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