Solved

OnTime Event

Posted on 2002-06-19
12
541 Views
Last Modified: 2006-11-17
Hi,

I need a full run down on how to use the OnTime event.  The online help for this topic is as much use as udders on bulls - so that is not a solution.

The specific problem is twofold:

Starting when a Global boolean variable named TestStarted is True then

1. Every 60 seconds, Application.Calculate

2. Every 60 seconds:

If WorkSheets("Score").Range("D11").Value >= WorkSheets("Admin").Range("C38").Value then
WorkSheets("Score").Select
End If

A code solution that is able to be implemented in reasonable time, with sufficient explanation will get a B

A code solution, and a description of how OnTime works - a description that is better than online help and Welkenbach's VBA Programming [both of which are useless on this topic] will get the A

Urgently needing help.

Dave


0
Comment
Question by:TigerMan
  • 7
  • 5
12 Comments
 
LVL 22

Accepted Solution

by:
ture earned 100 total points
ID: 7093135
Dave,

If the code below is not explanatory enough, please ask for further explanations.

'This code goes in the ThisWorkbook code module
Option Explicit

'Declare a variable that says when procedure is
'scheduled to run, and another public variable
Public RunTime As Date
Public TestStarted As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
  'Schedule the procedure to run 10 seconds after
  'workbook is opened
 
  'Set variable that says when procedure should run
  RunTime = Now + TimeValue("00:00:10")
 
  'Schedule procedure to run
  Application.OnTime _
    EarliestTime:=RunTime, _
    Procedure:="RunMeEveryTenSeconds", _
    Schedule:=True

End Sub

Private Sub Workbook_Open()
 
  'Unschedule procedure when closing workbook
  Application.OnTime _
    EarliestTime:=RunTime, _
    Procedure:="RunMeEveryTenSeconds", _
    Schedule:=False

End Sub



'This code goes in a normal code Module
Option Explicit

Sub RunMeEveryTenSeconds()

  If TestStarted = True Then
 
    'Calculate
    Application.Calculate
   
    'Do other things
    If Worksheets("Score").Range("D11").Value >= Worksheets("Admin").Range("C38").Value Then
      Worksheets("Score").Select
    End If
   
  End If
 
  'Set variable that says when procedure should run again
  RunTime = Now + TimeValue("00:00:10")
 
  'Schedule procedure to run again
  Application.OnTime _
    EarliestTime:=RunTime, _
    Procedure:="RunMeEveryTenSeconds", _
    Schedule:=True

End Sub

Ture Magnusson
Karlstad, Sweden
0
 
LVL 22

Expert Comment

by:ture
ID: 7093199
Dave,

Ok, I'll try to explain things:

What Application.OnTime does:

1. It makes Excel run a procedure at a specified time. If Excel is busy at that time, the procedure will be run as soon after the specified time as possible. The procedure to run must be in a normal code module and it may not be declared as a private procedure.

2. It can also be used to "unschedule" a procedure that has been set to run at a specified time.


Arguments:

EarliestTime: Time when procedure should run. If Excel is busy, the procedure will run as soon as possible after the specified time. (Required)

Procedure: Name of procedure to run. The procedure must be in a normal Code Module and may not be declared as a Private procedure. (Required)

LatestTime: Latest time when procedure should run if Excel happens to be busy. If not specified, Excel will run the procedure as soon as it can after EarliestTime, even if it is several days after. (Optional)

Schedule: True to schedule a procedure to run. False to turn off a scheduling. If not specified, Excel assumes this argument to be True. (Optional)


Some notes:

You must run VBA code to schedule a procedure. It is often practical to put this code in the Workbook_Open event procedure.

You should make sure that the scheduling is turned off before the workbook containing the code is closed, otherwise Excel will try to run a procedure that is no longer available. This turning-off VBA code should usually be located in the Workbook_BeforeVlose event procedure.

The scheduling is not repetitive. You cannot tell VBA to run a procedure every 60 seconds. You can schedule a procedure to run at a specified time, then you may schedule it to run again at a later time. You may put this re-scheduling code in the procedure that is called by the OnTime event.

To be able to unschedule a procedure, you must know what time it is scheduled to run. Therefore, it is practical to have this time in a public variable.

/Ture
0
 
LVL 5

Author Comment

by:TigerMan
ID: 7093258
Ture,

I knew I could count on you with this one.

Thanks very much.

Will check through and implement tomorrow - if I need to do you mind if I email?

Dave

0
 
LVL 22

Expert Comment

by:ture
ID: 7093284
Dave,

Thanks for the A grade and good luck with the testing. I prefer if you post your follow-up questions here instead of e-mailing - I get lots of junk mail and your message may drown in the flood of Viagra advertisements.

/Ture
0
 
LVL 5

Author Comment

by:TigerMan
ID: 7095002
I can understand your reluctance - I too have similar problems.

But when said and done, I still think its a bit stiff...

LOL
0
 
LVL 5

Author Comment

by:TigerMan
ID: 7095406
Ture,

All code inserted as stated.

In WorkBook code:

Option Explicit
Public RunTime As Date
Public TestStarted As Boolean

In standard code module:

Sub UpdateTime()
If TestStarted = True Then
Application.Calculate
End If
RunTime = Now + TimeValue("00:00:10")
Application.OnTime EarliestTime:=RunTime, Procedure:="UpdateTime", Schedule:=True

End Sub

When debugging this module, I get the old
Variable Not Defined error for TestStarted.

This is another of my favourite stinking past-times - Scoping in VB is an absolute pain - and it just never does what I am told it should.  So what am I doing wrong?

dave
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:TigerMan
ID: 7095410
And when I comment out If TestStarted ....
then the same error for RunTime

Beats me...
0
 
LVL 5

Author Comment

by:TigerMan
ID: 7101332
Hello - desperately need to get this thing going.

Dave
0
 
LVL 22

Expert Comment

by:ture
ID: 7102887
Dave,

Sorry for not responding earlier.

1. The public variables should be declared at the top of the code Module, instead of in ThisWorkbook.

2. The content of the Workbook_Open and Workbook_BeforeClose procedures should be switched.

/Ture
0
 
LVL 5

Author Comment

by:TigerMan
ID: 7102899
Hi Ture,

Yeah, I picked the switch of _open _close from the start and made the changes.

Did not see your point 1 [pub vars in Code] - and maybe that's what was giving me the grief.

Anyway, I now have it working - and when the workbook is complete, I will let you grab a copy if interested - it is basically a 'testing' tool - timed tests with scoresheet or untimed tests for practice.

Anyway, thanks again,

Dave
0
 
LVL 22

Expert Comment

by:ture
ID: 7102906
Dave,

Here's the complete, working code:

In ThisWorkbook:

'This code goes in the ThisWorkbook code module
Option Explicit


Private Sub Workbook_Open()
 
 'Schedule the procedure to run 10 seconds after
 'workbook is opened
 
 'Set variable that says when procedure should run
 RunTime = Now + TimeValue("00:00:10")
 
 'Schedule procedure to run
 Application.OnTime _
   EarliestTime:=RunTime, _
   Procedure:="RunMeEveryTenSeconds", _
   Schedule:=True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
 'Unschedule procedure when closing workbook
 Application.OnTime _
   EarliestTime:=RunTime, _
   Procedure:="RunMeEveryTenSeconds", _
   Schedule:=False

End Sub

In a normal code module:

'This code goes in a normal code Module
Option Explicit

'Declare a variable that says when procedure is
'scheduled to run, and another public variable
Public RunTime As Date
Public TestStarted As Boolean

Sub RunMeEveryTenSeconds()

 If TestStarted = True Then
 
   'Calculate
   Application.Calculate
   
   'Do other things
   If Worksheets("Score").Range("D11").Value >= Worksheets("Admin").Range("C38").Value Then
     Worksheets("Score").Select
   End If
   
 End If
 
 'Set variable that says when procedure should run again
 RunTime = Now + TimeValue("00:00:10")
 
 'Schedule procedure to run again
 Application.OnTime _
   EarliestTime:=RunTime, _
   Procedure:="RunMeEveryTenSeconds", _
   Schedule:=True

End Sub


/Ture
0
 
LVL 5

Author Comment

by:TigerMan
ID: 7106796
thx ture

i'll be back ! !

dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now