Lesson Objectives
Upon completion of this lesson, the participant will be able to:
Some Topics to be introduced in this lesson include:
Overview of Object Hierarchy
Visual Basic for Applications allows each application to extend the Visual Basic language with application specific objects. Microsoft Excel exposes Workbook, Chart, and Cell objects whereas Microsoft Project exposes Project, Task, Resource, and Calendar objects. Of course, there are places of overlap. Both Microsoft Project and Excel expose Application and Windows objects and wherever possible the shared objects have the same properties and methods.
Nesting of Objects
Objects are nested within each other to form a hierarchy. The Application contains the Projects, which contain the Tasks, which contain the Assignments. It is probably easiest to understand the hierarchy of the Microsoft Project object model through a figure.
The Object Hierarchy for Microsoft Project
Navigation
As you can see, the Application object sits at the top of the hierarchy. From the Application object you can navigate down to any other object. For example, the Duration field is contained in a Task which is contained in a Project which is contained in the Application. So to modify the Duration, you specify:
Application.Projects(1).Tasks(3).Duration = "3d"
Fortunately, there are some shortcuts that are available when referencing objects. First, the Application object is optional when accessing objects within Microsoft Project. So you can use:
Projects(1).Tasks(3).Duration = "3d"
Additionally, there are some pre-defined reference objects for commonly used objects. For example, there is an ActiveProject object which always refers to the active project. So, if Projects(1) were the active project, the same duration field can be accessed like:
ActiveProject.Tasks(3).Duration = "3d"
ActiveCell.Task.Duration = "5d"
Common Objects
There are some objects which are common to Microsoft Project and Excel. For example, both products support automatic or manual recalculation. Hence, the Application objects in both products have a Calculation property. For example, the following code has the same effect in Excel as it does in Project.
Application.Calculation = True
Differences between Microsoft Project and Excel
One difference to note between the implementation of Visual Basic for Applications in Microsoft Project and Excel is the extent of their respective object models. Microsoft Excel has taken the approach of creating objects for all of the data and user interface. In Microsoft Project, the emphasis was on exposing objects for the unique scheduling data (tasks, resources, etc...) and a hierarchy for navigating to that data. Most of the user interface elements are left to statements (such as FileOpen).
This difference can be seen in things like the way you close a file. In Microsoft Project, you use the FileClose statement:
Projects(2).Activate
FileClose
In Microsoft Excel, you can do this in one step:
Workbooks(2).Close
Another important difference is in the level of user input available in Microsoft Project and Excel. Visual Basic for Applications currently only provides the Visual Basic language. Excel has implemented a dialog editor in the form of Dialog Sheets which allow the user to obtain much of the benefit of the stand-alone Visual Basic dialog editor. In Excel, you can attach code to buttons and tie controls to worksheet cells.
Microsoft Project, on the other hand, provides forms for entering information about tasks or resources. These custom forms can include any of the fields that Microsoft Project supports. If a great level of user interaction is required beyond entering information about tasks or resources, it may be advantageous to use the stand-alone Visual Basic and control Microsoft Project through OLE Automation. This way you can leverage the great dialog tools in Visual Basic, but still access the rich scheduling data and the user interface statements.
Application Object
The Application is at the top of the Object Model hierarchy. It is through the Application object that all other objects can be accessed. The Application object contains several interesting properties and methods which are useful in macros.
User Interface Commands and Methods
The Application object contains methods which correspond to the user interface actions. These are the same as the macro commands found in Microsoft Project 3.0. For instance:
Application.FileOpen Name:="project1.mpp"
Application.EditCopyPicture
Since the Application object is always optional when writing macros within Microsoft Project, you can specify these items simply by their name:
FileOpen Name:="project1.mpp"
EditcopyPicture
For people familiar with the macro language in Microsoft Project version 3.0, there are a few differences of importance. While the names of many of the methods remained the same, the names of some of the methods changed to make them less tied to the menu structure (since the menu structure tends to change from version to version).
For example, the 3.0 command "OptionsSetPlan" was renamed to "BaselineSave". This change reflects both the change in terminology introduced in Microsoft Project 4.0 (Baseline vs. Plan) and the move away from menu-based naming.
Functionality has been added to this class of Application methods in two ways. First, some methods have improved functionality over their 3.0 counterparts. For example, the FileSaveAs method now has the ability to specify a table name when exporting to a different file format like XLS. Second, methods have been added to access functionality that was not available in the 3.0 macro language, like GanttBarStyleEdit. This particular command allows the macro writer to create or modify Gantt bar styles, such as changing the pattern and color for milestones.
Application Object Methods
Application Methods are commands that execute a Project feature. For example, the CalculateProject method is the same as hitting F9 on your keyboard. FileOpen is the same as choosing Open from the File Menu.
Most of the Application methods are used for basic Project commands. For example, the File and Edit menu methods have the same effect as choosing items from under your File and Edit menus.
FileNew, FileOpen, FileClose, FileSave, FileSaveAs, FileExit, EditCut, EditCopy, EditPaste, EditDelete, etc...
Some of the more interesting and useful application methods are ones that enable movement control of the active cell.
SelectCellDown, SelectCellRight, SelectCellLeft, SelectCellUp, SelectColumn. These allow easy movement of the active cell, similar to using your arrow keys. The following macro calculates a formula in the Number1 field and then moves down 3 tasks and repeats the formula.
For I = 1 to 10
ActiveCell.Task.Number1 = ActiveCell.Task.Duration * 5
SelectCellDown 3
Next
Almost all actions that can be performed manually in Project can be achieved by running application methods from within a macro. When you record a macro, you are using the user interface to issue commands. Hence, when a macro is recorded, these Application methods are used.
Exercise1:
Using the record macro feature of Project, record a macro that records as many commands that you can think of. After you are finished recording, edit your macro and look at the different application methods it recorded.
Exercise2: (MoveRect)
Write a macro using application methods that saves a file you created, and then moves the active cell around in a rectangle of any size 5 times. Then close the file. (Do not use the macro recorder or help file, this is an exercise to help train your mind to think in "method" mode.)
Sub MoveRect()
FileSave
For i = 1 To 5
SelectCellRight 2
SelectCellDown 2
SelectCellLeft 2
SelectCellUp 2
Next i
FileClose
End Sub
Application Object Properties
Most of the Application Object properties are used to set up and change the environment. For example, the Top property returns the position on the screen of the application window. You can set this property to a different value to reposition the window. However, not all properties are editable. For example, the Version property simply reports what version of Project your are currently working with. You cannot set the value. Version = 5.0 would fail.
Options & Leveling Properties
Another useful aspect of the Application object is the ability to set the option properties which correspond to the settings found in the Options and Leveling dialogs. Many times users need to make sure that before running their macro certain settings are always set to specific values. By setting a series of Application properties ahead of time the user can insure consistancy.
LevelOrder = 2 ‘Sets the leveling order to Priority,Standard
StartYearIn = 3 ‘Sets the fiscal year to start in March
MoveAfterReturn = True ‘Self explanitory
Most of the settings found in the Tools/Options dialog are accessible through the Application object. The exception here are the settings which are project specific, like the default Standard Rate for new resources. Since these options are project specific, they are contained in the Project object.
The Application object also holds some of the code module settings which are derived from the system, such as date and time formats, and the list separator (from the Module General tab of the Options dialog). Since these items are controlled by the operating system, they cannot be set from within Microsoft Project. These options are very useful when writing macros which will be run in different countries which use different settings. For example, in the United States, the normal date order is Month-Day-Year, but many other countries use Day-Month-Year. A macro could query this setting to know how to interpret dates.
Exercise:
Using the list of properties for the Application Object from the help file, write a macro which displays several current values of properties from the Options and Leveling dialogs.
Corresponding Application Methods
All of the options which can be accessed via either the Application or Project objects property can also be set using an Application method. There is one Application method for each tab in the Options dialog. For example:
Application.Calculation = pjManual
is the same as
OptionsCalculation Automatic := False
And,
ActiveProject.AutoAddResources = True
is the same as
OptionsGeneral AutoAddResources := True
The advantage of using the properties is that you can determine the current setting of the option, as demonstrated in the following example which saved the state of calculation before setting it to manual.
bCalcSetting = Application.Calculation
Application.Calculation = pjManual
...
Application.Calculation = bCalcSetting
The advantage in using the method is that many options can be set at once. The following example shows how you can set some of the display options for the application using both properties and a method.
Application.DisplayStatusBar = True
Application.DisplayScrollBars = True
Application.DisplayNotesIndicator = False
OptionsView DisplayStatusBar:=True, DisplayEntryBar:=True,
DisplayScrollBars:=True, DisplayNotesIndicator:=False
For the options which are project specific, the corresponding Application method provides the functionality to set the default for new projects. In the Tools/Options dialog, near some of the project specific options there is a ‘Set as Default" button which records the settings of the current project as the default settings for new projects. With those Application methods, there is an accompanying SetDefaults argument which allows those settings to be recorded. For example, to set the hours per week to 40 for new projects, you can use the OptionsCalendar method:
OptionsCalendar HoursPerWeek:=40, SetDefaults:=True
The following sample combines the two approaches to make the current project specific settings the defaults for new projects.
OptionsCalendar StartTime:=ActiveProject.DefaultStartTime,
FinishTime:=ActiveProject.DefaultFinishTime,
HoursPerDay:=ActiveProject.HoursPer,
HoursPerWeek:=defHoursPerWeek,
SetDefaults:=True
Project Object
Project objects are located within the Application object and contain all of the project-level information, such as the start of the project and the manager, as well as the details of the project, namely the tasks and resources.
Referencing Project Objects
There are several ways of referencing a project object. The easiest way is by referencing the active project. There is a property of the application object called ActiveProject which returns the object corresponding to the active project.
MsgBox ActiveProject.ProjectStart
This example displays the start date of the active project in a dialog box.
You can also use the Projects collection to refer to a particular project. Projects can either be referenced by their index or by their name.
MsgBox Projects(1).ProjectStart
MsgBox Projects("project1.mpp").ProjectStart
CurrentView, CurrentTable and CurrentFilter
Some useful properties of a Project object are those relating to the Views, Tables, and Filters. Each project contains CurrentView, CurrentTable, and CurrentFilter properties. These return the name of the current view, table, and filter respectively. Using these properties allows you to make sure that the user is in the correct view before executing a macro or lets you save the user’s state so that it can be restored after the execution of a macro.
The following example records the current state of the project, prints a PERT Chart, and then returns the user to the correct state.
Sub PrintPERTChart()
strView = ActiveProject.CurrentView
strFilter = ActiveProject.CurrentFilter
ViewApply "PERT Chart"
FilePrint
ViewApply strView
If strFilter <> "" Then
FilterApply strFilter
End If
End Sub
List Collections
Another useful object found in the project object is related to the CurrentView, CurrentTable, and CurrentFilter properties. It is the List collection. The List collection is a collection of strings or numbers. Because it is a collection, you can iterate over all of the items in the list using the For Each...Next construct discussed earlier. You can also index the items in the list directly.
In the Project object, there are several List collections which correspond to the Views, Tables and Filters available for use by the project. For example, the TaskViewList contains the names of all of the task views available for the project. Similarly the ResourceFilterList contains all of the resource filters.
This example is a function which checks the current view to see if it is a task view. It uses both the CurrentView and the TaskViewList properties. (This example would only have problems if a resource view had the same name as a task view.)
Function InTaskView() As Boolean
Dim bFound as Boolean
Dim varTemp as Variant
bFound = False
For Each varTemp in ActiveProject.TaskViewList
If ActiveProject.CurrentView = varTemp then
bFound = True
Exit For
End If
Next varTemp
InTaskView = bFound
End Function
Remember, even if a view is not in the TaskViewList, this does not necessarily mean that it is a resource view. The Module Editor view for editing Visual Basic code is neither a task nor a resource view.
Exercise: (ApplyAView)
Write a macro that prompts the user to enter the name of a task view and them applies it only if it is available. If it is not them return an error message.
Sub ApplyAView()
test = False
myView = InputBox("Enter a View to display: ")
For Each item In ActiveProject.TaskViewList
If LCase(item) = LCase(myView) Then
ViewApply Name:=myView
test = True
End If
Next item
If test = False Then MsgBox "View Does not exist"
End Sub