Performance profiling for VBA macros?

[Carl]

Is there a way to gather performance data, like how many seconds are spent on
each line, without adding code to instrument each line? I'm thinking of the
profiling capabilities available in C/C++ that help you focus your
optimization efforts...

-- Carl

 

[Rod Gill]

No there isn't that I know of. The only tasks that take time are those
interacting with other processes. What code do you have that is running
slowly?

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Carl]

Rod,

I am building a time-phased data report in Excel for about a thousand tasks.
It takes a few minutes to run.

-- Carl

 

[Rod Gill]

Hi,

The time taken will be sending the data to Excel as it is an inter-process
action. Speed this up significantly by either saving the timephased data
into an array then writing to Excel in one go, or exporting the timephased
data to a .csv file then open it in Excel.

To time code:
Dim Tim as Single
Tim=Timer
'Do code
MsgBox "Time taken: " & format(Timer-Tim,"0.00") & "Seconds"

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Carl]

Thanks for the suggestion, I'll give that a try and let you know the result!

-- Carl

 

[Carl]

Rod,

I narrowed it down to a single line of code that's burning four minutes of
execution time. I've got a Excel.Range variable that I use to point to the
"current cell" when I'm building my Excel report, the line that's using all
the time is where I bump that pointer along to the next cell in the row being
populated:

Set xlRange = xlRange.Offset(0, 1)

It accounts for almost 95% of the entire timeline, does that seem possible?
How could that simple operation be so time-consuming? My mind just
boggled...

I'm still new at this, so I'm guessing that I am creating a new object from
an existing object each time I shift to the right. If xlRange points to a
cell, is there a better method for bumping it along to the next cell in a row?

-- Carl

 

[Rod Gill]

That's obviously an expensive inter-process action!! You could try leaving
the variable pointing to the same cell and use a count variable:

Dim Count as Long
...
Count=Count+1
xlRange.Offset(0, Count)=Data

That may be quicker. otherwise copying all data to an array then to Excel is
very fast. I've copied 1000x5 (row,columns) of data in less than a second.

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

 

[Carl]

Rod,

I used the offset() you suggested and time spent in that routine dropped by
a factor of eight. Now I'm going to take a crack at converting it to the
array approach to see how that compares performance-wise.

-- Carl

 

[Rod Gill]

Hi,

You need to make sure you match the array size, to the range you copy to as
in the following code for copying the first 10 tasks:

Sub test()
Dim xlApp As Excel.Application
Dim xlr As Excel.Range
Dim Data(1 To 10, 1 To 2) As Variant
Dim Tsk As Task
Dim Id As Integer
For Each Tsk In ActiveProject.Tasks
If Not Tsk Is Nothing Then
Id = Id + 1
Data(Id, 1) = Tsk.Name
Data(Id, 2) = Tsk.Duration / 60 & "h"
If Id >= 10 Then Exit For
End If
Next
Set xlApp = GetObject(, "Excel.Application")
Set xlr = xlApp.Range("A4")
xlr.Range("A1:B10") = Data
Set xlr = Nothing
Set xlApp = Nothing
End Sub

Excel needs to be open with a new Workbook open

--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com