Use of Application.OnTime from a function

daveOverHere

New Member
Joined
Jul 30, 2015
Messages
2
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?
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
if you use Application.Volatile at the beginning of your function, it will force that function to recalulate when there is a calculation performed
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top