# 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
``````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

8/22/2022 - Mon
Saurabh Singh Teotia

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
JP

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.
JimboP34

Great but what about line 18?
JP

line 18 is fine if you want to see that message box each time.
Saurabh Singh Teotia

What about that line..? It shows you are 1/10 there what you want to do..?
JP

nevermid I see what your asking now, I will look at it more
JimboP34

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.
JP

elapsed including waiting for message boxes to be answered.
JP

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.
JimboP34

Is it possible to make it so just the time the code takes to run?
JP

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.
JP

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
``````
JimboP34

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
``````
JimboP34

Just out of interest how long does it take your pc to run the code when the target value is 2^26 ?
JP

6 seconds if I answer all the message boxes 2 if I comment them out.
JP

Oops I forgot to change the target value, the code needs some tweaking for that value. Atleast with the %'s it does.
JP

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
``````