# Thread: [RESOLVED] Find macro bottleneck

1. ## [RESOLVED] Find macro bottleneck

First let me say that I know how to time code and in fact I've written an article on the subject.

My problem is that I have a situation where a sheet in an Excel workbook contains about 3500 rows and for each of about 1100 of those rows I need to perform 7 macros. The whole process takes about 2 minutes to run whereas a very similar process takes only 15 seconds, and I'd like to determine the total time spent in each of those seven macros to try to determine if one or more of the macros is a bottleneck. Is there an existing free or paid tool that will do that or can someone come up with a DIY scheme to do it?

2. ## Re: Find macro bottleneck

not hard to use a timer of some sort to record the time each macro takes, especially if the 7 macros are called consecutively
depending on the required accuracy you could try timer, gettickcont or queryperformancecounter
i would assume that timer would be accurate enough, for what you need to do, you could sum the times for each macro into an array, then save the results to file or excel sheet for later comparisons
even possible to compare the times spent for each row processed if required

3. ## Re: Find macro bottleneck

Agree with Pete, but with slight differences.

I'd use Gettickcount to log entering a macro, and the same when exiting.
Result into a 2D-Array (or maybe a separate sheet)
Kind of like

Row - Macro1Enter, Macro1Exit, Macro1TimeLen, Macro2Enter, Macro2Exit, Macro2TimeLen etc...... (TimeLen being Exit minus Enter)

It should give you the hint which one is causing trouble.
It basically doesn't matter if you're using milliseconds, ticks, [whatever] since you just need the representation of the needed time.
The higher the value of TimeLen, the more time you need for it, independent of the used unit

4. ## Re: Find macro bottleneck

Thank you both for your help. I started out by adding a new sheet to the workbook and writing the elapsed time (as timed using GetTickCount) of each of the 7 macros to its own column with the intent to average each column. Part way through that process however I found the real problem and that was that automatic calculation was allowed to continue in the long-running process and when I changed that to manual the problem went away.

5. ## Re: [RESOLVED] Find macro bottleneck

Here's a class I've been using over the years. It uses vb's Timer function and keeps track of timers by name. Timers accumulate if called multiple times.

Usage:
Code:
```Sub TimeStuff()
Dim sw as New CStopwatch
Dim i as Long

For i = 1 to 100
sw.StartTimer "Process 1"
sw.StopTimer "Process 1"

sw.StartTimer "Process 2"
sw.StopTimer "Process 2"
Next

sw.Report 'Outputs to immediate window
End Sub```
Class CStopwatch
Code:
```Option Explicit

Private Type tTimer
Name As String
Start As Single
Stop As Single
RunTime As Single
End Type

Private mTimerCount As Byte
Private mTimers(255) As tTimer

Private Sub Class_Terminate()
Erase mTimers
End Sub

Public Sub StartTimer(Name As String)
Dim arrPos As Byte
Dim found As Boolean

found = FindTimer(Name, arrPos)

mTimers(arrPos).Stop = 0
mTimers(arrPos).Start = Timer
End Sub

Public Sub StopTimer(Name As String)
Dim arrPos As Byte
Dim found As Boolean

found = FindTimer(Name, arrPos, False)

Debug.Print "Trying to stop timer " & Name & " but no such timer exists."
Exit Sub
End If

mTimers(arrPos).Stop = Timer
mTimers(arrPos).RunTime = mTimers(arrPos).RunTime + mTimers(arrPos).Stop - mTimers(arrPos).Start
End Sub

Public Sub Report()
Dim i As Byte

If mTimerCount = 0 Then
Debug.Print "Nothing to report."
End If

For i = 1 To mTimerCount
If mTimers(i).Stop = 0 Then StopTimer mTimers(i).Name
Debug.Print mTimers(i).Name & ": " & Format(mTimers(i).RunTime, "#,##0.00s")
Next
End Sub

Private Function FindTimer(Name As String, ByRef ArrayPos As Byte, Optional IncreasePosition As Boolean = True) As Boolean
Dim found As Boolean

If mTimerCount = 0 Then
mTimerCount = 1
ArrayPos = 1
Else
For ArrayPos = 1 To mTimerCount
If mTimers(ArrayPos).Name = Name Then
found = True
Exit For
End If
Next
ArrayPos = mTimerCount + 1
If IncreasePosition Then mTimerCount = ArrayPos
End If
End If

FindTimer = found
End Function```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured