Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Macros
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Macros
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Macros
On Oct 30, 9:34*pm, "MerseyBeat" wrote:
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 wrote in message ... 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 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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Timing Macros
On 10/31/2010 6:31 AM, Andrew wrote:
On Oct 30, 9:34 pm, wrote: 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 wrote in message ... 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 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-Excel...lyzeData.ht m OnTime Method [Excel 2003 VBA Language Reference] http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx Application.Wait Method (Excel) http://msdn.microsoft.com/en-us/library/ff822851.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timing VBA code | Excel Programming | |||
Timing/loading bar | Excel Programming | |||
Timing Chart | Charts and Charting in Excel | |||
Timing Problem can someone help please | Excel Programming | |||
Program timing | Excel Programming |