1

I have a simple code that will copy the current value of my stock portfolio and paste it into a table. I recently tried to add in a timer so that it did this automatically every hour. It works, however, it pastes the value four or five times into the table. I searched around and I think this might be because it takes only a split second to run the macro and so it loops until a full second has passed.

Here's the code:

Sub AutoPL()
    Worksheets("Overview").Range("C15:D15").Copy
    Worksheets("Data").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).PasteSpecial                     xlPasteValues
    Application.CutCopyMode = False
    Call PLTimer
End Sub


Sub PLTimer()
    Application.OnTime Now + TimeValue("01:00:00"), "AutoPL"
End Sub
feetwet
  • 3,248
  • 7
  • 46
  • 84
mclapham
  • 43
  • 4
  • 1
    You could add public intLastHrRun, intLastMinuteRun and intLastSecondRun and then compare the `time` values to this and if different hour, which I suppose is all you need really, then run, if the same hr, then dont. Or set ` Now + TimeValue("01:00:00"), ` to a var, and then cancel the on time after running for this time, then reset in PLTimer. – Nathan_Sav Sep 13 '17 at 13:18
  • 1
    @Nathan_Sav It would be better to do `Dim TimeLastRun as Double` since this will be very precise compared to storing three `Integer`s and comparing them. – Brandon Barney Sep 13 '17 at 13:39
  • @mclapham If the issue is indeed that the code is running too quickly, just add `Application.Wait(TimeValue("00:01:00"))` before calling `PLTimer`. This will add an explicit wait that is a minute long. – Brandon Barney Sep 13 '17 at 13:40
  • 1
    https://stackoverflow.com/questions/20002666/vba-how-do-i-really-stop-application-ontime Use the cancelling of the timer in this post, by setting a public variable to hold the time last called. – Nathan_Sav Sep 13 '17 at 13:43
  • None of these seem to work, when I try the application.wait method my excel freezes.. Any ideas? – mclapham Sep 13 '17 at 14:45
  • Set Application.Wait to just one second, like `Application.Wait(TimeValue("00:00:01"))` – Kresimir L. Sep 13 '17 at 16:11

1 Answers1

0

@mclapham I solved the freezing that you described in your comments by using Application.Wait Now + TimeValue("00:00:01") before calling Application.OnTime again.

Tofulicious
  • 36
  • 3