1

Original

I have the following code that is coloring a cell to demonstrate the use of milisecond wait time. However, when i = 500 the code breaks. The error I get is Code Execution has been Interrupted and from 500 to 1000 I have to keep clicking continue. I've tried to wrap my code in an Application.DisplayAlerts = False and True but it still gets interrupted and won't finish. I estimate this code will take approximately 6 minutes more or less as i approaches 1000. I'm at a loss as to what could cause this. I've gone through every setting I can think of and it won't continue past 500 without breaking. ms was calculated from 1/(1000*24*60*60).

Excel 2007

Sub Kaleidoscope()
Dim r, g, b, i As Integer, ms As Double

ms = 0.0000000115741
For i = 1 To 1000
    r = WorksheetFunction.RandBetween(1, 255)
    g = WorksheetFunction.RandBetween(1, 255)
    b = WorksheetFunction.RandBetween(1, 255)
    Range("A1").Interior.Color = RGB(r, g, b)
    Application.Wait (Now + (ms * i))
Next i

End Sub

Thank you in advance!

Update

The link provided by @MarcoMarc (stackoverflow.com/a/5823507/5175942) solved the initial breaking problem of my question. However, it still does not appear to be incrementing correctly. It goes as if it isn't waiting until i = 500 then appears to be stalling 1 second every time. Is this the limit you were speaking of and ultimately it is not possible to wait for 1 ms? No change in the original code was needed to prevent the breaking.

Final Thoughts

@JohnMuggins gives a great tweak to my original code and provides additional tools to see the calculations behinds the scenes. Ultimatley though, he also had to call winAPI like @MacroMarc in order to pause the code for less than 1 second. Through research on additional websites and through Stack Overflow, it appears not possible to pause the program for less than 1 second using VBA alone. It either runs at normal speed or when it gets to 500 ms it rounds up to 1 second and delays the code for 1 second instead of 500 ms. My final code for demonstration is below with @JohnMuggins tweaks.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Kaleidascope()
Dim StartTime As Double
Dim EndTime As Double
Dim ms As Double
Dim i, r, g, b As Integer
Dim count As Long

StartTime = Timer

For i = 1 To 500
    ms = i
    r = WorksheetFunction.RandBetween(1, 255)
    g = WorksheetFunction.RandBetween(1, 255)
    b = WorksheetFunction.RandBetween(1, 255)
    Range("A1").Interior.Color = RGB(r, g, b)
    Sleep ms
    Range("B1").Value = "Time: " & Format(Timer - StartTime, "####.###")
    Range("C1").Value = "ms =  " & Format(ms, "####.####")
    Range("D1").Value = i & " of 500"
Next i

EndTime = Timer - StartTime
Debug.Print Format(EndTime, "####.##")
End Sub
Community
  • 1
  • 1
MartyMcfly0033
  • 168
  • 11
  • 1
    Use `DoEvents` after `For i = 1 To 1000` see if that helps – 0m3r May 02 '17 at 18:35
  • using your suggestion `For i = 1 To 1000 DoEvents ....` It still broke at 500. I did place them on separate lines. – MartyMcfly0033 May 02 '17 at 18:48
  • 1
    It must have something to do with the limits of the wait function. I used the following code without problem. Application.Wait (Now + (0.000001)) – John Muggins May 02 '17 at 19:27
  • 1
    It may be a situation where you have broken execution during a loop. This has happened to me before.Try to press Debug in the error popup, then Ctrl-Break twice or more. See this answer: http://stackoverflow.com/a/5823507/5175942 – MacroMarc May 02 '17 at 19:39
  • Can you elaborate on the limits of the wait function? If it can be used to wait for a minute then why does it break at 500 ms? – MartyMcfly0033 May 02 '17 at 19:43
  • @MacroMarc that link did the trick. However is the wait function still limited? I calculate that in order to run through the first 500 of `i` it should take about 2 minutes. It goes through very fast until it gets to 500 before slowing down. Then it seems to be incrementing at 1 second. – MartyMcfly0033 May 02 '17 at 19:51
  • 1
    `Now()` appears to be only accurate to a second. Try running `Dim x As Double: For i = 1 To 10000: x = Now(): Debug.Print i, x: Next` and you will see jumps in the values displayed. I believe `Wait` also works on seconds. I think the only way to accurately wait for short periods is by [using the `Sleep` function as suggested by MacroMarc](http://stackoverflow.com/a/43747850/6535336). – YowE3K May 02 '17 at 22:20
  • @YowE3K I thought since `Now` gave up to 10 decimal places the 8th, 9th, and 10th places would represent the milisecond. Since a ms is 0.0000000116 I thought that by adding this to `Now` would stall it just for 1 ms. However, as stated above, it seems that VBA or the `Wait` function only takes 1 second increments and will round any fraction of a second up or down. The Sleep function works exactly as anticipated, but now I need to code in windows detection and write it for both 32 & 64 bit >.< lol. Thank you all for the help! – MartyMcfly0033 May 04 '17 at 16:49

3 Answers3

3

You could use the Sleep function from winAPI.

At the top of the module:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Then in your code:

Sleep i ' where i is now in milliseconds

Note that Sleep delays all VBA code.

MacroMarc
  • 3,214
  • 2
  • 11
  • 20
  • Im gathering the excel VBA is incapable of waiting for less than 1 second without a call to Windows API? – MartyMcfly0033 May 03 '17 at 23:35
  • Not saying it's impossible, but Sleep is the only way I use. Sleep seems easiest. – MacroMarc May 04 '17 at 08:35
  • The only thing with sleep is I have to build in windows detection code to determine if it's 32bit or 64bit and have it call the right function. This is why I was attempting to achieve this effect through VBA alone. – MartyMcfly0033 May 04 '17 at 16:25
  • I don't do a lot of this, but you can try some conditional compilation: https://msdn.microsoft.com/en-us/library/office/gg264421.aspx – MacroMarc May 04 '17 at 18:49
0

0m3r was correct. The problem is with the application.wait function. Try the following do events routine instead.

Sub Kaleidoscope()
Dim r, g, b, i As Integer, ms As Double

ms = 0.0000000115741
For i = 1 To 1000
    r = WorksheetFunction.RandBetween(1, 255)
    g = WorksheetFunction.RandBetween(1, 255)
    b = WorksheetFunction.RandBetween(1, 255)
    Range("A1").Interior.Color = RGB(r, g, b)
    Range("A1").Value = i
    For j = Now To (Now + (ms * i))
        DoEvents
    Next j
    
Next i

End Sub
Community
  • 1
  • 1
John Muggins
  • 1,198
  • 1
  • 6
  • 12
  • This is a creative workaround but ultimately does not accomplish what I was looking to do. I want to demonstrate the wait function by allowing the code to wait 1 ms longer than the previous go through. This was done in about 2 seconds and does not demonstrate what I was looking for. – MartyMcfly0033 May 02 '17 at 19:42
  • 1
    Then it took twice as long as it should have. There are only 1000 miliseconds in a second. MCFLY!!! MCFLY!!!! – John Muggins May 02 '17 at 19:57
  • BTW - the human eye can only catch about every 30th to 60th millisecond of color. – John Muggins May 02 '17 at 19:59
  • I understand, but if you take each milisecond and add it together 1, 2, 3, 4, 5, and so on to 500, that would be 125250 ms. This equates to 125.25 seconds or 2.09 minutes. At `i >= 500` comparing the change rate with a timer, it is stalling at 1 second. The answer provided by John did the whole thing in about 2 seconds or 2000 ms < 10% of the calculated time. I could be mistaken so let me know if I missed a key point somewhere. – MartyMcfly0033 May 02 '17 at 20:03
  • `For j = Now To (Now + (ms * i))` will be incrementing by one day each time through the loop, so will never execute more than once. – YowE3K May 02 '17 at 22:02
0

This is the only way I could reach your objective of run-time of 2 minutes and 9 seconds.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'   run time on my computer 2:09:07
'   runs from 6 ms to 751.5 ms
Sub ewhgfsd()
Dim StartTime As Double
Dim EndTime As Double

StartTime = Timer

For i = 1 To 500
    ms = ms + (i * 0.006)
    r = WorksheetFunction.RandBetween(1, 255)
    g = WorksheetFunction.RandBetween(1, 255)
    b = WorksheetFunction.RandBetween(1, 255)
    Range("A1").Interior.Color = RGB(r, g, b)
    Sleep ms
    Range("B1").Value = "Time: " & Format(Timer - StartTime, "####.##")
    Range("C1").Value = "ms =  " & Format(ms, "####.#####")
    Range("D1").Value = i & " of 500"
Next i

EndTime = Timer - StartTime
Debug.Print Format(EndTime, "####.##")
End Sub
John Muggins
  • 1,198
  • 1
  • 6
  • 12
  • I stored `j as Double` and ran from `i = 1 To 500` and timed the code at 9 seconds. It still doesn't come close to matching my expected slowdown as `i` gets bigger and bigger. I understand at the very small intervals it will be nearly inconceivable but at the 100+ mark I expect a noticable slowdown. The only way I can see this method working is to determine exactly how many times it would take for the `j` to loop and take exactly 1 second. Then I could reverse engineer a formula to simulate a milisecond by performing what might be hundres of thousands of computations in that time frame. – MartyMcfly0033 May 02 '17 at 21:22
  • 1
    Also, my code was multiplying 1 milisecond (in excel 1 hours is 1/24/3600/1000 = 1.15741E-08 or 0.0000000115741). My code multiplies this number by some integer `i` say 2, which will then give me the decimal value of 2 miliseconds as interpreted by Excel. I then attempted to add this small decimal to `Now` in order for it to wait 2 miliseconds. Then on the next loop it will add 3 miliseconds, then 4, and so on. I expect to have to wait 2 minutes to loop through 500 iterations. – MartyMcfly0033 May 02 '17 at 21:36
  • I'm sorry but you are overthinking it and your logic is incorrect. A millisecond is 1,000th of a second. It is not .00000005741 of a second. In decimal form a millisecond is .001. You also put a negative mark on my answer which does exactly what you asked for. And when you multiply a fraction by a whole number you are performing division, not addition. Therefore, I'm done try9ing to help you. Good luck. – John Muggins May 03 '17 at 12:36
  • A millisecond represents 0.0000000115741 of a day. (.001 / 60 / 60 / 24) The OP didn't dv your answer - I did (because it just didn't work when I dved it). And when you multiply a fraction by a whole number (e.g. 0.0000000115741 * 2) you are not performing division, you are multiplying one millisecond by 2 in order to get two milliseconds. – YowE3K May 03 '17 at 16:37
  • Looking at your answer now, it still won't do what the OP wants, but it is a lot closer. If you change `Sleep ms` to `Sleep i` so that the first iteration waits 1 millisecond, the second iteration waits 2 milliseconds, etc, and get rid of the `Range.Value = ` statements (which will take several milliseconds each and thus ruin the timing) I believe you will be doing what the OP wants. I will then remove my dv. (Because your answer will match MacroMarc's.) – YowE3K May 03 '17 at 16:43
  • Actually, I retract my comment about the `Range.Value` ruining the timing - they seem to be only adding about 20 milliseconds in total, so I grossly overestimated the length of time they took to run. So, it is just the `Sleep ms` that needs to be changed to `Sleep i` in order to answer the OP's problem. – YowE3K May 03 '17 at 22:32
  • @johnMuggins I appreciate the help you were giving me. I haven't marked anyone's answer as negative because the approach you were providing was different. Also I know that a milisecond is 0.001 of a second, but excel DOES NOT view a second as 1. Also, Multiplication is MULTIPLICATION. When multiplying decimals the whole number is divided by the decimal OR the decimal is multiplied by the whole number. In this case Im starting with a decimal, therefore my method was indeed multiplication. – MartyMcfly0033 May 03 '17 at 23:30
  • @MartyMcfly0033 I apologize, I assumed it was you that did that. I understand that the number .0000000115741 is a fraction of a 24 hour period mathematically but excel does not recognize that as a millisecond. I changed the answer to a code that runs exactly 2 minutes and 9 seconds on my machine and it adds the milliseconds exponentially as you asked. As you can see I used 6 milliseconds as the base because that is the number required for the exact timing of 2 minutes and 9 seconds. – John Muggins May 04 '17 at 12:40
  • @YowE3K incrementing the sleep time by 1 would not accomplish what the OP asked for. He indicated that he wanted the numbers to be increased exponentially, so that 1 would be added to 2 and that sum would be added to 3 (to make 6) and that sum would be added to 4 (to make 10) and so on. Although his math conversion to Excel is a little off, he also indicated that he wants a run time of 2 minutes and 9 seconds according to his calculations. So I built code that runs in that exact time and it shows that .006 is the necessary element for that, a number far greater than .0000000115741. – John Muggins May 04 '17 at 12:49
  • @JohnMuggins so this code seems to do what it is I'm looking for. It appears this is only possible with a call to windows API and not possible through VBA code alone? – MartyMcfly0033 May 04 '17 at 15:36
  • @JohnMuggins Can you also explain why my calculation is a little off? The `Now` function returns number with 10 decimal places where a milisecond is determined by the 8th, 9th, and 10th decimal places produced by `Now`. – MartyMcfly0033 May 04 '17 at 16:18
  • I ran your code @JohnMuggins. On my PC it took exactly 12.72 seconds with the timer you put in. I did some research on the Sleep function and it takes integers as ms. When I set `ms = i` it takes 136.06 seconds to complete. This is slightly longer than I anticipated but that could be from the screen updating and the inserting of values into the cells. I did not need to add `ms` to itself since i was taking care of that from the loop. My statments above were following the logic behind waiting 1 additional ms through each loop. – MartyMcfly0033 May 04 '17 at 16:22
  • I upvoted your answer but gave the actual answer to @MarcoMarc as he was the first to state to use the windows API. – MartyMcfly0033 May 04 '17 at 16:23
  • Thank you and I agree, as I stated before he was correct. – John Muggins May 04 '17 at 16:30
  • (a) Your first call to the `Sleep` function is for 0.006 milliseconds, not 6 milliseconds (that would be `Sleep 6`), but will actually by 0 milliseconds due to the `Sleep` taking a `Long` parameter. (b) The original request was not (and still is not?) for an exponentially increasing delay, but for a linearly increasing delay, so that the elapsed time after 500 iterations would be 1+2+3+...+499+500 milliseconds, i.e. 125.25 seconds (2 mins 5.25 secs, i.e. 2.09 mins). And after 1000 iterations it should be 500.50 seconds, i.e. 8 mins 20.5 secs - not 1006.01 seconds as your code will produce. – YowE3K May 04 '17 at 19:27
  • Thank you for such astute calculation. In any event, the code runs in 2 minutes and 9 seconds. I added the timer so you can run it if you desire also and achieve similar results. Also, by keeping your eye on "B1" and "C1" and "D1" you'll see a counter that can easily be read as it changes with each iteration, without skipping any numbers. If that number were only viable for .006 milliseconds than the human eye would not be able to detect but about every hundredth one. So again, reality disproves faulty calculation. – John Muggins May 08 '17 at 12:48