Search code examples
excelvbacounteroffset

Counter over time period

I have a VBA script which count from 0 to x in y time steps and places this value in a single cell. By using the offset formula I can then use this count to read data from another sheet which then is used to animate an xy scatter graph.

The code used is.

Sub Counter()                     'Name and start of script

For j = 0 To 200 Step 1           'Start of For loop setting j to equal a count from 0 to 2400
    Range("A1").Value = j         'Place the value of j into cell A1
    For c = 1 To 2500000          'Set a new variable called c and count from 0 to a value, this acts to slow down j's count else the animation would not be very long. Increasing this number will slow down the simulation and decreasing will speed it up
    Next                          'Move onto the next event
    DoEvents                      'Tells excel to do the For loop
Next                              'Move onto the next event

End Sub

To enable the animated graph to not run to fast it has a waste time count. This is very dependent on the speed of the computer. (older computers get a smaller count)

What I would like to achieve is to be able to have a count from zero to x over a time periods in seconds as my data is usually generated over 0 to 20 seconds in 0.1 time intervals.

Therefore the animation will run for the same amount of time as the data is calculated for.

Solution

  • At top of module:

    #If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal milliseconds As LongPtr)
#Else  
    Public Declare Sub Sleep Lib "kernel32" (ByVal milliseconds as Long)
#End If

    Then:

    Sub Counter()
    For j = 0 To 200
        Range("A1").Value = j
        Sleep 100 ' or whatever - in milliseconds
        DoEvents
    Next
End Sub

    Adjust milliseconds till the timing is right.