PRJ: Macro to Sum Values in Filtered List

Last reviewed: November 24, 1997
Article ID: Q124482
The information in this article applies to:
  • Microsoft Project 98 for Windows
  • Microsoft Project for Windows 95, versions 4.1, 4.1a
  • Microsoft Project for Windows, version 4.0
  • Microsoft Project for the Macintosh, version 4.0

SUMMARY

The Microsoft Project sample macro in the "More Information" section of this article sums numeric values from a list of visible tasks and displays the total in a task row at the bottom of the list.

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following macro sums the values from the Number1 fields of all visible tasks. After you run this macro, a new task named "Total" will be created and displayed at the end of the active project. The Number1 field in this task will contain the sum of the Number1 values for all other visible tasks.

Microsoft Project 4.x

  1. On the Tools menu, click Macros.

  2. Click New.

  3. In the Macro Name field, type "SumFilteredList" (without the quotation marks). Click OK.

  4. In the Module Editor, enter the following code:

          Sub SumFilteredList()
          ' This macro totals up the values in the Number1 field
          ' for all visible (filtered) tasks, and displays this total
          ' at the bottom of the task list.
    
          Dim oTask As Task          ' Current task pointer
          Dim oTotalTask As Task     ' Totals task pointer
          Dim fTotal As Single       ' Used to hold totals
    
             ' Ignore error generated by trying to delete a task
             ' that does not exist.
             On Error Resume Next
             ' If totals task is present, delete it.
             ActiveProject.Tasks("Total:").Delete
             ' Re-enable default error handling
             On Error GoTo 0
    
             ' Select visible tasks
             SelectAll
             ' If tasks are selected, then loop through each of them
             If Not (ActiveSelection.Tasks Is Nothing) Then
                For Each oTask In ActiveSelection.Tasks
                    ' Add Number1 for each task to total
                   fTotal = fTotal + oTask.Number1   ' Field to Sum
                Next oTask
             Else
                ' There's nothing to add
                Exit Sub
             End If
    
              ' Create Totals task so it's visible
             Set oTotalTask = ActiveProject.Tasks.Add("Total:")
    
             ' Insure "Totals:" task is at outline level 1 (not indented)
             Do Until oTotalTask.OutlineLevel = 1
                  oTotalTask.OutlineOutdent
             Loop
    
             ' Put total in proper field
             oTotalTask.Number1 = fTotal             ' Field containing total
    
             ' Hide task bar for this task
             oTotalTask.HideBar = True
             ' Select totals row
             SelectRow oTotalTask.ID, False
             ' Set font to bold
             FontBold Set:=True
    
          End Sub
    
    
To use this macro, do the following:

  1. Select a task view that uses a table, such as the Gantt Chart.

  2. Display only the tasks you want to calculate a total for either by applying a filter or by hiding or showing subtasks.

  3. On the Tools menu, click Macros.

  4. In the Macro list, select SumFilteredList, and then click Run.

Microsoft Project 98

  1. On the Tools menu, point to Macro, and then click Macros.

  2. In the Macro Name field, type "SumFilteredList" (without the quotation marks). Click Create.

  3. In the Visual Basic Editor, enter the following code:

          Sub SumFilteredList()
          ' This macro totals up the values in the Number1 field
          ' for all visible (filtered) tasks, and displays this total
          ' at the bottom of the task list.
    
          Dim oTask As Task          ' Current task pointer
          Dim oTotalTask As Task     ' Totals task pointer
          Dim fTotal As Single       ' Used to hold totals
    
             ' Ignore error generated by trying to delete a task
             ' that does not exist.
             On Error Resume Next
             ' If totals task is present, delete it.
             ActiveProject.Tasks("Total:").Delete
             ' Re-enable default error handling
             On Error GoTo 0
    
             ' Select visible tasks
             SelectAll
             ' If tasks are selected, then loop through each of them
             If Not (ActiveSelection.Tasks Is Nothing) Then
                For Each oTask In ActiveSelection.Tasks
                    ' Add Number1 for each task to total
                   fTotal = fTotal + oTask.Number1   ' Field to Sum
                Next oTask
             Else
                ' There's nothing to add
                Exit Sub
             End If
    
              ' Create Totals task so it's visible
             Set oTotalTask = ActiveProject.Tasks.Add("Total:")
    
             ' Insure "Totals:" task is at outline level 1 (not indented)
             Do Until oTotalTask.OutlineLevel = 1
                  oTotalTask.OutlineOutdent
             Loop
    
             ' Put total in proper field
             oTotalTask.Number1 = fTotal             ' Field containing total
    
             ' Hide task bar for this task
             oTotalTask.HideBar = True
             ' Select totals row
             SelectRow oTotalTask.ID, False
             ' Set font to bold
             FontBold Set:=True
    
          End Sub
    
    
To use this macro, do the following:

  1. Select a task view that uses a table, such as the Gantt Chart.

  2. Display only the tasks you want to calculate a total for either by applying a filter or by hiding or showing subtasks.

  3. On the Tools menu, point to Macro, and then click Macros.

  4. In the Macro list, select SumFilteredList, and then click Run.

Modifying the Macro

To sum values from fields other than Number1, replace the reference to Number1 on the line commented with "Field to sum" with the name of the field for which you want to derive a total. To place the total in a field other than Number1, replace the reference to Number1 on the line commented with "Field containing total" with the name of the field into which you actually want to place the total.

For example, if the field you want to sum is a calculated field, such as Cost or Actual Cost, then the sum must be placed in a non-calculated field, such as Cost1 or Number1. Therefore, to get the total cost fields for all visible tasks and place the total in the Cost1 field of the "Total" task, you would change the indicated lines as follows:

   fTotal = fTotal + oTask.Cost   ' Field to Sum
   oTotalTask.Cost1 = fTotal      ' Field containing total


Additional query words: 4.00
Keywords : kbcode kbprg
Version : 4.0 4.1 4.1a 98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.