PRJ4: Sorting Fields That Contain Lists

MORE INFORMATION

The following macro sorts the resource names assigned to every task in the active project and places the sorted list in the task's Text10 field. To see the results of running the macro, add the Text10 field to a task table in the Gantt Chart view.

The sample code in this macro illustrates how to:

• Parse items from a list field into an array.
• Sort items in an array.

The following sample code uses a simple sorting algorithm.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

Creating the Macro

To create this macro, do the following:

1. From the Tools menu, choose Macros.
2. Choose New to bring up the New Macro dialog box.
3. In the Macro Name field, type SortResourceNames, and then choose OK.
4. In the Module Editor view, enter the code for the procedure that follows, exactly as shown.

      Sub SortResourceNames()
      ' This sub does a simple 'bubble sort' on the
      ' resource names field and places the resulting
      ' alphabetized list into the task's Text10 field.
   
      Dim tskIndex As Task        ' Task object
      Dim nNumNames As Integer    ' Name counter
      Dim sResNames As String     ' Name list
      Dim sName As String         ' Single name
      Dim sLS As String           ' List separator
      Dim asAlpha() As String     ' Alphabetized list
      Dim i As Integer            ' Loop counter
      Dim j As Integer            ' Loop counter
   
      ' Save list separator
      sLS = ListSeparator
   
      ' Loop through all project tasks
      For Each tskIndex In ActiveProject.Tasks
         ' Ignore blank tasks
         If Not (tskIndex Is Nothing) Then
           ' Clear the Text10 field
           tskIndex.Text10 = ""
           ' Save resource names in variable
            sResNames = tskIndex.ResourceNames
           ' Add list separator for parsing
           sResNames = sResNames & sLS
           ' Initialize counter
            nNumNames = 0
           ' Repeat until all names parsed from list
         Do While Len(sResNames) > 1
            ' Count number of names parsed
            nNumNames = nNumNames + 1
            ' Get next name from list
            sName = Left(sResNames, InStr(sResNames, sLS) - 1)
            ' Resize array to hold new value
            ReDim Preserve asAlpha(1 To nNumNames)
            ' Put name in array
             asAlpha(nNumNames) = sName
             ' Remove name from list
             sResNames = Mid(sResNames, Len(sName) + 2)
         Loop
         End If
   
        ' Now sort the list in the array
         For i = 1 To nNumNames
            For j = 1 To nNumNames - 1
            ' Compare alphabetically, ignoring case
               If LCase(asAlpha(j)) > LCase(asAlpha(j + 1)) Then
                  ' Switch positions
                  sName = asAlpha(j + 1)
                  asAlpha(j + 1) = asAlpha(j)
                  asAlpha(j) = sName
               End If
            Next j
         Next i
   
         ' Place sorted list into Text10 field
         sResNames = ""
         For i = 1 To nNumNames
            ' If necessary, add list separator
            If i > 1 Then sResNames = sResNames & sLS
               sResNames = sResNames & asAlpha(i)
         Next i
         tskIndex.Text10 = sResNames
         Next tskIndex
   
      End Sub
   					

Running the Macro

To use this macro, do the following:

1. From the Tools menu, choose Macros.
2. From the list of macros, select SortResourceNames, and then choose Run.
3. Apply a task table containing the Text10 field to see the results.

Modifying the Macro

To sort a field other than Resource Names, replace the reference to ResourceNames in the macro with the name of the field you want to sort. For instance, if you want to sort Resource Initials, you would change the following line to read:

   sResNames = tskIndex.ResourceInitials