Passing a cell value to a function

[Vishak]

I am looking for a user defined function which will call a message sub routine using the application.ontime method . I am passing arguments to the function that will execute at a certain time taken from selected cell . But my function doesnt work . I dont understand why. Can anyone help me with the codes and explain why my code dont work.

Function alarm (selectedcell as range)
sc=selectedcell.row
application.ontime Timevalue( cells(sc,1).text)," message"
End function

Sub message()
Msgbox " tested"
end sub

 

[joeu2004]

[.... deleted by me; posted prematurely ....]

 

[Rick Rothstein]

I am looking for a user defined function which will call a message sub routine using the application.ontime method . I am passing arguments to the function that will execute at a certain time taken from selected cell . But my function doesnt work . I dont understand why. Can anyone help me with the codes and explain why my code dont work.

Function alarm (selectedcell as range)
sc=selectedcell.row
application.ontime Timevalue( cells(sc,1).text)," message"
End function

Sub message()
Msgbox " tested"
end sub

I have no idea whether what you are trying to do is possible or not, but if it is to work, you would need to remove the space in front of the word "message" inside the quote I highlighted above.

 

[joeu2004]

you would need to remove the space in front of the word "message" inside the quote I highlighted above.

Good eye; and good thought! But is seems to work just fine. Test it.

Sub testit()
On Error Resume Next
Application.OnTime Now() + TimeSerial(0, 0, 2), " message"
MsgBox Now() & "  ontime error " & Err
End Sub

Sub message()
MsgBox Now() & "  message"
End Sub

 

[joeu2004]

Can anyone help me with the codes and explain why my code dont work.

Define "doesn't work". If you get an Excel error message, what is it (#NAME ?)? If you get a VBA error message, what is it? Or do you simply mean that you never see the "tested" Msgbox?

Function alarm (selectedcell as range)
sc=selectedcell.row
application.ontime Timevalue( cells(sc,1).text)," message"
End function

I am suspicious of the fact that things are not capitalized per VBA editor auto-corrections. It seems that you did not copy-and-paste from the VBA editor. Consequently, you might have unconsciously corrected the very error(?) that you are asking about.

What module did you enter the VBA function into?

It must be in a normal module (click Insert > Module), not a worksheet or ThisWorkbook module. Otherwise, Excel does not recognize the VBA function, and it should display a #NAME error in the worksheet.

Cells(sc,1) is the cell in column A of the row that contains selectedcell. Is that your intention?

What does Cells(sc,1).Text return?

What does Now() and TimeValue(Cells(sc,1).Text) return?

Be sure to format those values with 15 significant digits; for example, Format(Now(), "0.00000000000000E+0"). That's 14 zeros after the decimal point.

Note that TimeValue(...) returns only the time part, not also the date part. If the time part is before Now(), it refers to that time of day of the next day. If the time part is the same as Now() truncated to the second, it might refer to that time of day of the next day if, by coincidence, Now() is sufficiently close to the next second, and that second occurs before OnTime completes the scheduling of the event. That is called a "race condition".

It might be more reliable to write CDate(Cells(sc,1).Text), if the cell displays date as well as time.

 

[mikerickson]

I am looking for a user defined function which will call a message sub routine

The code in the OP looks like you want to have a message displayed whenever the value in a cell (selectedcell) is changed.

VBA is not needed for that. Data Validation will do that
Select a cell, invoke the Data Validation dialog.
Us the custom formula =FALSE
on the Error Alert tab, set the Style to Information and enter the error message of your choosing ("tested" based on the OP)

 

[Rick Rothstein]

Seems to work just fine. Test it.

Sub testit()
On Error Resume Next
Application.OnTime Now() + TimeSerial(0, 0, 2), " message"
MsgBox Now() & "  ontime error " & Err
End Sub

Sub message()
MsgBox Now() & "  message"
End Sub

Hmm, you are right, the space does not seem to matter (I am a little surprised at that). I still am not sure that you do that code as a UDF in the way the OP was looking for. Seems like that kind of timed call to a subroutine would violate the "rule" about functions not being able to affect the Excel environment.

 

[joeu2004]

I still am not sure that you do that code as a UDF in the way the OP was looking for. Seems like that kind of timed call to a subroutine would violate the "rule" about functions not being able to affect the Excel environment.

Right! I forgot that I had tested that some time ago.

The OnTime statement in the UDF does not return an error(!). But in fact, the event is not scheduled. Test it.

Dim eventtime As String

' To confirm basic operation
' Expect no error
Sub doit()
On Error Resume Next
eventtime = Now() + TimeSerial(0, 0, 2)
Application.OnTime eventtime, "message"
MsgBox Now() & "  ontime " & eventtime & "  error " & Err
End Sub

' B1:  =NOW() + TIME(0,0,2) formatted as m/d/yyyy hh:mm:ss
' A1:  =callit(B1)
' Expect no error
Function callit(r As Range)
On Error Resume Next
eventtime = r.Text
Application.OnTime eventtime, "message"
MsgBox Now() & "  ontime " & eventtime & "  error " & Err
End Function

' Expect to see after executing doit()
' Expect not to see after executing callit()
Sub message()
MsgBox Now() & "  message"
End Sub

' Execute more than 2 sec after entering or recalculating A1
' Expect an error
Sub endtest()
On Error Resume Next
Application.OnTime eventtime, "message", , False
MsgBox Now() & "  cancel ontime " & eventtime & "  error " & Err
End Sub

 

[joeu2004]

Errata....

Dim eventtime As String
[....]
Application.OnTime eventtime, "message"

I'm surprised that works. I meant to write CDate(eventtime).

 

[joeu2004]

But even though Rick probably identified Vishak's error, I do hope that Vishak pays attention to the questions in my post #5 for future reference. They should have answered in the original posting.