Painfully Slow Macros

[Bob Inwater]

I maintain a 1000 plus line schedule for Fortune 500 Company that uses a
formatting macro to color code and underline tasks in the Gantt. The code is
pretty standard stuff line “ for each task in active project…..If behind
plan format Bold Red if 100% Bold Grey and suchâ€.

The problem is the macro now takes up to ten minutes to cycle though. Any
ideas on speeding this up?


Code Snip below : (Text29 holds a If then statment for a number of conditions)

If t.Number1 = 100 Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjGray

ElseIf t.Text29 = " Condition 1" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjRed

ElseIf t.Text29 = "Condition 2" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjRed

ElseIf t.Text29 = " Condition 3" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjGreen

ElseIf t.Text29 = " Condition 4" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjNavy

ElseIf t.Text29 = "Condition 5" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjPurple
End If

 

[JackD]

The way to speed it up is to drop the formatting and use a custom field with
an indicator instead. It will be almost instantaneous and will be visible
and correct at all times instead of only being accurate at the time the
macro was run. Another advantage is you can choose different shapes for the
indicators so that even when printed in black and white, the various
conditions can be identified.

Look here for some basic guidelines on when they should be used:
http://masamiki.com/project/macvcalc.htm

And here for some common issues people have getting started:
http://masamiki.com/project/customfieldFAQ.htm

You can make your code faster. Create and apply a filter for the conditions,
then select all and apply formatting, repeat as necessary for the 5
combinations you have listed below. But the simple answer is just don't
format the text. It is a poor practice.

 

[Jan De Messemaeker]

Hi Bob,

I vaguely remember having tried the following a few years ago:
Instead of straightforward formatting, for all tasks having some format put
a value in say Number7
Then filter on That value in number7
SelectAll
Now apply the formatting instruction.

HTH

 

[Bob Inwater]

Thanks Jack,
Sadly, I really can not ditch the color coding. The engineers I am working
with hate the schedule as it is… if they had to understand symbols/indicators
I think their brains would explode. Guess I will keep doing what I am doing
for now.ïŠ

FYI - I love your web site and have been referencing it ever since I left
the dark world of P3.

Bob

 

[JackD]

I work with engineers too. They understand what a green happy face or a red
flag means better than they understand what purple underlined text means.
I'd work on making them happy with the schedule first though.

 

[Earl Lewis]

Bob,

I haven't tested this but just looking at your code I think what's slowing your macro down are the SelectRow statements. These cause the UI to become engaged in the processing of the macro and I believe is unnecessary. I'll have to look back at a formatting macro I did a few months back.

In the meantime, you could just try commenting out the SelectRow line and see what happens - on a TEST PLAN.

Earl
I maintain a 1000 plus line schedule for Fortune 500 Company that uses a
formatting macro to color code and underline tasks in the Gantt. The code is
pretty standard stuff line “ for each task in active project…..If behind
plan format Bold Red if 100% Bold Grey and such”.

The problem is the macro now takes up to ten minutes to cycle though. Any
ideas on speeding this up?


Code Snip below : (Text29 holds a If then statment for a number of conditions)

If t.Number1 = 100 Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjGray

ElseIf t.Text29 = " Condition 1" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjRed

ElseIf t.Text29 = "Condition 2" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjRed

ElseIf t.Text29 = " Condition 3" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjGreen

ElseIf t.Text29 = " Condition 4" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjNavy

ElseIf t.Text29 = "Condition 5" Then
SelectRow Row:=r, rowrelative:=False
Font Color:=pjPurple
End If

 

[Lars Hammarberg]

I'd guess creating a filter (or five) and applying the formatting on all
tasks in that filtered selection at once would speed things up considerably.