Microsoft KB Archive/127170

= PRJ4: Sorting Fields That Contain Lists =

Article ID: 127170

Article Last Modified on 10/7/2003

-

APPLIES TO


 * Microsoft Project 4.1 Standard Edition
 * Microsoft Project 4.1a
 * Microsoft Project 4.0 Standard Edition

-



This article was previously published under Q127170



SUMMARY
In Microsoft Project, every task in a project is supplied with three different list fields that display information about the resources assigned to the task. These fields are Resource Names, Resource Initials, and Resource Group. Although these fields do not present the resource information in a sorted list, it is possible to create a macro that will sort the list and place it in a different task field.



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:

 From the Tools menu, choose Macros. Choose New to bring up the New Macro dialog box. In the Macro Name field, type SortResourceNames, and then choose OK.  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

Additional query words: Bubble

Keywords: kbhowto kbmacro kbcode kbusage KB127170

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.