none
Cell value display not chaning RRS feed

  • 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

Answers

  • 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 pmakulski 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:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    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.

    Thanks for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

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

    To examine, I made this

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

    which when run a few times returns this:

    39612.5156250000
    39613.7929687500
    39613.9296875000
    39614.0625000000
    39614.1992187500
    39614.3320312500

    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