Project 2007 Macro Performance

[rmarsh3772]

We are currently in the process of updating from Project 2003 to Project 2007. Very simple macros that run just fine in 2003 take forever in 2007. Our tech guy tried in 2010 and said it runs just as slow there.

Same machine, same file, same macro. Consistent across all machines.

Has anyone seen this before?

If it is working good for you, let me know that too.

Thanks

 

[SkipVought]

Hi,

I have no answer for you. However, I do extensive Excel VBA programming and have seen no degradation from 2003 to 2007.

Please post one of your macros and I'll see if there can be some VB improvement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PDQBach]

I concur with Skip: I have seen no material degredation in macro run times in 2007 or 2010. And, as he suggested, perhaps you could post a macro and give us the time it took to run, and the number of tasks in the file.

 

[rmarsh3772]

Thanks for the responses.
Here is the macro

Sub A_SpeedTest()

Dim oTask As Task
Dim xDateIn As String
Dim xDateOut As String
xDateIn = Format(Time, "HH:mm:SS")
SelectAll
For Each oTask In ActiveProject.Tasks
oTask.Name = "Task " + Format(oTask.ID)
Next oTask
xDateOut = Format(Time, "HH:mm:SS")
MsgBox DateDiff("s", xDateIn, xDateOut)

End Sub

My project has about 5,000 activities. The macro takes less than one second in Project 2003 and about 15 in Project 2007. I'll attach a link for the project when I get home.

 

[SkipVought]

I would not convert Time Serial value to a string, and then force VBA to convert them back to TimeSerial in the DateDiff function.

Using the With...End With structure will give a measure of efficiency.

Sub A_SpeedTest()

    Dim oTask As Task
    Dim xDateIn As Variant
    
    xDateIn = Time
    
    For Each oTask In ActiveProject.Tasks
        With oTask
            .Name = "Task " & .ID
        End With
    Next oTask
    
    MsgBox DateDiff("s", xDateIn, Time)
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rmarsh3772]

The problem I am having is much bigger than the format of the time command. It has to do with looping through the project.

I have attached the test file.

 

[rmarsh3772]

Open the file or down load it and run the macro and tell me how long it takes to run. When I run it in 2003 it displays a 0 or 1. When I run it in 2007 it displays 15 or 20. The macro is A_SpeedTest.

BTW, I am having no problems with Excel 2007.

Thanks

 

[SkipVought]

Runs in 2 seconds in 2007.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, running with THIS construct and the displayed results have been 0 and 1,

    With oTask
        .Name = "Task " & .ID
    End With

since it is more efficient code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rmarsh3772]

Thanks for the input on the speed, and thanks for input on the code.

I'll have to get with our IT folks and find out what they did with our version that makes it so slow. I have some complex macros for taking data from another system and putting it into Project that run 50 minutes for a 5,000 line file in 2007. The same macro ran less than 5 minutes in 2003.

 

[SkipVought]

Yes, your environment seems that it has some issues.

Your code can probably be tuned to run more efficiently, at least by using the With...End With structure when drilling down thru several nodes a repeated number of times.

Select and Activat are almost always unnecessary and time consuming statements. Use a reference instead.

I don't know if screen updating or automatic calculation can be turned off in Project like they can in Excel, but that might be another place to look.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}