Asna Jadeja
asked on
Optimize MS Project VBA Macro
I have a macro that I am currently running to remove tasks from a project plan that a) have zero work effort and b) aren't key milestone tasks, which is a custom field that we have added.
At the time that the macro runs, there are approximately 1850 rows in the project plan that the macro executes againt to remove the tasks, and the current run time can be upwards of 20 minutes (on average, about 15 minutes). While the macro will successfully execute each time, I am looking for ways to optimize the code to reduce run time. From the research I've done to date, I am not able to figure out a way to modify the code. I explored whether it would make sense to build a progress bar (or make use of the status bar) to inform the user of the macro progress, but as I understand it, the addition of this functionality, will further slow down the code.
Any help anyone can offer would be MUCH appreciated - thank you!
Sub DeleteMsProjectTask()
DeleteTasks ActiveProject
Dim sp As Subproject
For Each sp In ActiveProject.Subprojects
DeleteTasks sp.SourceProject
Next sp
MsgBox ("Done")
End Sub
Sub DeleteTasks(prj As Project)
Application.ScreenUpdating = False
Dim NumTasks As Integer
NumTasks = prj.Tasks.Count
Dim t As Task
Dim mileTsk As String
Do While NumTasks > 0
Set t = prj.Tasks(NumTasks)
If t.OutlineLevel > 1 And t.Work = 0 Then
mileTsk = t.GetField(FieldNameToFiel dConstant( "Key Milestone?", pjTask))
If mileTsk = "No" Then
t.Delete
End If
End If
NumTasks = NumTasks - 1
Loop
Application.ScreenUpdating = True
End Sub
At the time that the macro runs, there are approximately 1850 rows in the project plan that the macro executes againt to remove the tasks, and the current run time can be upwards of 20 minutes (on average, about 15 minutes). While the macro will successfully execute each time, I am looking for ways to optimize the code to reduce run time. From the research I've done to date, I am not able to figure out a way to modify the code. I explored whether it would make sense to build a progress bar (or make use of the status bar) to inform the user of the macro progress, but as I understand it, the addition of this functionality, will further slow down the code.
Any help anyone can offer would be MUCH appreciated - thank you!
Sub DeleteMsProjectTask()
DeleteTasks ActiveProject
Dim sp As Subproject
For Each sp In ActiveProject.Subprojects
DeleteTasks sp.SourceProject
Next sp
MsgBox ("Done")
End Sub
Sub DeleteTasks(prj As Project)
Application.ScreenUpdating
Dim NumTasks As Integer
NumTasks = prj.Tasks.Count
Dim t As Task
Dim mileTsk As String
Do While NumTasks > 0
Set t = prj.Tasks(NumTasks)
If t.OutlineLevel > 1 And t.Work = 0 Then
mileTsk = t.GetField(FieldNameToFiel
If mileTsk = "No" Then
t.Delete
End If
End If
NumTasks = NumTasks - 1
Loop
Application.ScreenUpdating
End Sub
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Application.ScreenUpdating
As I can see, you loop through sub-projectst, that means every single subproject has to be loaded what may consume some time.
It may help to leave a comment In the status bar for each single subproject just to see, what is the delay between each subproject..
Another thing you can do is to disable automatic calculation, as any change to the project forces a recalculation of all fields.
With 1850 rows, is this the size of the major project or the size of each subproject?