Hi there. I have a very simple workbook_open event to determine a random time (within half an hour of a given time) to start and end a process. I would like to pass the start and finish times to the next sub which is in a module. However, once the workbook_open event ends, the variables empty. How can I pass these variables to the next sub using application.ontime?
Code
Option Explicit
Public RandomStartTime As Date 'Actual time to start
Public RandomFinishTime As Date 'Actual time to end
Private Sub Workbook_Open()
Dim RandomNumber As Integer 'Randomly generated number of seconds to wait
Dim StartDate As Date 'Date the process was started
Dim StartTime As Date 'Earliest time to start
Dim FinishTime As Date 'Earliest time to end
StartDate = Date 'Set the date the process was started
StartTime = TimeValue("07:45:00") 'Earliest time to start
FinishTime = TimeValue("16:45:00") 'Earliest time to end
RandomNumber = Int((1800 - 1) * Rnd + 1) 'Randomly generated number of seconds to wait
RandomStartTime = StartTime + TimeSerial(0, 0, RandomNumber) 'Actual time to start
RandomNumber = Int((1800 - 1) * Rnd + 1) 'Randomly generated number of seconds to wait
RandomFinishTime = FinishTime + TimeSerial(0, 0, RandomNumber) 'Actual time to end
Application.OnTime TimeValue("11:25:00"), "ProcessBegin" 'Call the next module
End Sub
Display More