automatic macro execution

[Jos reulen]

Dear reader,
is it possible in excel to have a macro to be executed for
example every 15 seconds automatically ?

 

[Chip Pearson]

Jos,

You can use the OnTime method to schedule a macro to execute at a specific
time. See www.cpearson.com/excel/ontime.htm for details and example code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[jos reulen]

Dear Chip,
thank you very much for your info. I made a little program
for testing.
The code is included. However, I get an error message in
dutch; a free translation says
Cannot find the entrancepoint of DLL settimer in user32

have you any idea whats wrong ?

thank you very much

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 = 10 'how often to "pop"the timer
timerid = settimer(0&, 0&, timerseconds * 1000&,
AddressOfTimerproc)
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)

'the procedure is called by windows. Put your timer
related code here
StartTimer
Beep
endtimer
End Sub

Timerproc
End

 

[jos reulen]

-----Original Message-----
Dear Chip,
thank you very much for your info. I made a little excel

macro program for testing. It should give a beep every 10
seconds

 

[Chip Pearson]

Jos,

Function names in the Declare statements are case sensitive. Change
settimer to SetTimer and change killtimer to KillTimer, and omit the
Aliases.

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

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)