none
Shorten Macro Run Time RRS feed

  • Question

  • I have a simple macro that works fine.  It does what it needs to do.  My problem is the file has grown in size from a few hundred tasks to near 2500 tasks.  (using MSProject Professional 2010 - Win 7)  When the file was small the macro completed within a few minutes.  Now the run time is up over a hour or more.  (I'm not an IT person)

    Is there any way I can modify it to shorten the run time?  Are there any background defaults in project that can be turned off that might speed up the process?

    Sub FlagSelTsks()

    Dim t As Task
    Dim c As Task
    For Each t In ActiveProject.Tasks
    t.Flag7 = t.Summary
        If Not t Is Nothing Then
            If t.Summary And t.PercentComplete = 100 Then
                t.Flag7 = False
            End If
            If Not t.Summary Then
            t.Flag7 = t.OutlineParent.Flag7
            End If
        End If
    Next t

    End Sub

    Thank you,

    Steve

    Monday, December 16, 2013 1:53 PM

Answers

  • I've tidied it up as best I could without changing the logic - hard to know whether this is the most efficient way of doing this without knowing what you're trying to achieve, but this code at least has fewer if statements:

    Sub FlagSelTsks()
    
    Dim t As Task
    
    Application.Calculation = pjManual
    
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Flag7 = t.Summary
            If t.Summary Then
                If t.percentComplete = 100 Then
                    t.Flag7 = False
                End If
            Else
                t.Flag7 = t.OutlineParent.Flag7
            End If
        End If
    Next t
    
    Application.Calculation = pjAutomatic
    Application.CalculateProject
    
    End Sub

    .

    Let me know if that helps,

    Andrew

    • Marked as answer by Mrcvn Monday, December 16, 2013 11:23 PM
    Monday, December 16, 2013 2:41 PM

All replies

  • I've tidied it up as best I could without changing the logic - hard to know whether this is the most efficient way of doing this without knowing what you're trying to achieve, but this code at least has fewer if statements:

    Sub FlagSelTsks()
    
    Dim t As Task
    
    Application.Calculation = pjManual
    
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Flag7 = t.Summary
            If t.Summary Then
                If t.percentComplete = 100 Then
                    t.Flag7 = False
                End If
            Else
                t.Flag7 = t.OutlineParent.Flag7
            End If
        End If
    Next t
    
    Application.Calculation = pjAutomatic
    Application.CalculateProject
    
    End Sub

    .

    Let me know if that helps,

    Andrew

    • Marked as answer by Mrcvn Monday, December 16, 2013 11:23 PM
    Monday, December 16, 2013 2:41 PM
  • Steve,

    Your macro is indeed very simple and except for having the "If Not t is Nothing Then" in the wrong place, this macro should run lightning fast even on a very large file, so there is obviously something else going on. The fact that it took a "few minutes" when the file was small is an indication of a problem.

    Try this experiment. Create a small file of 50 tasks or so with a sprinkling of summary lines and subtasks. Tweak your code by adding a timer as shown below:

    Sub FlagSelTsks()

    Dim t As Task
    Dim c As Task
    Dim tim As Single
    tim = Timer
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Flag7 = t.Summary
            If t.Summary And t.PercentComplete = 100 Then
                t.Flag7 = False
            ElseIf Not t.Summary Then
                t.Flag7 = t.OutlineParent.Flag7
            End If
        End If
    Next t
    MsgBox "Runtime = " & Timer - tim & " secs", vbInformation

    End Sub

    The message presented after the macro runs should show an extremely small number. I ran the above code on a sample file with 188 tasks. The runtime was .078125 secs. The second time it ran even faster at .0625 secs and the third time faster yet. The runtime reaches a plateau but consecutive runs do execute faster for some reason.

    John

    Monday, December 16, 2013 4:31 PM
  • Andrew,

    Perfect...  That did the trick.  Turning the background calculation function off freed up the macro to simply update the flag field.  I have run both your and mine three times now with perfect matches each time.  My version runs for about 55 minutes.  Your version does it in about 5 seconds. 

    Many thanks,

    Steve 

    Monday, December 16, 2013 11:11 PM
  • John,

    Thank you for help.  It did not speed up the macro.  It ran about the same amount of the time, 44 minutes the first time and 41 minutes the second time.  It appears the solution is to set projects calculation function to manual mode first to allow the macro to update.  When doing that way it reduces the time down to about 4-5 seconds.

    Many thanks for taking your time to helpout.

    Steve

    Monday, December 16, 2013 11:22 PM
  • Steve,

    I'm a little surprised that temporarily turning calculation off made a difference. Execution of your code should not cause any kind of re-calculation since it is only setting a benign flag field. Nonetheless, the proof is in the pudding and I'm glad you found a viable approach.

    John

    Tuesday, December 17, 2013 2:13 AM
  • Hi John,

    I believe if there is a formula driven off the flag field then updating its value would cause a recalculation. I thought that may have been the case in Steve's scenario.

    Thanks,

    Andrew

    Tuesday, December 17, 2013 8:00 AM
  • Andrew,

    There is no formula in this flag field.  However, I do have a number field that does have an IIf() formula that compares the [Schedule Finish] date to NOW() and shows Graphical Indicators for 100% [% Work Complete], tasks overdue, tasks due within 8 weeks and tasks due within 16 weeks.

    Steve

    Tuesday, December 17, 2013 3:48 PM
  • Andrew,

    Yeah, I thought about that afterward. Those who post questions don't always tell the whole story.

    The bottom line is that you covered that possibility and solved the poster's issue. Good job. When I develop complex macros I always turn calculation off at the onset and then turn it back on on close, unless there is a driving reason to get calculation updates during the execution of the macro. I just didn't think about it in this case. My bad.

    John

    Tuesday, December 17, 2013 3:54 PM