0

I'm trying to make code which moves the active cell downwards every x seconds (with hopefully x<1), and am having some issues.

Firstly, the following line of code bugs out at the 4th line when I run it, saying: "Cannot run the macro "__". The macro may not be available in this workbook or all macros may be disabled".

I cannot find any fixes for this online - is there an error in this Sub or is this an issue with my setup of Excel which can be easily fixed?

Public Sub Time_Practice()
    Debug.Print "Hello, it is " & Timer
    Timer = Now + TimeValue("00:00:01")
    Application.OnTime Timer, "Time_Practice"
End Sub

Secondly, the actual code I would like to run is the stuff below, but where I can vary TimeValue("00:00:01") by using a variable such as Added_Time.

By this I mean, if something has happened Y times, I would like the new value for Added_Time = TimeValue("00:00:01") - 0.01 * Y (i.e. if it has happened 10 times, I would like the time gap to be 0.9 seconds).

Would the following line of code be valid and work ok?

Dim Timer As Double, Dim Y as Double
Public Sub Movement()
    ActiveCell.Offset(1, 0).Activate
    Timer = Now + TimeValue("00:00:01") - 0.01 * Y
    Application.OnTime Timer, "Movement"
End Sub

Thanks in advance

braX
  • 11,506
  • 5
  • 20
  • 33
Edward
  • 1
  • 5
    `Timer` is a keyword in VBA, so choose something else. Also you might take a look at https://stackoverflow.com/questions/25116231/ontime-for-less-than-1-second-without-becoming-unresponsive – Tim Williams Dec 10 '19 at 18:00
  • `Now + TimeValue("00:00:01") - ((0.01/(24*60*60)) * Y)` – Scott Craner Dec 10 '19 at 18:04
  • 1
    `OnTime` only supports times in increments of 1s. You'll need another approach to get <1s [see this answer of mine](https://stackoverflow.com/a/25116614/445425) – chris neilsen Dec 10 '19 at 18:36

0 Answers0