Profiling VBA code for microsoft word

Question

I have some legacy code that uses VBA to parse a word document and build some XML output;

Needless to say it runs like a dog but I was interested in profiling it to see where it's breaking down and maybe if there are some options to make it faster.

I don't want to try anything until I can start measuring my results so profiling is a must - I've done a little searching around but can't find anything that would do this job easily. There was one tool by brentwood? that requires modifying your code but it didn't work and I ran outa time.

Anyone know anything simple that works?

Update: The code base is about 20 or so files, each with at least 100 methods - manually adding in start/end calls for each method just isn't appropriate - especially removing them all afterwards - I was actually thinking about doing some form of REGEX to solve this issue and another to remove them all after but its just a little too intrusive but may be the only solution. I've found some nice timing code on here earlier so the timing part of it isn't an issue.

Answer 1

Insert a bunch of

Debug.Print "before/after foo", Now

before and after snippets that you think might run for long terms, then just compare them and voila there you are.

Answer 2

It may be possible to use a template to add a line to each procedure:

http://msdn.microsoft.com/en-us/library/aa191135(office.10).aspx

Error handler templates usually include an ExitHere label of some description.. The first line after the label could be the timer print.

It is also possible to modify code through code: "Example: Add some lines required for DAO" is an Access example, but something similar could be done with Word.

This would, hopefully, narrow down the area to search for problems. The line could then be commented out, or you could revert to back-ups.

Answer 3

Using a class and #if would make that "adding code to each method" a little easier...

Profiler Class Module::

#If PROFILE = 1 Then

Private m_locationName As String
Private Sub Class_Initialize()
    m_locationName = "unknown"
End Sub

Public Sub Start(locationName As String)
    m_locationName = locationName
    MsgBox m_locationName
End Sub

Private Sub Class_Terminate()
    MsgBox m_locationName & " end"
End Sub

#Else

Public Sub Start(locationName As String)
 'no op
End Sub

#End If

some other code module:

' helper "factory" since VBA classes don't have ctor params (or do they?)
Private Function start_profile(location As String) As Profiler
    Set start_profile = New Profiler
    start_profile.Start location
End Function

Private Sub test()
    Set p = start_profile("test")
    MsgBox "do work"
    subroutine
End Sub

Private Sub subroutine()
    Set p = start_profile("subroutine")
End Sub

In Project Properties set Conditional Compilation Arguments to:

PROFILE = 1

Remove the line for normal, non-profiled versions.

Adding the lines is a pain, I don't know of any way to automatically get the current method name which would make adding the profiling line to each function easy. You could use the VBE object model to inject the code for you - but I wonder is doing this manually would be ultimately faster.

Answer 4

My suggestion would be to divide and conquer, by inserting some timing lines in a few key places to try to isolate the problem, and then drill down on that area.

If the problem is more diffused and not obvious, I'd suggest simplifying by progressively disabling whole chunks of code one at a time, as far as is possible without breaking the process. This is the analogy of finding speed bumps in an Excel workbook by progressively hard coding sheets or parts of sheets until the speed problem disappears.

Answer 5

About that "Now" function (above, svinto) ...

I've used the "Timer" function (in Excel VBA), which returns a Single. It seems to work just fine. Larry