Cell value display not chaning

• Question

• Ok, I'm trying to do something and it is giving me grief.

After struggle with it in excel 2016, I tried the exact spreadsheet in Excel 2010 and it did exactly what I wanted.

So there is some difference between Excel 2016 and 2010. 2010 behaves as i expect, 2016 does not nd I don't know why.

Here is the code. It is simply counting through 4 cycles of 4 (I'm doing something more complex, of course, but this simple example shows the problem:

```Option Explicit

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

Sub CycleCounter()
Dim rngBeat As Range, rngCycle As Range, I As Integer

Set rngBeat = Range("Beat")
rngBeat.Value = 0
Set rngCycle = Range("Cycle")
rngCycle.Value = 0

Do While I < 16
I = I + 1
If rngBeat = 4 Or rngBeat = 0 Then
rngBeat = 1
rngCycle = rngCycle + 1
Else
rngBeat = rngBeat + 1
End If
Sleep 500
Loop
End Sub```

The Sleep function is just slowing things down 500 millisecods.

In 2010 I see the two cells do the following:

1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4
1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4

In 2016 it does this:

0 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4
0 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4

This is driving me nuts.

Tuesday, January 10, 2017 9:13 PM

• Sleep does have the advantage of allowing time intervals of less than 1 second, but it has the serious disadvantage of completely "freezing" Excel - it won't recalculate or repaint the screen while it waits.

Try this version. The Timer function does allow for fractions of a second:

```Sub CycleCounter()
Dim t As Single
Dim rngBeat As Range, rngCycle As Range, I As Integer

Set rngBeat = Range("Beat")
rngBeat.Value = 0
Set rngCycle = Range("Cycle")
rngCycle.Value = 0

Do While I < 16
I = I + 1
If rngBeat = 4 Or rngBeat = 0 Then
rngBeat = 1
rngCycle = rngCycle + 1
Else
rngBeat = rngBeat + 1
End If
t = Timer
Do
DoEvents
Loop Until Timer >= t + 0.5
Loop
End Sub```

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Wednesday, January 11, 2017 4:14 PM
Wednesday, January 11, 2017 7:01 AM

All replies

• I can't explain the difference, but I'd do it like this:

```Dim rngBeat As Range, rngCycle As Range, I As Integer

Sub CycleCounter()
I = 0
Set rngBeat = Range("Beat")
rngBeat.Value = 0
Set rngCycle = Range("Cycle")
rngCycle.Value = 0
Call OneCycle
End Sub

Sub OneCycle()
I = I + 1
If rngBeat = 4 Or rngBeat = 0 Then
rngBeat = 1
rngCycle = rngCycle + 1
Else
rngBeat = rngBeat + 1
End If
If I < 16 Then
Application.OnTime Now + TimeSerial(0, 0, 1), "OneCycle"
End If
End Sub```

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Tuesday, January 10, 2017 9:31 PM
• Hans,

I started using the Now function but I found that it was not capable of measuring time increments in less than one second.

I'm trying to set up a timing process that requires less than one second precision.

I looked on the web to find a solution to the sub-second issue and found this "sleep" function.

The sleep function may be part of the problem, as I have never used it before.

It does properly deliver sub-second  wait times:
Sleep 500 is a half second
Sleep 333 is a third of a second

Tuesday, January 10, 2017 9:59 PM
• Hi pmakulski,

This is the forum to discuss questions and feedback for Microsoft Excel client, as it seems that your query is more related to developing issues involving Excel, I'll move your question to the MSDN forum for Excel:

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Best regards,
Yuki Sun

If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

Wednesday, January 11, 2017 3:23 AM
• Sleep does have the advantage of allowing time intervals of less than 1 second, but it has the serious disadvantage of completely "freezing" Excel - it won't recalculate or repaint the screen while it waits.

Try this version. The Timer function does allow for fractions of a second:

```Sub CycleCounter()
Dim t As Single
Dim rngBeat As Range, rngCycle As Range, I As Integer

Set rngBeat = Range("Beat")
rngBeat.Value = 0
Set rngCycle = Range("Cycle")
rngCycle.Value = 0

Do While I < 16
I = I + 1
If rngBeat = 4 Or rngBeat = 0 Then
rngBeat = 1
rngCycle = rngCycle + 1
Else
rngBeat = rngBeat + 1
End If
t = Timer
Do
DoEvents
Loop Until Timer >= t + 0.5
Loop
End Sub```

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Wednesday, January 11, 2017 4:14 PM
Wednesday, January 11, 2017 7:01 AM
• pmakulski,

Indeed, the Sleep function does strange things while I tested it with your code.
However, if you use DoEvents in your While-loop then all seems to work as expected.

```Option Explicit

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

Sub CycleCounter()
Dim rngBeat As Range, rngCycle As Range, I As Integer

Set rngBeat = Range("Beat")
rngBeat.Value = 0
Set rngCycle = Range("Cycle")
rngCycle.Value = 0

Do While I < 16
I = I + 1
If rngBeat = 4 Or rngBeat = 0 Then
rngBeat = 1
rngCycle = rngCycle + 1
Else
rngBeat = rngBeat + 1
End If
Sleep 500
DoEvents
Loop
End Sub```

I only tested it in Excel 2010, so I would be glad to hear if this is working in 2016 as well.

Jan

Wednesday, January 11, 2017 9:54 AM
• Thanks Jan,

My code works in 2010, but not n 2016.

It actually works in 2016, it just paints the screen differently (later) than it does in 2010.

Wednesday, January 11, 2017 3:48 PM
• Super. Thanks Hans.

So Timer returns the second of the day with 7 decimal places of accuracy.
While Now() returns the day with fraction of a day to single second accuracy.

Timer is what I need. I'll check if it eliminates the problem I had with Sleep.

```Sub test()  'ctrl+t
ActiveCell.Value = Timer
ActiveCell.Offset(1, 0).Activate
End Sub
```

which when run a few times returns this:

 39612.5 39613.8 39613.9 39614.1 39614.2 39614.3

I initially thought I was looking at days, which would not have been accurate enough. Then I noticed the integer portion was changing, so it is actually seconds (today is 42746)

Wednesday, January 11, 2017 4:11 PM
• pmakulski,

Without the DoEvents sometimes it painted the screen later in my Excel 2010 as well.
With the DoEvents it painted it always in time.
So, perhaps this will work for you (in 2016) also.
If not, then of course the solution of Hans is what you need.

Jan

Wednesday, January 11, 2017 4:18 PM
• Yes, Timer returns the number of seconds since midnight. It's granularity is about 1/60 of a second.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Wednesday, January 11, 2017 8:21 PM