DoEvents not giving control to Windows in Excel

Question

I'm trying to make an autosave function in Excel as the current Microsoft one does not word for local file.

Here is the VB I have come up with. But still only macro is running, not giving Windows any control. The DoEvents seems not to work.

Sub Auto_save()   
    Do While 1 < 2    
        ActiveWorkbook.Save
        Application.Wait (Now + TimeValue("0:00:05"))
        DoEvents
    Loop

End Sub

(I'm using Excel 365 Pro Plus.)

@QHarr, looks that way, but logical if you want to build your own autosave function... — Luuklag, Apr 19 '18 at 12:40
@Qharr beats me... Perhaps a worksheet change event could do the trick, checking current time against `lastsavetime` and if the difference is larger then X go save. — Luuklag, Apr 19 '18 at 12:46
VBA will keep control after `Application.Wait (Now + TimeValue("0:00:05"))` for 5 secs and before you know rest of the code for `Do..Loop` will be over! If you want to build Auto_Save then you can use code samples posted for digital clocks in excel etc. e.g. https://stackoverflow.com/questions/11867225/how-do-i-show-a-running-clock-in-excel — shrivallabha.redij, Apr 19 '18 at 12:46

FunThomas · Accepted Answer · 2018-04-20T07:43:59.553

3

You are asking VBA to wait 5s which will freeze Excel during this time, and only after the Wait is finished, the DoEvents fires.

One solution that is often seen is to implement an own wait-function, using a loop containing a DoEvents, for example suggested at https://stackoverflow.com/a/21405463/7599798 (copied from there)

Private Sub delay(seconds As Long)
    Dim endTime As Date
    endTime = DateAdd("s", seconds, Now())
    Do While Now() < endTime
        DoEvents
    Loop
End Sub

However, I think this not really a good solution because it keeps your CPU busy. An alternative is to use the OnTime function:

Sub SaveMe()
    ActiveWorkbook.Save
    Application.OnTime Now + TimeValue("00:00:05"), "SaveMe"
End Sub

Side note: You shouldn't use ActiveWorkbook, because the focus could easily change to a different workbook and in that case you wouldn't save the workbook you are expecting.

edited Apr 20 '18 at 07:43

answered Apr 19 '18 at 12:56

FunThomas

23,043
3
18
34

1

Q: If I'm reading the code correctly, the `SaveMe` Sub is recursive. I have had a few (very few, thankfully) instances where using a recursive function built up the call stack too high, and it crashed. Does saving the workbook help clear the call stack? Or could there be a potential issue if a workbook with this code was left open too long? – Mistella Apr 19 '18 at 13:42
@FunThomas `ThisWorkbook.Save` instead of `ActiveWorkbook` – BND Apr 19 '18 at 13:52
@BND: Not necessarily - depends if the code is within the workbook the OP wants to autosave. – FunThomas Apr 19 '18 at 14:01
2

@Mistella: Yes and no. The Sub is calling itself "in the future", but doesn't wait until then, so the stack doesn't increase. – FunThomas Apr 19 '18 at 14:04
Is it possible to integrate a macro to the excel program itself? So that any thime you open a new excel file you macro is automatically run – BND Apr 19 '18 at 15:12
1

@BND: You could add this to your `personal` workbook and run it all few minutes, looping thru all open workbooks. However, keep in mind that if you autosave all open files, you cannot close without saving - and personally, I have too often screwed up a file and was happy to quit and restart. Maybe using `SaveCopyAs` could be a solution. – FunThomas Apr 20 '18 at 07:43

score 2 · Answer 2 · answered Apr 19 '18 at 12:58

You don't need DoEvents. Example code copied and edited from How do I show a running clock in Excel?

Dim TimerActive As Boolean
Sub StartTimer()
    Start_Timer
End Sub
Private Sub Start_Timer()
    TimerActive = True
    Application.OnTime Now() + TimeValue("00:00:05"), "Timer"
End Sub
Private Sub Stop_Timer()
    TimerActive = False
End Sub
Private Sub Timer()
    If TimerActive Then
        ThisWorkbook.Save
        ThisWorkbook.Sheets(1).Range("A1").Value = "Last saved by : " & Application.UserName & " on " & Format(Now(), "dd-mm-yy hh:mm:ss")
        Application.OnTime Now() + TimeValue("00:00:05"), "Timer"
    End If
End Sub

Please note that this solution requires the use of cells A1 on worksheet 1. — Luuklag, Apr 19 '18 at 13:32

score 0 · Answer 3 · answered Oct 12 '18 at 05:10

0

Starting with Excel 365/2016, TWO consecutive DoEvents commands are needed in the VBA code for a dynamic plot to update. Strange but true. Works every time.

answered Oct 12 '18 at 05:10

user10493218

1

DoEvents not giving control to Windows in Excel

3 Answers3