Excel 2016 error by timer API Workbook.close

Question

I have a workbook that causes Excel to crash when closing, when closing is triggered by an API timer.

Change The link below is nothing but timer and exit modules, and it has the same behavior as the content issue.

https://www.dropbox.com/s/x0xdwgj5h34ctdk/Book1.xlsm?dl=0

A few seconds after the Excel workbook closes, it crashes. I tried to turn off the timer, display all sheets, unload forms ... all objects are set to Nothing .

All I do is Workbooks(ThisWorkbook.Name).Close SaveChanges:=True ?? !!

The same sub name is called from UserForm without any problems. Workbook closes without Excel crashes.

How to fix it?

 Sub ApplicationExit() ' Call UnloadAllForms ' DoEvents ' Sleep 1000 ' Call StopCloseTimer 'DoEvents 'If Application.Workbooks.Count = 1 Then ' Workbooks(ThisWorkbook.Name).Save ' Application.Quit 'Else DoEvents Workbooks(ThisWorkbook.Name).Close SaveChanges:=True 'End If End Sub 

Timer Code:

 Sub TimerCalled() If CloseTimerValue = "" Then Call Reset_CloseTimerValue DoEvents If basTimers.CloseTimerValue <= Now() And Not Unlocked Then Call ApplicationExit On Error Resume Next 'In case sheet is protected ThisWorkbook.Sheets("JobIndex").Range("CloseCount").Value = Format(Now() - CloseTimerValue, "hh:m:s") End Sub 

Custom form says:

 CloseUp: i = MsgBox("Close Project Register?", vbYesNo, MsgBoxTitle) Select Case i Case vbYes Call ApplicationExit Case vbNo Workbooks(ThisWorkbook.Name).Save End Select 

Answer 1

The timer in excel vba is executed using the Application.OnTime command or the window timer when you use.

Note that to schedule a function call (using the command above), she used APPLICATION, which means that he tells EXCEL to make the call, not your workbook. So, in your case, the workbook is closed, BUT the future procedure is still planned to leave in the future. The error you get is that excel does not find this procedure and then throws an error.

The reason why your case crashes is because you use a Windows timer for this. When you close the workbook, your lib32 instance is lost, and when the time comes, Windows cannot get into memory and then flushes the entire application.

In both cases, it appears that your scheduled procedure is still running.

I suggest you study this and consider using application.ontime.