Project 2000’s custom value lists enable you to limit what values will be used in particular fields by predefining available values. This feature can be a great help for organizations that need to report across projects on values held in custom fields.

Microsoft has provided several new methods in VBA for dealing with these value lists via code. This article will present a macro that shows how you can return values from a list and add and delete items. It also demonstrates how to create your macros so that they can stand alone and be called as is from other macros.

Macro preliminaries
The key to developing stand-alone code lies in the use of arguments. Arguments are basically variables that the macro needs in order to do its work. But instead of dimensioning the variables using Dim statements, you declare them in the Sub statement. When you call this macro from your other macros, you define the argument (variable) values in the line of code you use to call the macro. This allows you to pass these values into the macro and use them as if you had declared them like traditional variables.

This technique makes it easy to use the code in your other macros. And because you’re passing values via the arguments instead of editing the macro, there’s less room for error.

Note that you can’t run this macro on its own. This kind of macro requires you to call it from a second macro. For example, our sample macro will need to be called from a macro like this one:
Sub CallListValues()
 
List_Values Field:=188743731, Value:=”Three3″, _
AddDelete:=”Delete”, UpTo:=100, ValueIndex:=3
 
End Sub

Our macro is called List_Values, and it accepts four required arguments and one optional argument. The required arguments are:

  • ·        Field
    This needs to be the nine-digit custom field ID that represents the custom field whose list you want to act upon. We will cover later where to find this number.
  • ·        Value
    This is a string value that is the value you either want to add or delete from the custom value list.
  • ·        AddDelete
    This string should either be “Add” or “Delete” and it tells the List_Values macro what you want to do with the value.
  • ·        UpTo
    This is a number from 1 to 5,000. It tells the macro how many values to search for in the list. A list can have up to 5,000 values, but you might know that your list only has 10 values so searching up to 5,000 every time is unnecessary.

The optional argument is

  • ·        ValueIndex
    This is a number that represents where in the list you would like to add the value. It is needed only if the AddDelete argument is “Add”.

    • VBA makes it easy to include the proper arguments. When you enter the code that calls your macro, the Visual Basic Editor will present argument prompts, as shown in Figure A. Notice how the prompts show you what arguments need to be inserted and their data type. This can be a great benefit when you are trying to remember what arguments need to be passed to the macro.

    Figure A
    Argument prompts in VBA

    The macro
    Below, you can see the macro itself. We’ve included line numbers for discussion purposes, but to use the code, you must copy it into your project without the numbers. Listing A provides a number-free version of the macro.

    1. ·        Sub List_Values(Field As Long, Value As String, AddDelete As String, _
    2. ·        UpTo As Long, Optional ValueIndex As Long)
    3. ·         
    4. ·        Dim lngListNumber As Long
    5. ·        Dim lngFoundAt As Long
    6. ·        Dim strListValue As String
    7. ·         
    8. ·        Dim blnValueFound As Boolean
    9. ·        Dim blnListFound As Boolean
    10. ·         
    11. ·        On Error GoTo Errorhandler
    12. ·         
    13. ·        If AddDelete <> “Add” And AddDelete <> “Delete” Then
    14. ·        MsgBox Prompt:=”The AddDelete argument must be either ‘Add’ or ‘Delete'”, _
    15. ·        buttons:=vbExclamation, Title:=”List Values Macro Error”
    16. ·         
    17. ·        Exit Sub
    18. ·        End If
    19. ·         
    20. ·        If AddDelete = “Add” And ValueIndex < 1 Or ValueIndex > 5000 Then
    21. ·        MsgBox Prompt:= _
    22. ·        “If AddDelete is ‘Add’ then you must provide a ValueIndex value between 1 and 5000”, _
    23. ·        buttons:=vbExclamation, Title:=”List Values Macro Error”
    24. ·         
    25. ·        Exit Sub
    26. ·        End If
    27. ·         
    28. ·        For lngListNumber = 1 To UpTo
    29. ·        strListValue = CustomFieldValueListGetItem(FieldID:=Field, _
    30. ·        Item:=pjValueListValue, Index:=lngListNumber)
    31. ·         
    32. ·        If strListValue = Value Then
    33. ·        blnValueFound = True
    34. ·        lngFoundAt = lngListNumber
    35. ·        ‘Holds the position of the found value so it can be deleted
    36. ·        GoTo LoopOut
    37. ·        ‘Takes us out of the loop because the value has been found
    38. ·        End If
    39. ·         
    40. ·        blnListFound = True
    41. ·        ‘Lets the macro know that a list was found
    42. ·         
    43. ·        Next lngListNumber
    44. ·        LoopOut:
    45. ·         
    46. ·        ‘If the Add or Delete value is Add and the blnValueFound flag
    47. ·        ‘is False then this code will add the value at the index value
    48. ·        ‘specified in the ValueIndex variable
    49. ·        If AddDelete = “Add” Then
    50. ·        If blnValueFound = False Then
    51. ·        CustomFieldValueListAdd FieldID:=Field, Value:=Value, _
    52. ·        Index:=ValueIndex
    53. ·        End If
    54. ·         
    55. ·        ‘if the AddDelete value is Delete and the blnValueFound is True
    56. ·        ‘then this code will delete the value from the list at the index value
    57. ·        ‘of the lngFoundAt variable
    58. ·        ElseIf AddDelete = “Delete” Then
    59. ·         
    60. ·        If blnValueFound = True Then
    61. ·        Application.CustomFieldValueListDelete FieldID:=Field, Index:=lngFoundAt
    62. ·        End If
    63. ·         
    64. ·        End If
    65. ·         
    66. ·        ‘Start Error Handler Section
    67. ·        Exit Sub
    68. ·        Errorhandler:
    69. ·        ‘Error number 1004 will be thrown when you try to assign a variable the value
    70. ·        ‘of a value list value that does not exist. This happens on the next Index number
    71. ·        ‘after the last value in the list. The handler will check to see if the
    72. ·        ‘blnListFound variable is True. If it is then we know that the error is because
    73. ·        ‘the Last value was past. If it is False then the error is because there is not a
    74. ·        ‘List associated with the field specified
    75. ·        If Err.Number = 1004 Then
    76. ·        If blnListFound = True Then
    77. ·        GoTo LoopOut
    78. ·        Else
    79. ·        MsgBox Prompt:=”No value list was found for this field”, _
    80. ·        buttons:=vbCritical, Title:=”Value List Macro Error”
    81. ·        End If
    82. ·         
    83. ·        Else
    84. ·        MsgBox Prompt:=”Error: ” & Err.Description, buttons:=vbCritical, _
    85. ·        Title:=”Value List Macro Error”
    86. ·        End If
    87. ·         
    88. ·        End Sub

     

    Breakdown of macro
    Lines 1 and 2 define the name of the subroutine and the arguments it needs to do its work. Lines 4 through 9 declare the other variables that will be used by the macro. Line 11 sets the error trap so that if the macro hits an error, we can send the execution to an error-handling routine at the end of the macro.

    Lines 13 through 26 examine the arguments and will end the macro if they’re incorrect. Lines 28 through 43 look at the Value List for the Custom field specified in the Field argument. If it finds the value, the macro sets a flag saying it was found and then stores the position of the value within the list so that if AddDelete is set to “Delete”, it can remove the value.

    Line 44 is a bookmark used by the macro’s error-handling routine. Lines 46 through 64 do the actual work of adding or deleting the list value. Lines 49 through 53 look at the AddDelete argument. If AddDelete is set to “Add”, the macro adds the value specified in the Value argument at the position specified in the argument ValueIndex. The macro uses the CustomFieldValueListAdd method to do this. This method accepts several arguments, but for our purposes, we are using only three: FieldID, Value, and Index. Notice that these are some of the same arguments we pass to the macro. The CustomFieldValueListAdd method adds the Value to the List for the field specified in the argument Field at the location in the list specified in the ValueIndex argument.

    Lines 60 through 62 handle the deleting if the value of AddDelete is set to “Delete”. The macro does this using the CustomFieldValueListDelete method, which accepts two arguments, FieldID and Index. We get FieldID from our Field argument, and we get Index from the value we stored in the variable lngFoundAt back in line 34.

    Lines 66 through 86 are the Error Handler section. Line 68 is the actual bookmark location, but on Line 67, we insert the Exit Sub statement to make sure that the code that follows does not execute unless there is an error. This error-handling routine looks at the number of the error that occurred. If that value is 1004, we know one of two things:

    • ·        If the value of the variable blnListFound (as set back on line 40) is True, the error occurred because the loop that starts on line 28 has hit the end of the actual number of values in the list.
    • ·        If the blnListFound variable is False, no list was found in the Field specified in the Field argument.

    The handler displays a more generic message for any other error numbers.

    How do I find the FieldID value for my field?
    There is a great, easy way to find the nine-digit FieldID number that’s needed to pass into our macro in the Field argument: Just use the Object Browser in Project 2000’s Visual Basic Editor.

    From inside the editor, click on the View menu and select Object Browser. Then, in the Search Text field of the Browser, enter (as one word) the name of the field you want to use. For example, for Task Text1, enter tasktext1; for Resource Cost1, enter resourcecost1. Then, click on the Search button. Figure B shows an example.

    Figure B
    Determining the FieldID number

    In the Search Results section, find the field you want in the Member field. It will have pjCustom in front of it. When you select it, the nine-digit number will appear in the lower section of the Object Browser. For instance, in Figure B, the number appears after pjCustomTaskText1 = and is 188743731. Use this number for the Field argument when calling the List_Values macro.

    The List_Values macro is not a complex one, but it does illustrate a couple of good points.

    First, it shows how to work with list values programmatically in VBA and lets us see some of the possibilities available to us in maintaining projects that use these lists. Second, and I think, more importantly, it shows how easy it is to design your code to stand alone. This article introduced the concept of using arguments to make your code more reusable. While working with list values is a good take-away, knowing how to use arguments in your code will make all your coding efforts more valuable to you and your organization.

    Know any good macros for Microsoft Project?

    Are there macros for MS Project 2000 that help you with your work? Care to share them with us? Send them to TechRepublic.