I know you can pause a macro for a certain number of milliseconds using

Public Declare Sub Sleep Lib "kernel32" _
(ByVal dwMilliseconds As Long)
Option Private Module

but I want to know the time between a certain number of iterations. The
code runs too fast per iteration to calculate using seconds, and I can't seem
to figure out how to measure more precisely. My code is as follows:

Function timeRemaining(lastTime, curRow, endRow, cycles)

lastTime = CDbl(lastTime)

Dim timeNow As Double
Dim cyclesLeft As Long
timeNow = CDbl(Time)
cyclesLeft = Int((endRow - curRow) / cycles)

timeRemaining = format(CDate((timeNow - lastTime) * cyclesLeft), "h:mm:ss")

lastTime = CDbl(Time)

End Function

Thanks for your help,
Pflugs

Maybe this is what you want:

Option Explicit
Private lStartTime As Long
Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional ByRef strMessage As Variant = "")
MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
End Sub

Sub ForTesting()

Dim i as long

StartSW
For i = 1 to 10000
'testing code
Next
StopSW

End Sub


RBS

"Pflugs" <(E-Mail Removed)> wrote in message
news:038D62E0-8CA7-418D-8A18-(E-Mail Removed)...
>I know you can pause a macro for a certain number of milliseconds using
>
> Public Declare Sub Sleep Lib "kernel32" _
> (ByVal dwMilliseconds As Long)
> Option Private Module
>
> but I want to know the time between a certain number of iterations. The
> code runs too fast per iteration to calculate using seconds, and I can't
> seem
> to figure out how to measure more precisely. My code is as follows:
>
> Function timeRemaining(lastTime, curRow, endRow, cycles)
>
> lastTime = CDbl(lastTime)
>
> Dim timeNow As Double
> Dim cyclesLeft As Long
> timeNow = CDbl(Time)
> cyclesLeft = Int((endRow - curRow) / cycles)
>
> timeRemaining = format(CDate((timeNow - lastTime) * cyclesLeft),
> "h:mm:ss")
>
> lastTime = CDbl(Time)
>
> End Function
>
> Thanks for your help,
> Pflugs

Yes, that will work well. Thanks very much!

"RB Smissaert" wrote:

> Maybe this is what you want:
>
> Option Explicit
> Private lStartTime As Long
> Private Declare Function timeGetTime Lib "winmm.dll" () As Long
>
> Sub StartSW()
> lStartTime = timeGetTime()
> End Sub
>
> Sub StopSW(Optional ByRef strMessage As Variant = "")
> MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
> End Sub
>
> Sub ForTesting()
>
> Dim i as long
>
> StartSW
> For i = 1 to 10000
> 'testing code
> Next
> StopSW
>
> End Sub
>
>
> RBS
>
> "Pflugs" <(E-Mail Removed)> wrote in message
> news:038D62E0-8CA7-418D-8A18-(E-Mail Removed)...
> >I know you can pause a macro for a certain number of milliseconds using
> >
> > Public Declare Sub Sleep Lib "kernel32" _
> > (ByVal dwMilliseconds As Long)
> > Option Private Module
> >
> > but I want to know the time between a certain number of iterations. The
> > code runs too fast per iteration to calculate using seconds, and I can't
> > seem
> > to figure out how to measure more precisely. My code is as follows:
> >
> > Function timeRemaining(lastTime, curRow, endRow, cycles)
> >
> > lastTime = CDbl(lastTime)
> >
> > Dim timeNow As Double
> > Dim cyclesLeft As Long
> > timeNow = CDbl(Time)
> > cyclesLeft = Int((endRow - curRow) / cycles)
> >
> > timeRemaining = format(CDate((timeNow - lastTime) * cyclesLeft),
> > "h:mm:ss")
> >
> > lastTime = CDbl(Time)
> >
> > End Function
> >
> > Thanks for your help,
> > Pflugs
>
>