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?
 

CalcSux78

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
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

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

Forum statistics

Threads
1,082,269
Messages
5,364,150
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top