OnTime macro stops running when Excel minimized

[north]

Hi. I am using Excel2010.

My sheet displays values collected from a database via an AddIn.
The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute.
When the values exceed a certain limit, a sound is played from a custom Alarm function.

This all works as long as the excel window is active.

But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.

Is there a workaround to this?

Appreciate all answers.

 

[Cool blue]

Hi, it might be helpful to post your code, but anyway, maybe the SendKeys doesn't work when the workbook is not active? I suggest you do a little experiment to check that. If that's the problem, then you need to run the add-in function using a direct call in your code. This is much nicer anyway, SendKeys is generally quite bugy.

 

[north]

In Sheet1:

Private Sub Workbook_Open()
Run "TimerSet"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call StopTimer
End Sub

In module:

Sub TimerSet()
Application.OnTime Now + TimeValue("00:01:00"), "TimerSet"
Application.WindowState = xlNormal
Application.SendKeys ("^G")
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime TimeValue("00:01:00"), "TimerSet", , False
End Sub


Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\beep.wav" 
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

 

[Cool blue]

I would try it with a direct call to TimerSet and not use the Run method.

Also, StopTimer will not kill the timer. You need to store the EarliestTime in a Global variable in the SetTimer routine and use that as the EarliestTime in StopTimer as well. It must have the exact same time in both calls.

 

[north]

Hi, it might be helpful to post your code, but anyway, maybe the SendKeys doesn't work when the workbook is not active? I suggest you do a little experiment to check that. If that's the problem, then you need to run the add-in function using a direct call in your code. This is much nicer anyway, SendKeys is generally quite bugy.

Thank you for your reply, Cool blue.

I think you are right about SendKeys being the problem. I made a test sheet with a NOW() function and Calculate instead of SendKeys in the TimerSet sub.

Sub TimerSet()
Application.OnTime Now + TimeValue("00:00:30"), "TimerSet"
Calculate
End Sub

When the NOW() function exceeded my set "date limit" the alarm sounded, even with Excel minimized.

I will look into the option of running the update from the AddIn without use of SendKeys as you suggested.

Thanks again.

 

[Cool blue]

Just try like this...

Private Sub Workbook_Open() 
    TimerSet
End Sub  
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    StopTimer 
End Sub

And, as I said, the timer will not be killed by your current code.
The spreadsheet will keep re-opening if you close it.
You need to do it like this...

Public nextTime as Double

Sub TimerSet() 
    nextTime=Now + TimeValue("00:00:30")
    Application.OnTime nextTime, "TimerSet" 
    Calculate 
End Sub

Sub StopTimer() 
    Application.OnTime nextTime, "TimerSet", , False 
End Sub

I don't know why people put the Resume Next before killing the timer but I think its a bad idea. You should not get an error unless you do something wrong and you need to know about it.

But the fundamental problem is probably SendKeys

 

[north]

Thank you for your insight, Cool blue. I'll apply your improvements.