Can Project VBA be Sped Up?

[roadkill]

The VBA code that I write is nearly always for either MS Project or MS Excel.
Relatively speaking, the code in Project runs at a snales pace. Even when
I'm just extracting task or assignment information (i.e. no calculations
involved), it takes eons (again, relative to Excel). Are there any tricks
that might help to speed things up?
Will

 

[Jack Dahlgren]

If you are writing code like you do in Excel (selecting cells) then
definitely you can speed the project code by working directly with the
object model.
But there is no way to know unless you post your code so we can see it.

-Jack Dahlgren

 

[Jan De Messemaeker]

That's funny. To me Excel's speed is a catastrophy as compared to Project.
But maybe you are using visual instructions in Project based on selections
etc.?
You should write as mush as possible based on Project's objects (task,
resource, assignment...)
I don't know what else can be the matter..

 

[John]

The VBA code that I write is nearly always for either MS Project or MS Excel.
Relatively speaking, the code in Project runs at a snales pace. Even when
I'm just extracting task or assignment information (i.e. no calculations
involved), it takes eons (again, relative to Excel). Are there any tricks
that might help to speed things up?
Will

roadkill,
What's a snale? Sorry, I just couldn't resist.

As Jack and Jan mentioned, the slowest code you can write for any
application, (Excel, Project, etc.), is foreground based code. This is
the type of VBA you get when you record a macro. The fastest most
efficient code operates directly on the application's objects, that is,
it does not require information from the active view. Unfortunately
however, some operations in Project, like text formatting, can only be
done using direct selection of the data field/cell.

John

 

[Rick Williams]

In general, there are often more than one way to do things in Project VBA,
and some are faster than others. For example, turn off automatic
recalculation at the beginning of your code. Instead of Subs, write them as
functions, which are faster. If you use For Each Task in
ActiveProject.Tasks, try SelectSheet and then use For Each Task In
ActiveSelection.Tasks - this is sometimes faster.
The absolute fastest way for code to run in Project is to convert it to VB
code and compile it as a DLL, and call it from a VBA macro.
Hope this helps,
Rick Williams

 

[roadkill]

Below I've copied over the main loop (where the bulk of the time is spent) of
code from one of my macros in the hope that it will help in determining why
it runs so slow. Note that most of the time (as in the code herein), I'm
running code in MS Project but am dumping information into Excel. Could the
slowness be because I'm crossing over between applications or object models?
I just assumed that MS Project alone was the problem because of the speed I
always experience from Excel-alone code.

For j = 1 To NumFiles
Application.StatusBar = j - 1 & " files complete . . . working on " &
ProjFiles(j)
FileOpen Name:=PthToMPP & ProjFiles(j), ReadOnly:=True,
FormatID:="MSProject.MPP", openpool:=pjDoNotOpenPool 'open MSP file
For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
If T.SplitParts(T.SplitParts.Count).start <= datWkEnding2 And
T.RemainingWork > 0 Then
bolBothWE = False
If T.SplitParts(T.SplitParts.Count).start <= datWkEnding
Then bolBothWE = True
For Each A In T.Assignments
If A.ResourceType = pjResourceTypeWork And
A.RemainingWork > 0 Then
With xlSheet
strScratch = ""
If T.Critical = True Then strScratch = "Y"
If bolBothWE = True Then
.Cells(OutRow, WECOL).Value =
DateFormat(datWkEnding, pjDate_mm_dd_yyyy)
Else
.Cells(OutRow, WECOL).Value =
DateFormat(datWkEnding2, pjDate_mm_dd_yyyy)
End If
.Cells(OutRow, RSRCCOL).Value = A.ResourceName
.Cells(OutRow, PRIORCOL).Value = ProjPrior(j)
.Cells(OutRow, PROJCOL).Value = ProjFiles(j) & "
#" & ProjNum(j)
.Cells(OutRow, TASKCOL).Value = T.Name & " (" &
T.OutlineParent.Name & ")"
.Cells(OutRow, NOTECOL).Value = T.Notes
.Cells(OutRow, IDCOL).Value = T.ID
.Cells(OutRow, STARTCOL).Value =
DateFormat(T.start, pjDate_mm_dd_yyyy)
.Cells(OutRow, FINISHCOL).Value =
DateFormat(T.finish, pjDate_mm_dd_yyyy)
.Cells(OutRow, FINISHCOL2).Value =
DateFormat(T.finish, pjDate_mm_dd_yyyy)
.Cells(OutRow, REMWORKCOL).Value =
FormatNumber(A.RemainingWork / 60, 1)
.Cells(OutRow, PMCOL).Value = ProjPM(j)
.Cells(OutRow, ACTWRKCOL).Value =
FormatNumber(A.ActualWork / 60, 1)
.Cells(OutRow, CRITCOL).Value = strScratch
.Cells(OutRow, BASEWORKCOL).Value =
FormatNumber(A.BaselineWork / 60, 1)
.Cells(OutRow, TSCOL).Value = T.TotalSlack / 60
/ ActiveProject.HoursPerDay
End With
OutRow = OutRow + 1

End If
Next A
End If
End If
Next T
OutRowStart = OutRow
FileClose pjDoNotSave 'close the project file - done with it
Next j

Thank you
Will

 

[Jan De Messemaeker]

Hi,

One glance and I saw what.
..Cells, when called from MS project , is the culprit (at least for me)
That's what made me say Excel is slow, not Project.
But of course I practically never run VBA from Excel!
Greetings,

 

[John]

Below I've copied over the main loop (where the bulk of the time is spent) of
code from one of my macros in the hope that it will help in determining why
it runs so slow. Note that most of the time (as in the code herein), I'm
running code in MS Project but am dumping information into Excel. Could the
slowness be because I'm crossing over between applications or object models?
I just assumed that MS Project alone was the problem because of the speed I
always experience from Excel-alone code.

For j = 1 To NumFiles
Application.StatusBar = j - 1 & " files complete . . . working on " &
ProjFiles(j)
FileOpen Name:=PthToMPP & ProjFiles(j), ReadOnly:=True,
FormatID:="MSProject.MPP", openpool:=pjDoNotOpenPool 'open MSP file
For Each T In ActiveProject.Tasks
If Not T Is Nothing Then
If T.SplitParts(T.SplitParts.Count).start <= datWkEnding2 And
T.RemainingWork > 0 Then
bolBothWE = False
If T.SplitParts(T.SplitParts.Count).start <= datWkEnding
Then bolBothWE = True
For Each A In T.Assignments
If A.ResourceType = pjResourceTypeWork And
A.RemainingWork > 0 Then
With xlSheet
strScratch = ""
If T.Critical = True Then strScratch = "Y"
If bolBothWE = True Then
.Cells(OutRow, WECOL).Value =
DateFormat(datWkEnding, pjDate_mm_dd_yyyy)
Else
.Cells(OutRow, WECOL).Value =
DateFormat(datWkEnding2, pjDate_mm_dd_yyyy)
End If
.Cells(OutRow, RSRCCOL).Value = A.ResourceName
.Cells(OutRow, PRIORCOL).Value = ProjPrior(j)
.Cells(OutRow, PROJCOL).Value = ProjFiles(j) & "
#" & ProjNum(j)
.Cells(OutRow, TASKCOL).Value = T.Name & " (" &
T.OutlineParent.Name & ")"
.Cells(OutRow, NOTECOL).Value = T.Notes
.Cells(OutRow, IDCOL).Value = T.ID
.Cells(OutRow, STARTCOL).Value =
DateFormat(T.start, pjDate_mm_dd_yyyy)
.Cells(OutRow, FINISHCOL).Value =
DateFormat(T.finish, pjDate_mm_dd_yyyy)
.Cells(OutRow, FINISHCOL2).Value =
DateFormat(T.finish, pjDate_mm_dd_yyyy)
.Cells(OutRow, REMWORKCOL).Value =
FormatNumber(A.RemainingWork / 60, 1)
.Cells(OutRow, PMCOL).Value = ProjPM(j)
.Cells(OutRow, ACTWRKCOL).Value =
FormatNumber(A.ActualWork / 60, 1)
.Cells(OutRow, CRITCOL).Value = strScratch
.Cells(OutRow, BASEWORKCOL).Value =
FormatNumber(A.BaselineWork / 60, 1)
.Cells(OutRow, TSCOL).Value = T.TotalSlack / 60
/ ActiveProject.HoursPerDay
End With
OutRow = OutRow + 1

End If
Next A
End If
End If
Next T
OutRowStart = OutRow
FileClose pjDoNotSave 'close the project file - done with it
Next j

Thank you
Will

Will,
Taking a quick look at your code the only thing I see that is a real
slow process is the opening and closing of each Project file - those
will chew up execution time. The fact that you are working directly with
Project and Excel objects is good. In Excel I normally work with a range
object instead of a cell object but I can't say that one is faster than
the other.

Crossing between applications is not generally a speed factor. Excel is
opened at some point earlier in your code so the objects of both
applications are instantly available. If you were running in the
foreground (i.e. selecting active objects), then that would be a
execution time hit, but you are not doing that. One thing you might try
but I'm not sure it will buy you much given the file open and close time
hogs, is to leave Excel in the background (i.e. visible=false) while the
code runs. That saves time by eliminating the screen update in Excel.

Hope this helps.
John
Project MVP

 

[roadkill]

Jan
I plan to try the suggestion from John about hiding Excel but would also
like to try an alternative to the cell object (you said it's been your
experience that this slows things down). In your experience, would using the
range object be faster (e.g. instead of ".Cells(A,B).value" use
".Range(chr(64+B) & A).value"?
Will

 

[Jan De Messemaeker]

Hi,

I never tried that.
Range goes faster if it's more than one cell (for instance coloring the
background of a large range seems to go as fast as for one cell)
In my case what I'm going to try is to skip the cells().value= ...
altogether when the value is 0

I did try visble and calculation off.

HTH

 

[Rod Gill]

In my experience it is the inter-process code that runs slowly. Cells is a
range object, so no problem there. The culprit is so many calls (one per
cell statement) between processes. Alternative ways are:

1) Export to a text file then import that into Excel
2) Copy and paste arrays of data, either one array per assignment or one
array for all data.

In one macro controlling Excel I wrote, exporting a cell at a time (as you
have) the macro took about 20s for 100 odd rows. Storing all data in an
array in Project first then pasting the array to Excel took a total of 1
second, so the speed difference is big! Note that if you have an array with
data for 50 assignments and 5 pieces of information per assignment, , then
you need to paste the array into a range 50 rows and 5 columns in size. You
get an error if there isn't an exact match.

The following code works in Excel VBA:
Range("A1:F2") = Array(1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6)