Advanced VBA in MS Project
Presented by: Glenn Minch
Glenn Minch is president and founder of Critical Path Technical Services, Inc., a Microsoft Solution Provider specializing in developing custom solutions using Microsoft Project, Microsoft Office, and Visual Basic. Glenn can be reached at CPTS, Inc., PO Box 52771, Bellevue, WA 98005. CompuServe ID: 73513,403.
Overview
The addition of Microsoft® Visual Basic® for Applications to Microsoft Project planning software allows developers to quickly create powerful applications that use Microsoft Project as an integral component of the overall solution. In this section, we will take a look at some programming techniques that can be used to construct complex add-on applications for Microsoft Project.
We will also examine an application that was created with Microsoft Project, Microsoft Excel spreadsheet program and Visual Basic for Applications programming system. This application is a "real world" solution that was created to allow the customer to analyze project management data in a way not possible using only Microsoft Project. The resulting information provides a set of standards, or metrics, against which the progress of the project can be measured. Throughout the rest of this paper, we will refer to the sample application as the Project Metrics application.
The Project Metrics application will serve to illustrate the practical usage of Visual Basic for Applications programming techniques. Many of the code fragments included are taken from the Project Metrics application. Although the complete code of the application is not included here, one complete module from the Project Metrics application is included.
The code module included here contains procedures that manage the instance of Microsoft Excel required by the Project Metrics application.
Sample Files
Several sample files are included for this session. The sample files contain all of the code fragments used to illustrate the programming techniques discussed in this paper. The following sample files are included:
· PJ301A.MPP This Microsoft Project 4.0 file contains all of the code samples for this paper. In addition, it contains the complete code module used by the Project Metrics application to manage the instance of Microsoft Excel required for the application. The project schedule was copied from the ROLLOUT.MPT template that is included as a sample with Microsoft Project.
· PJ301B.XLS This is a Microsoft Excel file that contains the dialog box and code used by the GetXLPrefs() procedure. To run the sample code provided, you must copy PJ301B.XLS to the directory containing your Microsoft Project installation (i.e., the directory that contains WINPROJ.EXE).
· PJDEMO.EXE This is a Microsoft Visual Basic 3.0 application that acts as a dialog box for the FormatGantt() macro. This file must also be copied to the same location as WINPROJ.EXE.
· PJDEMO.FRM, PJDEMO.MAK Source code for PJDEMO.EXE.
· STATUS.MPP This Microsoft Project file is used to display status messages during the performance demonstrations.
Programming Techniques
Program Structures
Since this is intended to be an advanced discussion, we will not examine the basic program structures in this paper. However, it is worthwhile to consider the For Each ... Next statement, because there are special issues to be considered when using this statement with certain Microsoft Project object collections.
For Each ... Next
The For Each ... Next statement is used to iterate through a collection of objects. The For Each statement may also be used to iterate through an array. The main thing to keep in mind while using For Each with a Microsoft Project collection is that some collections may contain null members. An example of this can be seen in the following illustration:
{bmc ZCK0A.WPG}
In this illustration, task ID 4 is null. Because of this, the following code will fail with a run-time error 91, "Object variable not Set":
Sub ForEach()
Dim t As Task
For Each t In ActiveProject.Tasks
Debug.Print t.Name
Next t
End Sub
To correct this problem, you must test each member of the collection to be sure that it is valid before using it in an expression. Although the TypeName function could be used to perform the test to determine if an object is valid, it is generally quicker to test to see if the object Is Nothing. The following code illustrates this technique:
Sub ForEach()
Dim t As Task
For Each t In ActiveProject.Tasks
If Not (t Is Nothing) Then
Debug.Print t.Name
End If
Next t
End Sub
The following three collections may contain null members:
· Projects
· Tasks
· Resources
As mentioned above, the For Each statement may also be used to iterate through an array. The following example demonstrates this:
Sub ForEach_Array()
Dim v_array As Variant
Dim v As Variant
v_array = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)
For Each v In v_array
Debug.Print v
Next v
End Sub
Note the use of the Array function to create an array using the Variant data type. The For Each statement can be used with any array.
Activating Applications
You will often need to activate another application from your Visual Basic for Applications code. Generally, you’ll activate an application using the AppActivate method.
To use the AppActivate method, you must know the exact text (not case-sensitive) of the title of the application window that you want to activate. Since you won’t always have this information, you can use an application object to get the caption of the window. The following example demonstrates this technique:
''''''''''''''''''''
' Activates Microsoft Excel (Excel must already be running).
'
Sub ActivateExcel()
Dim oExcel As Object
Set oExcel = GetObject(, "Excel.Application.5")
AppActivate oExcel.Caption
End Sub
A more complex example involves checking to see if an instance of Microsoft Excel is already running, and if not, starting Microsoft Excel. Since an instance of Microsoft Excel might be running "invisibly" (that is, Microsoft Excel does not appear in the Microsoft Windows® Task List), we need a way to check for invisible instances of Microsoft Excel. To accomplish this, we’ll use the FindWindow function.
The FindWindow function is a Microsoft Windows API function that is declared as follows:
Declare Function FindWindow Lib "USER" (ByVal lpClassName _ As String, ByVal lpWindowName As Long) As Integer
The FindWindow function will retrieve the window handle of the window whose class name and window name match the arguments passed to FindWindow. If the class name parameter is null, all classes match; similarly, if the window name parameter is null, all window names match. We can take advantage of this fact by passing a null value for the window name parameter, and then passing the class name of the application we want to search for. Note that in the above declaration, the lpWindowName parameter is declared as the Long data type. This is because null pointers are passed as long integers with a value of zero (0&).
The class names for various applications are as follows. Although the class names presented here use a mixture of upper- and lower-case characters for readability, the FindWindow function is not case sensitive.
· Microsoft Project - "JWinproj-WhimperMainClass"
· Microsoft Excel - "XLMain"
· Microsoft Word - "OpusApp"
· Microsoft Access - "OMain"
If FindWindow doesn’t find a window that matches the specified window class name, it will return 0. The following code fragment (taken from the GetXLApp() function of the Project Metrics application) uses FindWindow to check for a current instance of Microsoft Excel.
Const XL_APPCLASS = "Excel.Application.5"
Const XL_WNDCLASS = "xlmain"
Const API_NULL = 0&
'Check to see if excel is running.
hWnd_xl = FindWindow(XL_WNDCLASS, API_NULL)
If hWnd_xl <> 0 Then
'There is at least one instance of Excel available; try 'to obtain obj ref
Set obj_xl_app = GetObject(, XL_APPCLASS)
Else
'Excel is not running. Start Excel and obtain obj ref.
Set obj_xl_app = CreateObject(XL_APPCLASS)
End If
Setting the Mouse Cursor
Unlike Visual Basic 3.0, Visual Basic for Applications does not provide a way to easily set the mouse cursor when you need to indicate that the application is busy. Microsoft Project will simply appear to "hang" during a lengthy operation. Using the Windows API functions LoadCursor and SetCursor, we can change the mouse cursor to the standard hourglass icon.
Include the following declarations and constants in your Visual Basic for Applications module:
Declare Function SetCursor Lib "USER" (ByVal hCursor As _ Integer) As Integer
Declare Function LoadCursor Lib "USER" (ByVal hInstance _ As Integer, ByVal lpCursorName As Any) As Integer
Const INT_NULL = 0
Const IDC_WAIT = 32514&
The following example shows how to set the cursor to the hourglass icon, then restore the cursor to the previous icon:
'''''''''''
' Sets the cursor to the Hourglass icon, then restores the
'previous cursor.
'
Sub SetMousePointer()
Dim hPrevCursor As Integer 'saves a handle to the previous
'cursor resource.
Dim lCounter As Long
Dim nRtn As Integer
' Set the cursor to hourglass and save the previous cursor
hPrevCursor = SetCursor(LoadCursor(INT_NULL, IDC_WAIT))
' Pause a moment so we can see the hourglass...
For lCounter = 1 To 1000000
Next lCounter
' Restore the previous cursor
nRtn = SetCursor(hPrevCursor)
End Sub
Modules
Visual Basic for Applications code is stored in code modules. Modules may be stored in the individual project file (*.MPP) or in the Global file (GLOBAL.MPT). Modules may contain variable and constant declarations in addition to procedures.
Choose an Effective Module Layout
The programmer has considerable latitude regarding the way that a module is organized. The way that a module is organized can help, or hinder, development efforts.
The following example illustrates an effective method of module organization. This example is taken from the Project Metrics application.
' This module contains the procs that are responsible for 'providing xl support functions to the other routines. None of 'the procs in this module manipulate project data.
'
' Procs contained in this module:
' Sub GoToMetrics()
' Function GetUserPref(oXL As Object, utPref As _
' utPrefStruct) As Boolean
' Function GetXLBook(oXLBook As Object, Status As _
' Integer, Optional sTemplate As Variant) As Boolean
' Function GetXLApp(oXLApp As Object) As Boolean
' Function KillXL(Optional xl_obj As Variant) As Boolean
' Sub MakeXLVisible()
The first few lines of each module are a brief description of the contents of the module. This description is important during the development of large projects, and it will assist maintenance programmers during future modifications to the application.
Following the module description is a section that contains the procedure header for every procedure in the module. It is important that the procedure headers exactly match the actual procedures. This header section serves several purposes:
· Immediately indicates what procedures are contained in a given module;
· Allows the programmer to quickly move to a given procedure, by clicking on the name of the procedure using the right mouse button and selecting "Procedure Definition";
· Provides a way to quickly look up the correct order and type of arguments to a given procedure.
The module declarations section should be placed immediately after the initial comments and list of procedures.
' **** BEGIN MODULE DECLARATIONS ****
Option Explicit
Option Compare Text ' Text comparisons are NOT case 'sensitive.
Private obj_xl_app As Object ' This is used to cache a 'reference to the current
'Excel application.
Private obj_xl_book As Object ' This is used to cache a 'reference to the current
'Excel metrics workbook.
' **** END MODULE DECLARATIONS ****
The declarations section should be clearly identified to set it apart from the procedures that follow. Typically, variables declared at the module or global level should have some sort of comment to indicate their purpose.
The Option Private Statement
The VBA_PJ.HLP file included with Microsoft Project states that "The Option Private statement is used at the module level to indicate that the entire module is private." This statement is somewhat misleading, since it implies that all of the data and procedures contained in a module will be private to that module if the Option Private statement is included in the module declarations.
The Option Private statement means that the procedures in a private module cannot be called from procedures located in a module in another project. Procedures contained in the private module can be called from any other modules in the project file where the private module is located.
If a variable is declared in a private module using the Public keyword, that variable will be visible to all other modules in the project. Furthermore, Sub procedures located in a private module will still appear in the list of macros under the Tools menu, Macros option.
The Option Private statement is useful for preventing name conflicts among procedures in different projects, but only if a reference exists between the projects.
Obtaining User Input
Visual Basic for Applications does not implement dialogs, so Microsoft Project does not support user-defined dialog boxes. The primary mechanism for obtaining user input is the InputBox function. Although the InputBox is useful for obtaining simple user input, it does not allow the use of common controls such as option buttons, check boxes, and list boxes.
One solution to this problem is to use a dialog box created in Microsoft Excel or Microsoft Visual Basic 3.0 to obtain user input for your macro. However, this creates a minor dilemma of sorts: How to pass information from the dialog box back to the Microsoft Project macro?
Using Microsoft Excel, you are able to pass arguments to a macro via the application-defined Macro method. Although Microsoft Project supports a Macro method, it does not allow you to pass arguments to the macro.
There are several ways to return user input to the Microsoft Project macro. You could use DDE, OLE, global memory blocks, the Clipboard, or even text files. The method we will examine here uses OLE, and although it is not the fastest method, it is simple to implement and reliable.
The following diagram illustrates the basic concept.
{bmc ZCK1A.WPG}
In this diagram, the following actions occur (in sequence):
1. A Microsoft Project macro uses the AppExecute method to launch a Visual Basic 3.0 form. The Visual Basic form will act as a dialog box to obtain user input for the Microsoft Project macro. The following code is from the example included with this paper:
Sub FormatGantt()
' Display the VB dialog to obtain user input.
AppExecute Command:=Application.Path & "\pjdemo.exe" _ ,Activate:=True
End Sub
2. The Visual Basic form handles the task of obtaining user input. When the user is finished and has selected the OK or Cancel buttons, the Visual Basic form establishes an OLE connection to Microsoft Project.
Dim oProj as Object
Set oProj = GetObject(, "msproject.application")
3. The Visual Basic form sets the value of certain pre-determined fields of the project summary task to the values obtained from the user. In the example provided with this paper (PJDEMO.EXE), the Visual Basic form sets the value of the Text 10 field.
If index = 0 Then 'OK button pressed
oProj.Activeproject.Text10 = cboColor.List(cboColor.ListIndex)
Else 'Cancel button pressed
oProj.Activeproject.Text10 = "Cancel"
End If
4. The Visual Basic form activates Microsoft Project. Then the Visual Basic form uses the Macro method to evoke a Microsoft Project macro that will process the user input. After the Microsoft Project macro is evoked, the Visual Basic form unloads itself from memory.
' Run the Microsoft Project macro to process the user's 'selection
AppActivate oProj.Caption
oProj.macro "DoFormatGantt"
End
5. The Microsoft Project macro examines the user input and takes the appropriate action.
''''''''''''''''
' This sub is called by the VB dialog to process the user's 'selection.
'
Sub DoFormatGantt()
Dim sMsg As String
Dim nColor As Integer
Const pjNoAction = -1
'Check the Text10 property of the project task to determine
'what to do next.
Select Case ActiveProject.Text10
Case "cancel"
sMsg = "User pressed the Cancel button."
nColor = pjNoAction
Case "black"
sMsg = "User selected Black."
nColor = pjBlack
Case "red"
sMsg = "User selected Red."
nColor = pjRed
Case "yellow"
sMsg = "User selected Yellow."
nColor = pjYellow
Case "blue"
sMsg = "User selected Blue."
nColor = pjBlue
Case "green"
sMsg = "User selected Green."
nColor = pjGreen
Case Else
sMsg = "The value returned from the dialog is not valid."
nColor = pjNoAction
End Select
If nColor <> pjNoAction Then GanttBarFormat middlecolor:=nColor
MsgBox sMsg, vbInformation + vbOKOnly, "Format Gantt Bar"
End Sub
This general method can be extended to work with applications other than Visual Basic 3.0. The XL code module included with PJ301A.MPP contains a procedure that calls a Microsoft Excel dialog to obtain user input. The procedure name is GetUserPref().
Project VBA Performance Issues
Measuring Performance
In order to address performance issues in your code, you need to be able to accurately measure the speed of your routines. The GetTickCount function will give us this capability.
GetTickCount is a Windows API function that returns the number of milliseconds that have elapsed since the current session of Windows was started. This function can be declared in a Project code module as:
Declare Function GetTickCount Lib "USER" () As Long
Calculating the difference between this function’s value at the beginning and end of a routine provides an excellent measure of the routine’s performance. The following example illustrates the use of this function in a simple case.
' This sub displays the number of milliseconds required
' to run through a simple For...Next loop.
Sub TimeLoop()
Dim lBegin As Long ' Beginning tick count
Dim lEnd As Long ' Ending tick count
Dim lIndex As Long ' Index for loop
lBegin = GetTickCount()
For lIndex = 1 To 60000
Next lIndex
lEnd = GetTickCount()
MsgBox "Elapsed time: " & (lEnd - lBegin) & " ms."
End Sub
We’ll be using this method throughout this session to illustrate code performance in different situations.
Screen Updating
One of the major performance hits you’ll encounter when creating Microsoft Project macro code is the inability to turn off screen updating while the macro executes. The Microsoft Project Application object does not include a ScreenUpdating property as Microsoft Excel does. Instead, Microsoft Project spends a lot of time refreshing its application window during the execution of your macro. If a graphically intense view such as the Gantt Chart happens to be displayed, this constant screen updating may result in a major performance loss.
To illustrate the effect the current view will have on your macro performance, try the following example. This example runs through the same loop four times, and in each case sets the Flag1 field of every task in the project to True. Each loop is executed while a different view is active. The difference in performance among the different views is displayed in a message box. Note that the test macros do not check for null tasks in order to simplify the code listing. A "real" macro must always check for null tasks.
' This sub sets the Flag1 field of all tasks to True
' in 4 different views.
Sub SetFlagTest1()
Dim tskIndex As Task ' Index for Tasks collection
Dim lBegin As Long ' Beginning tick count
Dim lEnd As Long ' Ending tick count
Dim lGantt As Long ' Time to execute in Gantt view
Dim lTSheet As Long ' Time to execute in Task Sheet view
Dim lRSheet As Long ' Time to execute in Resource Sheet view
Dim lEdit As Long ' Time to execute in Module Editor view
ViewApply "Gantt Chart"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lGantt = lEnd - lBegin
ViewApply "Task Sheet"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lTSheet = lEnd - lBegin
ViewApply "Resource Sheet"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lRSheet = lEnd - lBegin
ViewApply "Module Editor"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lEdit = lEnd - lBegin
MsgBox "Elapsed times:" & Chr(10) & _
"Gantt - " & lGantt / 1000 & " s." & Chr(10) & _
"Task Sheet - " & lTSheet / 1000 & " s." & Chr(10) & _
"Resource Sheet - " & lRSheet / 1000 & " s." & _ Chr(10) & "Editor - " & lEdit / 1000 & " s."
End Sub
Running this macro on the sample Rollout project that ships with Microsoft Project (ROLLOUT.MPT) will give results similar to this:
{bmc ZCK2A.WPG}
Note that since only task data is being updated, Microsoft Project repaints the window only when in a task view, hence the virtually equivalent execution times in the Module Editor and Resource Sheet views.
As an alternative to applying a different view, you may just want to minimize the application window. When minimized, Microsoft Project does not need to repaint its window and so your code executes at about the same rate as it would in the Module Editor view.
Try the following modification to the last example:
' This sub sets the Flag1 field of all tasks to True
' in 4 different views.
Sub SetFlagTest2()
Dim tskIndex As Task ' Index for Tasks collection
Dim lBegin As Long ' Beginning tick count
Dim lEnd As Long ' Ending tick count
Dim lGantt As Long ' Time to execute in Gantt view
Dim lTSheet As Long ' Time to execute in Task Sheet view
Dim lRSheet As Long ' Time to execute in Resource Sheet view
Dim lEdit As Long ' Time to execute in Module Editor view
' Minimize the Project application window
Application.WindowState = pjMinimized
ViewApply "Gantt Chart"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lGantt = lEnd - lBegin
ViewApply "Task Sheet"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lTSheet = lEnd - lBegin
ViewApply "Resource Sheet"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lRSheet = lEnd - lBegin
ViewApply "Module Editor"
lBegin = GetTickCount()
For Each tskIndex In ActiveProject.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lEdit = lEnd - lBegin
' Restore the Project application window
Application.WindowState = pjNormal
MsgBox "Elapsed times:" & Chr(10) & _
"Gantt - " & lGantt / 1000 & " s." & Chr(10) & _
"Task Sheet - " & lTSheet / 1000 & " s." & Chr(10) & _
"Resource Sheet - " & lRSheet / 1000 & " s." & _ Chr(10) & "Editor - " & lEdit / 1000 & " s."
End Sub
Running this variation on the same Rollout project gives the following results:
{bmc ZCK3A.WPG}
Note that while in this minimized state, the code executes at virtually the same speed in all cases.
A major drawback of minimizing Microsoft Project while executing your code is that the user is then provided with little or no visual feedback on the progress of the macro. At least while a task view is visible, the flickering screen redraws give some indication that the macro is still executing. For time consuming macros, users will generally prefer to trade a little speed for better visual feedback. A nice method for managing this tradeoff is to creatively use a dummy project.
Creating a dummy project that you can use to fill the application window provides you with two benefits: It eliminates excessive screen redraws and provides a nice medium for displaying status messages. To effectively use this method, you must first create the project you will use to provide the status messages. Take a look at the STATUS.MPP file provided. This project contains a single task and has been saved while a custom PERT view named "Macro Status" was active. While our macro is running, we open this file and use the single task box displayed to provide status messages to the user.
To illustrate this process, the following macro runs through every task in the active project twice, first setting the Flag1 field to True and then setting the Flag2 field as well.
Sub SetFlagTest3()
Dim tskIndex As Task ' Index for Tasks collection
Dim tskStatus As Task ' Task used to display progress
Dim prjStatus As Project ' Project used to display progress
Dim prjCurrent As Project ' Current project
Set prjCurrent = ActiveProject
FileOpen "status.mpp"
Set prjStatus = ActiveProject
Set tskStatus = prjStatus.Tasks(1)
ActiveWindow.Caption = "Macro Status"
tskStatus.Name = "Now setting task Flag1 fields..."
DoEvents ' Allows view to refresh
For Each tskIndex In prjCurrent.Tasks
tskIndex.Flag1 = True
tskStatus.Name = "Now processing task " & tskIndex.ID
Next tskIndex
tskStatus.Name = "Now setting task Flag2 fields..."
DoEvents ' Allows view to refresh
For Each tskIndex In prjCurrent.Tasks
tskIndex.Flag2 = True
tskStatus.Name = "Now processing task " & tskIndex.ID
Next tskIndex
FileClose pjDoNotSave
End Sub
Note that by taking advantage of the way Microsoft Project delays refreshing the displayed task box, the macro can actually display one message in the box while displaying another in the entry bar.
To determine how much impact these status messages are actually having on the macro’s performance, we can use the following code:
Sub SetFlagtest4()
Dim tskIndex As Task ' Index for Tasks collection
Dim lBegin As Long ' Beginning tick count
Dim lEnd As Long ' Ending tick count
Dim lMsgFlag1 As Long ' Time to set Flag1 with status messages
Dim lMinFlag1 As Long ' Time to set Flag1 while app minimized
Dim lMsgFlag2 As Long ' Time to set Flag2 with status messages
Dim lMinFlag2 As Long ' Time to set Flag2 while app minimized
Dim tskStatus As Task ' Task used to display progress
Dim prjStatus As Project ' Project used to display progress
Dim prjCurrent As Project ' Current project
Set prjCurrent = ActiveProject
FileOpen "status.mpp"
Set prjStatus = ActiveProject
Set tskStatus = prjStatus.Tasks(1)
ActiveWindow.Caption = "Macro Status"
lBegin = GetTickCount()
tskStatus.Name = "Now setting task Flag1 fields..."
DoEvents ' Allows view to refresh
For Each tskIndex In prjCurrent.Tasks
tskIndex.Flag1 = True
tskStatus.Name = "Now processing task " & tskIndex.ID
Next tskIndex
lEnd = GetTickCount()
lMsgFlag1 = lEnd - lBegin
lBegin = GetTickCount()
tskStatus.Name = "Now setting task Flag2 fields..."
DoEvents ' Allows view to refresh
For Each tskIndex In prjCurrent.Tasks
tskIndex.Flag2 = True
tskStatus.Name = "Now processing task " & tskIndex.ID
Next tskIndex
lEnd = GetTickCount()
lMsgFlag2 = lEnd - lBegin
FileClose pjDoNotSave
Application.WindowState = pjMinimized
lBegin = GetTickCount()
For Each tskIndex In prjCurrent.Tasks
tskIndex.Flag1 = True
Next tskIndex
lEnd = GetTickCount()
lMinFlag1 = lEnd - lBegin
lBegin = GetTickCount()
For Each tskIndex In prjCurrent.Tasks
tskIndex.Flag2 = True
Next tskIndex
lEnd = GetTickCount()
lMinFlag2 = lEnd - lBegin
Application.WindowState = pjNormal
MsgBox "Elapsed times with messages:" & Chr(10) & _
"Flag1 - " & lMsgFlag1 / 1000 & " s." & Chr(10) & _
"Flag2 - " & lMsgFlag2 / 1000 & " s." & Chr(10) & _
Chr(10) & "While minimized:" & Chr(10) & _
"Flag1 - " & lMinFlag1 / 1000 & " s." & Chr(10) & _
"Flag2 - " & lMinFlag2 / 1000 & " s."
End Sub
Running this macro on a project of 200 tasks, you should get results similar to this:
{bmc ZCK4A.WPG}
Note that providing status messages in this case increased execution time by about 30%. While this can represent a substantial increase in execution time on some projects, user’s will generally prefer to accept the performance drop in return for the continuous feedback.
Minimize Direct Object Manipulation
Another way to improve the performance of a Microsoft Project macro is to minimize, wherever possible, the direct manipulation of objects in code. Although it is often convenient to iterate through an entire collection of objects while performing some action, it is usually much quicker to use some of Microsoft Project’s built-in features to minimize the number of objects that must be examined. An example of this technique follows.
In this example (taken from the Project Metrics application) it is necessary to examine each of the tasks in the project to determine if there are any resources assigned to the task. The following code fragment accomplishes this by checking the count of resources assigned to each task in the current project’s task collection:
' Assign the dummy resource to any task that does not already 'have at least one resource already assigned. A maximum of 1,000 'assignments may be made.
tassign = 0
For Each t In ActiveProject.Tasks
If Not (t Is Nothing) Then
If t.Resources.Count < 1 Then
If tassign > 1000 Then Error err_assign_limit
t.Assignments.Add ResourceID:=oNewRes.ID
tassign = tassign + 1 'keeps track of the 'number of assignments made
End If
End If
Next t
The preceding code works, but the next example is much faster. The difference between the first example and the second is that the second example sets a task filter, which filters out all tasks that have resources assigned. This filtering operation is very fast and can improve the performance of this routine by over 300%.
The cpts_nores_filter constant refers to a task filter that is defined as follows:
| Field Name | Test | Value(s) | And/Or |
| Resource Names | Equals | And | |
| Milestone | Equals | No | And |
| Summary | Equals | No |
tassign = 0
FilterApply cpts_nores_filter
SelectAll
If Not ActiveSelection.Tasks Is Nothing Then
For Each t In ActiveSelection.Tasks
With t
If tassign > 1000 Then Error err_assign_limit
.Assignments.Add ResourceID:=oNewRes.ID
tassign = tassign + 1 'keeps track of the number of 'assignments made
End With
Next t
End If
© 1995 Microsoft Corporation.
THESE MATERIALS ARE PROVIDED "AS-IS," FOR INFORMATIONAL PURPOSES ONLY.
NEITHER MICROSOFT NOR ITS SUPPLIERS MAKES ANY WARRANTY, EXPRESS OR IMPLIED WITH RESPECT TO THE CONTENT OF THESE MATERIALS OR THE ACCURACY OF ANY INFORMATION CONTAINED HEREIN, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW EXCLUSIONS OF IMPLIED WARRANTIES, THE ABOVE LIMITATION MAY NOT APPLY TO YOU.
NEITHER MICROSOFT NOR ITS SUPPLIERS SHALL HAVE ANY LIABILITY FOR ANY DAMAGES WHATSOEVER INCLUDING CONSEQUENTIAL INCIDENTAL, DIRECT, INDIRECT, SPECIAL, AND LOSS PROFITS. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF CONSEQUENTIAL OR INCIDENTAL DAMAGES THE ABOVE LIMITATION MAY NOT APPLY TO YOU. IN ANY EVENT, MICROSOFT’S AND ITS SUPPLIERS’ ENTIRE LIABILITY IN ANY MANNER ARISING OUT OF THESE MATERIALS, WHETHER BY TORT, CONTRACT, OR OTHERWISE SHALL NOT EXCEED THE SUGGESTED RETAIL PRICE OF THESE MATERIALS.