VBA Application.Ontime error 438. Object doesn't support this property or method

Question

I've got a macro that has been working absolutely fine in Excel 2010. I'm now trying to run this file using Excel 2016 / 365 and the macro pops up with a run time error 438: Object doesn't support this property or method. It basically closes and re-opens the current Excel file that I'm working on ignoring any messages to save along the way and regardless of whether I'm read-only or not. If anyone can help then that would be fantastic. The code is below. Thanks in advance.

Sub reopen2()

Application.DisplayAlerts = False
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim pth As String
pth = wb.FullName

Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(FILENAME:=pth, Notify _
        :=False)
wb.Close (False)
Application.DisplayAlerts = True

End Sub

Hmm. No repro in Office 2016 (but not 365). Does this not work in a new workbook (make sure to save first). — BigBen, Dec 04 '19 at 20:46
Surely it complains about Application.Workbooks.Open(), the procedure name must be provided as a string and can't take arguments. https://learn.microsoft.com/en-us/office/vba/api/excel.application.ontime — Hans Passant, Dec 04 '19 at 20:48

GSerg · Accepted Answer · 2019-12-04T21:13:31.843

Application.OnTime's second parameter is explicitly declared as string, so this:

Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(FILENAME:=pth, Notify _
        :=False)

is actually:

Application.OnTime Now + TimeValue("00:00:01"), Application.Workbooks.Open(FILENAME:=pth, Notify _
        :=False).DefaultPropertyOfWorkbook

A workbook does not have a default property, hence the "Object doesn't support this property or method" error.

You have to put the code into a method and provide its name to OnTime as a string.

You want to pass the already closed workbook's full path to that method, so normally you would have to register the OnTime call with parameters.

In your case, though, you don't have to do that because all you want to do is open the workbook, and Excel remembers in which workbook the registered macro resides, and if scheduled to be called on time, it will reopen the workbook automatically just to call the macro, which is already the effect that you want. So your complete code would be:

Option Explicit

Sub reopen2()
  Application.OnTime Now + TimeValue("00:00:01"), "DoNothing"

  ThisWorkbook.Close False
End Sub

Public Sub DoNothing()
End Sub

provided it's in a regular module.

VBA Application.Ontime error 438. Object doesn't support this property or method

1 Answers1