Highlight project tasks based on custom field value / Increase macro performance


  • Dear forum,

    my VBA macro checks the value of a calculated custom number field (Number1) and sets the color of the name column in the tasks row:

    Sub SetHighlighting()
    Application.ScreenUpdating = False
    Application.Calculation = pjManual
    Application.StatusBar = False
    Application.DisplayAlerts = False
    Dim t As Task
    For Each t In ActiveProject.Tasks()
        If Not t Is Nothing Then
        	If (t.Summary = No) And (t.Number1 <> 1) Then
        		SelectTaskCell Row:=t.ID, Column:="Name", rowrelative:=False
        		Select Case t.Number1
        			Case 0: Font32Ex CellColor:=vOrange
       			Case 2: Font32Ex CellColor:=vGrey
        			Case 3: Font32Ex CellColor:=vGreen
        		End Select
        	End If
        End If
    Next t
    Application.DisplayAlerts = True
    Application.StatusBar = True
    Application.Calculation = pjAutomatic
    Application.ScreenUpdating = True
    End Sub

    (*color values are constants set outside of the sub)

    As my project is approaching ~200 rows, execution of the macro takes like ~20 seconds. Already applied some optimization approaches. However, I'd like to check with you if (a) row property cellcolor can be accessed more efficiently or if (b) accessing the number1-field value is to costly and value calculation should be directly done in VBA (basically check if task is completed, finish date is in the past, in the next seven days or ahead:

    IIf([% Complete]<100;IIf([Finish]<Now();0;(IIf([Finish]<Now()+7;3;1)));2)

    Looking forward to learning about your insights and recommendations!

    Kind regards 


    Selasa, 04 Desember 2018 22.06

Semua Balasan

  • Leo,

    Is this a macro run outside of Project (e.g. VSTO, etc.)? If so, that's probably why it's slow.

    I just ran your macro (using a hexadecimal representation for the colors) on a file with 182 task lines. It ran in .265 seconds. When I removed the Do Events line, (I don't understand why it's even in there), the run time was .25 seconds.

    I'd do this. Run the code directly in Project and set up your colors using hexadecimal (i.e. no external color definition). You can add the calculation for the Number1 field to the code, but I don't expect that to have much effect on execution.

    For reference Project is not as flexible as Excel when it comes to font manipulation. Cell font colors in Project can only be accessed using foreground processing (i.e. selecting a cell in the current view) and foreground processing is generally always slower than background processing (i.e. working directly on Project objects independent of the current view).

    Hope this helps.


    Selasa, 04 Desember 2018 23.14
  • Dear John,

    thank you for your quick reply.

    The macro is implemented directly in VBA (Alt+F11 from application). Strangely, on my end the execution is way longer...the DoEvents is included to prevent the application from going to (Not Responding) after few seconds of execution. The colors have been translated to hex codes. Performance remained unchanged, maybe this is due to the project plan I'm working with...might be corrupted. I'll be looking a bit more into that.

    To reduce calculation effort I've added steps to copy calculated value of field number1 in field number2, highlighting macro then checks if value of number1 is different to number2 (change since last execution of highlighting macro) and only then executes cell highlighting, hence updating.

    Kind regards


    Rabu, 05 Desember 2018 16.13
  • Leo,

    There is nothing inherently wrong with your code. You should not need to transfer your calculated Number1 field to another field, it will not reduce any calculation time and won't run any faster.

    Maybe something is wrong with your file. Try saving your file to XML. Then re-open the XML file in Project. Note, you will lose any custom formatting with this process.

    Does the "cleaned" file work any better?

    If you can share you file with me, I'd be happy to take a look at it. Let me know.


    Kamis, 06 Desember 2018 01.44
  • To significantly speed up your code, create a filter to filter all tasks where Number1=1. Then set the color for all of them at the same time. Then filter for all Number1=2 etc. Will work much, much faster.

    Rod Gill
    Author of the one and only Project VBA Book and VBA developer.

    Kamis, 13 Desember 2018 08.41