Timing Macros

[jambun]

Can I trigger countback macros in one worksheet from another worksheet
and or can I program a macro to trigger every 30 seconds or so? If so
how is it done?
EPJ

 

[MerseyBeat]

EPJ,

This should get you started in the right direction. It is from Chip
Pearson's website: http://www.cpearson.com/Excel/OnTime.aspx

MB

 

[Andrew]

EPJ,

This should get you started in the right direction.  It is from Chip
Pearson's website:  http://www.cpearson.com/Excel/OnTime.aspx

MB

Below is a code I've been using for a timer. I cannot properly
explain how it works, although at one time I did know.
This will give you a working example and you can work backwards to
figure out how it works and how to tweak it to
suit your application.

Name your first worksheet "Main".
Copy the code below into a module.

'---------------------------------------------------------------------------------------------------

' ====================
Sub tool_ObserverAction()
Sheets("Main").Range("A1").Value = Time

Static count As Integer
Sheets("Main").Cells(1, 2) = count
count = count + 1
End Sub
' ====================

' ====================
Sub qexc_Observer()

'declarations
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSectonds As Integer

'config
intHours = 0
intMinutes = 0
intSeconds = 1

'do action that should be performed regularly
tool_ObserverAction

'observe again regularly
Application.OnTime Now + TimeSerial(intHours, intMinutes,
intSeconds), "qexc_Observer"
'Application.OnTime Now + TimeSerial(intHours, intMinutes,
intSeconds), "mytest"
End Sub
' ====================

'----------------------------------------------------------------------------------------------------

Now put this code in the ThisWorkbook

'----------------------------------------------------------------------------------------------------
Private Sub Workbook_Open()
qexc_Observer
End Sub
'----------------------------------------------------------------------------------------------------

Once you open the workbook, the timer will be running. The current
time and the timer value will be displayed in the first row of the
sheet labeled "Main"

 

[Mike S]

Below is a code I've been using for a timer. I cannot properly
explain how it works, although at one time I did know.
This will give you a working example and you can work backwards to
figure out how it works and how to tweak it to
suit your application.

Name your first worksheet "Main".
Copy the code below into a module.

'---------------------------------------------------------------------------------------------------

' ====================
Sub tool_ObserverAction()
Sheets("Main").Range("A1").Value = Time

Static count As Integer
Sheets("Main").Cells(1, 2) = count
count = count + 1
End Sub
' ====================

' ====================
Sub qexc_Observer()

'declarations
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSectonds As Integer

'config
intHours = 0
intMinutes = 0
intSeconds = 1

'do action that should be performed regularly
tool_ObserverAction

'observe again regularly
Application.OnTime Now + TimeSerial(intHours, intMinutes,
intSeconds), "qexc_Observer"
'Application.OnTime Now + TimeSerial(intHours, intMinutes,
intSeconds), "mytest"
End Sub
' ====================

'----------------------------------------------------------------------------------------------------

Now put this code in the ThisWorkbook

'----------------------------------------------------------------------------------------------------
Private Sub Workbook_Open()
qexc_Observer
End Sub
'----------------------------------------------------------------------------------------------------

Once you open the workbook, the timer will be running. The current
time and the timer value will be displayed in the first row of the
sheet labeled "Main"

That's interesting - I wondered how VBA did it too. VB6 can use a timer
control, VBA can use Application.OnTime and Application.Wait, there are
lots of good search engine hits showing how they work:

Using Application.OnTime to Periodically Analyze Data
http://www.java2s.com/Code/VBA-Exce...pplicationOnTimetoPeriodicallyAnalyzeData.htm

OnTime Method [Excel 2003 VBA Language Reference]
http://msdn.microsoft.com/en-us/library/aa195809(office.11).aspx

Application.Wait Method (Excel)
http://msdn.microsoft.com/en-us/library/ff822851.aspx