how can i save my workbook in VBA without stopping the macro from running?

Question

I have VBA code that is calling this method after a certain amount of time has passed using application.ontime,

Sub run()

Worksheets("Sheet1").Calculate

Cells(2, 5).Value = Cells(2, 5).Value + 1

ActiveWorkbook.Save

End Sub

however, after the ActiveWorkbook.Save line, the macros just stop running. how can i continue to run my macros after saving the workbook, or maybe call a macro upon saving? I'm using excel 2013 btw

here is my macro that calls run:

Sub every30seconds()

runTime = Now + TimeValue("00:00:30")

Application.OnTime EarliestTime:=runTime, Procedure:="run", schedule:=True

If Cells(2, 5).Value = 2 Then
   Application.OnTime runTime, "run", , False
   Cells(2, 5).Value = Cells(2, 5).Value - 2
End If

End Sub

Where is your macro code to call the run sub? Do you have any code in your subs Workbook_AfterSave or Workbook_BeforeSave — dbmitch
Shouldn't the code to insert the procedure in 30 seconds be in your run sub? This should be moved into run: runTime = Now + TimeValue("00:00:30") Application.OnTime EarliestTime:=runTime, Procedure:="run", schedule:=True — dbmitch
do you want this to literally to run every 30 seconds? If so, you need a loop in one or the other macros. As written the macro will only run once. — Scott Holtzman
@ScottHoltzman - Assuming the initial value of E2 is zero, the OP's code will only set it to 1 and then stop. It never gets back to the "every30seconds" code to test whether E2 is equal to 2. — YowE3K
@ScottHoltzman sorry about that man! you were right all along — Edmond

YowE3K YowE3K · Accepted Answer · 2016-07-24T21:23:48

The Application.Ontime Method does not allow you to schedule something to happen multiple times in the future. It only allows you to set something to happen once.

I believe the code you are after is

Sub run()
    Worksheets("Sheet1").Calculate

    Cells(2, 5).Value = Cells(2, 5).Value + 1

    ActiveWorkbook.Save

    every30seconds
End Sub   

Sub every30seconds()
    If Cells(2, 5).Value <> 2 Then
        runTime = Now + TimeValue("00:00:30")

        Application.OnTime EarliestTime:=runTime, Procedure:="run", schedule:=True
    Else
        Cells(2, 5).Value = Cells(2, 5).Value - 2
    End If
End Sub

how can i save my workbook in VBA without stopping the macro from running?

1 Answers