Use of Application.OnTime from a function

[daveOverHere]

I am trying to simulate some logic within a spreadsheet in Excel.

All the basic logic gates are obvious but I am having trouble generating a pulse function.

My intention was to create a function that would set the value of the cell to 1 and then clear it down to 0 after a delay using the Application.OnTime method.

When I use the OnTime method within a sub all is well, but as soon as I try to call it from a function it seems to wander off and get lost.

An example of what works is:
Sub test()
Application.OnTime Now + TimeValue("00:00:02"), "clearPulseDown"
End Sub

Sub clearPulseDown()
MsgBox "yay!"
End Sub

If I run 'test', then 2 seconds later the message box appears.

However, if I create a function "Pulse" and use the function in a spreadsheet I cannot get the message box even though I know that the code is running as I see lastOP changing for the following code (don't worry that the logic for the pulse is not correct, I have trashed everything in a attempt to get the OnTime to work).

Function Pulse(IP As Range) As Integer
If IP = 1 Then
Application.OnTime Now() + TimeValue("00:00:02"), "clearPulseDown"
Pulse = 1
Else
Pulse = 0
End If
End Function

Sub clearPulseDown()
MsgBox "yay!"
End Sub
I tried the following to see that I could call a sub from a function ad it all works:

Function Pulse(IP As Range) As Integer
If IP = 1 Then
clearPulseDown
Pulse = 1
Else
Pulse = 0
End If
End Function

Sub clearPulseDown()
MsgBox "yay!"
End Sub

Is anyone able to point out where I am wrgoing wrong?

 

[CalcSux78]

if you use Application.Volatile at the beginning of your function, it will force that function to recalulate when there is a calculation performed

 

[daveOverHere]

Thanks CalcSux78. I gave it a shot and there is no change.

The function is called when the input changes and is working apart from the application.ontime.

I did just try a slight variation in which I call a subroutine which uses application.wait before popping up the message box and found that the message box is displayed but the wait is ignored. I do not want to use the wait as there could be a number of pulses that are used and this would become unworkable.

Function Pulse(IP As Range, lastOP As Range) As Integer
If IP = 1 And lastOP = 0 Then
clearPulseDown
Pulse = 1
Else
Pulse = 0
End If
End Function

Sub clearPulseDown()
Application.Wait Now + TimeValue("0:00:05")
' Range("I11") = 0
MsgBox "yay!"
End Sub

It also appears that my original plan will not work anyway as excel detects that the cell will be over-written when I try to reset it to 0 and calls it a circular reference.

My guess is that excel disables the delays to ensure that the function recalculates quickly and the spreadsheet does not lock up. It looks like I need to find a different route.