asked on
Time Taken to Run Macro does not match reality
Dear all
I have two issues with the attached code:
1) Only the first message box saying "1/10 of the way there" comes up. 1/9 and 1/8 etc do not.
2)The time stated in the msgbox is a fraction of a second - whearas in reallity takes about 20 seconds or so.
Any thoughts?
Thanks.
Jimbo
I have two issues with the attached code:
1) Only the first message box saying "1/10 of the way there" comes up. 1/9 and 1/8 etc do not.
2)The time stated in the msgbox is a fraction of a second - whearas in reallity takes about 20 seconds or so.
Any thoughts?
Thanks.
Jimbo
Sub Macro1()
Dim TimeA As Variant
Dim n As Long
Dim z As Long
z = 10
TimeA = Now()
MsgBox 100000000
Do Until n = 100000000
n = n + 1
If n = ((100000000) / z) Then
MsgBox "1/" & z & " of the way there!"
z = z - 1
MsgBox "z is " & z
End If
Loop
TimeA = Now() - TimeA
MsgBox TimeA
MsgBox n
End Sub
Microsoft Excel
Last Comment
JP
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Great but what about line 18?
line 18 is fine if you want to see that message box each time.
What about that line..? It shows you are 1/10 there what you want to do..?
nevermid I see what your asking now, I will look at it more
ASKER
Great thanks guys - one final question will the time in the end be the time it took to run the code (ie it stops when a message box appears and starts again when clicked ok) or the "real" time lenght?
Cheers.
Cheers.
elapsed including waiting for message boxes to be answered.
But line 18 still is funky, after 1/2 you wait for the other half to finish and then it says you are 1/1. Maybe you'd be better off with a % of completion.
ASKER
Is it possible to make it so just the time the code takes to run?
not unless you want to remove your messgae boxes, unless maybe you could get a timestamp before and after each message but then you need to add them up it is definitley ugly.
Try the code below, it is faster and a little cleaner.
Sub Macro1()
Dim TimeA As Variant
Dim n As Long, x As Long, z As Long
z = 10
x = 1
TimeA = Now()
MsgBox 100000000
Do Until n = 100000000
n = n + 1
If n = (100000000 / z) * x Then
MsgBox n
MsgBox z * x & "% " & "of the way there!"
x = x + 1
End If
Loop
TimeA = Now() - TimeA
MsgBox Format(TimeA, "hh:mm:ss")
MsgBox n
End Sub
ASKER
What about something like the below?
Sub Macro1()
Dim TimeA As Variant
Dim n As Long
Dim z As Long
z = 1
TimeA = Now()
MsgBox 1000000
Do Until n = 1000000
n = n + 1
If (z / 10) = (n / 1000000) Then
MsgBox z & "0% of the way there!"
z = z + 1
End If
Loop
TimeA = Now() - TimeA
MsgBox Format(TimeA, "hh:mm:ss")
MsgBox n
End Sub
ASKER
Just out of interest how long does it take your pc to run the code when the target value is 2^26 ?
6 seconds if I answer all the message boxes 2 if I comment them out.
Oops I forgot to change the target value, the code needs some tweaking for that value. Atleast with the %'s it does.
Ok, 11 seconds if I answer the messages and 6 if I comment them out using the code I've attached.
Sub Macro1()
Dim TimeA As Variant
Dim n As Long, x As Long, z As Long
z = 1
TimeA = Now()
x = 67108864
MsgBox x
Do Until n = x
n = n + 1
If n = Round(x * 0.1, 0) * z Then
MsgBox z & "0% of the way there!"
z = z + 1
End If
Loop
TimeA = Now() - TimeA
MsgBox Format(TimeA, "hh:mm:ss")
MsgBox n
End Sub