how can i improve loop performance when using project macro?

Question

As you can read from the below VBA codes for Project2013, the loop of "for" takes about 50-80 seconds to complete when the number of tasks comes to more than 1000. How can i improve the performance? Is there an "array" method like excel VBA? thanks for your help!

Sub Change_Color_By_Task_Status()


' Expand all sub tasks
    SelectSheet
    OutlineShowAllTasks
    SelectTaskField Row:=1, Column:="Name"

' Clear all fields color
    SelectSheet
    FontEx CellColor:=16
    SelectTaskField Row:=1, Column:="Name", RowRelative:=False


Dim tskt As Task
For Each tskt In ActiveProject.Tasks
If Len(tskt.Name) > 0 Then
  If Not tskt Is Nothing Then  
  If Not tskt.ExternalTask Then
    If Not tskt.Summary Then    
        Select Case tskt.Text1
            Case "Complete"
                SelectRow Row:=tskt.ID, RowRelative:=False
                'Font Color:=pjBlack
                FontEx CellColor:=pjGray
            Case "Yellow"
                SelectRow Row:=tskt.ID, RowRelative:=False
                'Font Color:=pjBlack
                FontEx CellColor:=pjYellow
            Case "Green"
                SelectRow Row:=tskt.ID, RowRelative:=False
                'Font Color:=pjBlack
                FontEx CellColor:=pjWhite
            Case "Red"
                SelectRow Row:=tskt.ID, RowRelative:=False
               'Font Color:=pjRed
               FontEx CellColor:=pjRed
            Case "Overdue"
                SelectRow Row:=tskt.ID, RowRelative:=False
               Font Color:=pjWhite
               Font32Ex CellColor:=192
        End Select
     End If 
     End If  
    End If  
    End If
Next tskt

End Sub

Answer 1

I have found it is much quicker to use ms project built-in filters when working with the interface.

Have project add columns for External Task, Summary, and Text1. Then use Application.SetAutoFilter to filter out Summary=yes, ExternalTak=Yes, then filter out each Text1, SelectAll and set your formatting. Something like this:

Sub Change_Color_By_Task_Status()

    'Add columns to filter
    TableEditEx Name:="&Entry", TaskTable:=True, NewName:="", NewFieldName:="Text1", ColumnPosition:=0
    TableApply Name:="&Entry"
    TableEditEx Name:="&Entry", TaskTable:=True, NewName:="", NewFieldName:="External Task", Title:="", ColumnPosition:=0
    TableApply Name:="&Entry"
    TableEditEx Name:="&Entry", TaskTable:=True, NewName:="", NewFieldName:="Summary", Title:="", ColumnPosition:=0
    TableApply Name:="&Entry"

    'Filter out summaries and externals
    SetAutoFilter FieldName:="External Task", FilterType:=pjAutoFilterFlagNo
    SetAutoFilter FieldName:="Summary", FilterType:=pjAutoFilterFlagNo

    'Filter by Text1
    'for "Complete"
    SetAutoFilter FieldName:="Text1", FilterType:=pjAutoFilterCustom,     Test1:="equals", criteria1:="Complete"
    SelectAll
    '[Apply complete formatting]
    SetAutoFilter FieldName:="Text1", FilterType:=pjAutoFilterClear

    '... repeat for the other Text1 values

    'clear filters
    SetAutoFilter FieldName:="External Task", FilterType:=pjAutoFilterClear
    SetAutoFilter FieldName:="Summary", FilterType:=pjAutoFilterClear

    'Remove columns
    SelectTaskColumn Column:="Text1"
    ColumnDelete
    SelectTaskColumn Column:="Summary"
    ColumnDelete
    SelectTaskColumn Column:="External Task"
    ColumnDelete
End Sub

Hope this speeds it up