VBA Run-time Errors

[Lanzer]

Hi, I'm getting two run-time error popups when a VBA script runs (Excel 2016):

1 - Popup - Run-time error '438': Object doesn't support this property or method
Triggered by this line of code in both Task1 and Task2 of the VBA script below: Workbooks("Workbook Name.xlsm").Calculate

2 - Popup - Run-time error '9': Subscript out of range
Triggered by this line of code in both Task1 and Task2 of the VBA script below: Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 1").Calculate

The lines in the script showing the attempts to use both Workbooks(Name) and Worksheets(Name) are commented out, but were the first attempts before moving on to the Workbooks(Name) attempt. All resulting in the Run-time errors.

I am also getting duplicate log events when Task2 runs:

3 - When Task2 runs, six duplicate log events are printed to the worksheet instead of only one. Task1 correctly prints only once for each run event.

Any help troubleshooting these 3 errors is very much appreciated.

Option Explicit

Global OnTime1, Ontime2, b_Init1, b_Init2

Sub Initiating()
'****************************************************************************************************
'at a certain moment, for example opening this workbook, you want to set both schedule-times without executing the task itself
'****************************************************************************************************
b_Init1 = True: b_Init2 = True                             'set flags for initiating both tasks
Task1 'schedule the 1st task
     Task2
End Sub

Sub Task1()
Dim mytime
mytime = TimeValue("13:45:01")                             'daily schedule for this task
StopTasks 1                                                'stop previous ontime for this task

If Not b_Init1 Then                                        'do this part only when you are at that schedule-time, not when you schedule this task as you open this workbook
Workbooks("Workbook Name.xlsm").Calculate                                                 'calculate a specific workbook
'          Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 1").Calculate                      'calculate a specific worksheet
'          Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 2").Calculate                      'calculate a specific worksheet
Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = "Task 1 executed " & Format(Now, "mm/dd/yy at hh:mm:ss")     'add a task execution timestamp in specific worksheet
'          Beep                                                  'do something else ....
End If

Dim b
b = (Now - Date) > mytime                                  'daily scheduled time is already past
OnTime1 = Date + mytime - b                                'next moment = today or tomorrow at the scheduled time
Debug.Print "Task 1 " & IIf(b_Init1, "initiate ", "execute ") & Format(OnTime1, "mm/dd at hh:mm:ss")  'write next scheduled execution time to Immediate window
Application.OnTime OnTime1, "Task1"                        'schedule next moment
b_Init1 = False                                            'reset flag

End Sub

Sub Task2()
Dim mytime
mytime = TimeValue("15:00:01")                             'daily schedule for this task
StopTasks 2                                                'stop previous ontime for this task

If Not b_Init2 Then                                        'do this part only when you are at that schedule-time, not when you schedule this task as you open this workbook
Workbooks("Workbook Name.xlsm").Calculate                                                 'calculate a specific workbook
'          Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 1").Calculate                      'calculate a specific worksheet
'          Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 2").Calculate                      'calculate a specific worksheet
Workbooks("Workbook Name.xlsm").Worksheets("SHEET NAME 1").Range("F" & Rows.Count).End(xlUp).Offset(1).Value = "Task 2 executed " & Format(Now, "mm/dd/yy at hh:mm:ss")     'add a task execution timestamp in specific worksheet
'          Beep                                                  'do something else ....
End If

Dim b
b = (Now - Date) > mytime                                  'daily scheduled time is already past
Ontime2 = Date + mytime - b                                'next moment = today or tomorrow at the scheduled time
Debug.Print "Task 2 " & IIf(b_Init2, "initiate ", "execute ") & Format(Ontime2, "mm/dd at hh:mm:ss")  'write next scheduled execution time to Immediate window
Application.OnTime Ontime2, "Task2"
b_Init2 = False                                            'reset flag

End Sub

Sub StopTasks(Nr)
On Error Resume Next
If Nr = 1 Or Nr = 3 Then Application.OnTime OnTime1, "task1", , 0
If Nr = 2 Or Nr = 3 Then Application.OnTime OnTime1, "task2", , 0
On Error GoTo 0
End Sub

 

[StephenCrump]

1. You can't calculate a workbook like this. Your best option is a simple: Calculate which will calculate all open workbooks.

2. This will mean you don't have a worksheet called "SHEET NAME 1" in Workbooks("Workbook Name.xlsm"). (But presumably you do have a workbook called "Workbook Name.xlsm", otherwise you'd also get a subscript out of range error at step 1.)

3. Presumably means Task2 is running six times. Your flags and timers might be getting muddled. I'll see if I can replicate this, unless someone else jumps in in the meantime.

 

[Lanzer]

1. You can't calculate a workbook like this. Your best option is a simple: Calculate which will calculate all open workbooks.

Thanks Stephen! Changing Workbooks("Workbook Name.xlsm").Calculate to just Calculate seems to have resolved that issue and the other two as well.

 

[StephenCrump]

OK, I'm still not totally clear what was happening. But I'm glad you got it working!