How do I make macro run every minute?

[Legacy 76526]

Hi All,

I have a macro called MyMacro which I would like to run every minute starting from 08:00:00 and finish at 14:00:00.

Thanks for any assistance.

 

[tusharm]

See Chip Pearson's example of using the OnTime method: http://www.cpearson.com/excel/OnTime.aspx The one change I would make is to declare RunWhen as Date rather than Double.

Hi All,

I have a macro called MyMacro which I would like to run every minute starting from 08:00:00 and finish at 14:00:00.

Thanks for any assistance.

 

[Legacy 76526]

Thanks Tusharm,

I have managed to get the macro to run every minute by adapting Pearsons formula. WIll tackle the 0800 and 1400 hrs a little later. Thanks for your help.

For others who are also searching for a similar macro, here is the code:

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long) As Long

Public TimerID As Long
Public TimerSeconds As Single

Sub StartTimer()
TimerSeconds = Range("A1").Value 'enter in cell(A1) the seconds between updates
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
End Sub

Sub EndTimer()
On Error Resume Next
KillTimer 0&, TimerID
End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
ByVal nIDEvent As Long, ByVal dwTimer As Long)
MyMacro 'the name of your macro to run
End Sub

 

[spcalan]

Just curious..

What macro would you want to run every minute.
I am not trying to be critical, just curious...

 

[Legacy 76526]

I have DDE links from the Stock exchange. I have developed my own spreadsheet that sorts stocks into %gainers and fallers and other goodies ... whilst the data is continuously updated and provides the information it must be in alphabetical order, I prefer the order to be %move, thus my macro being run every minute is useful.

 

[spcalan]

very , very cool.
I would like to see that spreadsheet!

 

[spcalan]

I once tried to create a spreadsheet that would update automatically, but I could never figure it out.

Would you enlighten me on how to do this?

 

[Legacy 76526]

Tusharm (and others),

I have had the unfortunate expereince of encountering the weakness of the WIndows API that cpearson mentions in http://www.cpearson.com/excel/OnTime.aspx Whilst I am not editing a cell, the cells are being updated continuously by DDE links. It seems that when the cells are updating and the windows timer performs the macro simultaneously, Excel crashes without saving work.

Does anyone know how to get around this problem or have a better idea of how to make a macro run every minute.

Regards, Brad

 

[Legacy 76526]

See Chip Pearson's example of using the OnTime method: http://www.cpearson.com/excel/OnTime.aspx The one change I would make is to declare RunWhen as Date rather than Double.

Can someone tell me what the difference between "RunWhen as Date" and "RunWhen as Double" is please. What is meant bt Double?

Thanks for assitance.

Brad

 

[Yard]

Hi, take a quick look at http://www-tus.csx.cam.ac.uk/courses/vba/notes/data_types.html