Using VBA with Local Custom Fields and Outline Codes
Custom fields provide extended attributes of tasks, resources, or projects. Microsoft Office Project 2007 simplifies the administration of custom fields and outline codes and the related programmatic interface.
A Project Management Office (PMO) uses enterprise custom fields and their associated lookup tables to collect data for roll-up reporting across the organization. In Microsoft Office Project Server 2007, enterprise custom fields are also called server custom fields. You can export local custom fields in a project to become enterprise custom fields in Project Server. For more information, see Local and Enterprise Custom Fields.
Earlier versions of Project define two kinds of custom fields:
Outline codes, which can use hierarchical lookup tables.
Custom fields which can use flat value lists or formulas to determine the value. Value lists have no hierarchy.
In Project Standard 2007 and Project Professional 2007, outline codes operate the same way as in earlier versions of Project. In Project Server 2007, an outline code is not a separate entity. Custom text fields with hierarchical lookup tables handle the features of outline codes in Project Server. Outline code custom fields are included within the drop-down list in the custom field collections for Task and Resource, and also for Project when Project Professional is connected with Project Server.
Figure 1 shows that you can select Project custom fields as well as Task and Resource custom fields options in the Project Professional 2007 Custom Fields dialog box. The set of predefined local custom fields in Project 2007 is the same as in earlier Project versions, and adds Outline Code as a type of custom field.
Project Professional 2003 handles administration of both local and enterprise custom fields. In Project 2007, you create and edit server (enterprise) custom fields in Project Web Access. Project Professional 2007 can show server custom fields, but cannot change them. Project Professional manages local custom fields for tasks and resources. The Project option shows server custom fields in a read-only view. You can easily see the changes in Project 2007 when you compare Figure 1 with Figure 2, the Customize Fields dialog box in Project 2003.
The Customize submenu on the Tools menu in Project 2007 includes Fields, but not Enterprise Fields. For example, the RBS server custom field is a read-only Resource Text custom field. Accordingly, the Project 2007 object model deprecates (hides) Project 2003 methods such as EnterpriseCustomizeFields that handle server custom fields. To see hidden members in the Microsoft Visual Basic for Applications (VBA) Object Browser, right-click in the Members pane, and then click Show Hidden Members.
In Project 2007, there is no longer a 1:1 relationship between a custom field and a lookup table. As a local user, you can import a lookup table from Global.mpt or another open project. You can create hierarchical lookup tables in the Edit Lookup Table dialog box (Figure 3) for all custom field types except flag.
A Project Server administrator who has the correct permission can create multiple shared lookup tables, for example, Locations and Departments. The administrator can then create a server custom field such as Location and select the lookup table to use. To manage enterprise custom fields, lookup tables, and calculation formulas programmatically, you must use the Project Server Interface (PSI).
The following code shows one way to create the lookup table shown in Figure 3, where Redmond is the default value. New object model features in Project 2007 are shown in bold font.
Sub CreateLocationOutlineCode() Dim objOutlineCode As OutlineCode Dim defaultCity As String Set objOutlineCode = ActiveProject.OutlineCodes.Add( _ pjCustomResourceOutlineCode9, "Location") DefineLocationCodeMask objOutlineCode.CodeMask defaultCity = EditLocationLookupTable(objOutlineCode.LookupTable) objOutlineCode.DefaultValue = defaultCity objOutlineCode.SortOrder = pjListOrderAscending Application.FileSave End Sub Sub DefineLocationCodeMask(objCodeMask As CodeMask) objCodeMask.Add _ Sequence:=pjCustomOutlineCodeUppercaseLetters, Length:=2, _ Separator:="." objCodeMask.Add _ Sequence:=pjCustomOutlineCodeCharacters, Length:="Any", _ Separator:="." objCodeMask.Add _ Sequence:=pjCustomOutlineCodeCharacters, Length:="Any", _ Separator:="." End Sub Private Function EditLocationLookupTable(objLookupTable As LookupTable) As String Dim objStateEntry As LookupTableEntry Dim objCountyEntry As LookupTableEntry Dim objCityEntry As LookupTableEntry Dim defaultCityName As String Set objStateEntry = objLookupTable.AddChild("WA") objStateEntry.Description = "Washington state" Set objCountyEntry = objLookupTable.AddChild("King", _ objStateEntry.UniqueID) objCountyEntry.Description = "King county" Set objCityEntry = objLookupTable.AddChild("Seattle", _ objCountyEntry.UniqueID) objCityEntry.Description = "Company headquarters" Set objCityEntry = objLookupTable.AddChild("Redmond", _ objCountyEntry.UniqueID) objCityEntry.Description = "Product development" defaultCityName = objCityEntry.Name Set objCityEntry = objLookupTable.AddChild("Kirkland", _ objCountyEntry.UniqueID) objCityEntry.Description = "Sales offices" EditLocationLookupTable = defaultCityName End Function
All text fields support from 1 to 250 values. The Project 2007 object model also enables localized lookup table entries, with the LookupTableEntry.Cookie and LookupTableEntry.LocalizedCookie properties.
Programming with the VBA object model supports the Project 2007 changes in functionality, as shown in Figures 1 and 3. Changes in custom fields include the following:
All field types (cost, date, duration, flag, number, start, finish, text, and outline code) have a code mask. The code masks for all fields except outline code are single-level and cannot be edited. The default mask for outline code fields is a single-level "Characters, Any" and you can create multilevel masks for outline codes, as you can in Project 2003.
A default entry can be set for the field.
Display order can be set to be user-defined, or sorted descending as well as ascending.
You can no longer create new local custom fields; you can still select a predefined field, rename it, and edit its properties.
Server custom fields show in the same Custom Fields dialog box, but are now read-only. You can see all of the local and server custom fields but you cannot rename server custom fields or edit their properties using VBA in Project Professional. You can create and manage an essentially unlimited number of enterprise custom fields through Project Web Access or programmatically with the PSI.