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]

Errata....

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

Actually, it is scheduled. It is just that when the event arrives, it does not call the event procedure.

IMHO, this is not due to the rule that UDFs cannot change the Excel environment.

In general, a VBA subroutine can be called from in a UDF context (i.e. from a VBA function that is called from an Excel worksheet directly or indirectly). But VBA does not permit subroutines in that context to change the Excel environment.

The same VBA subroutine can change the Excel environment if it is not called in a UDF context (i.e. from a VBA subroutine or function that is not called from an Excel worksheet directly or indirectly).

It is simply a context state.

With OnTime, the call to the VBA subroutine is simply deferred. There is no conceptual reason why VBA could not remember that it was scheduled in a UDF context and allow it to execute in general, but simply not permit it to change the Excel environment, just as it does when the UDF context is entered directly. SMOP!

Only a MSFT engineer could tell us if this is a design oversight (maybe even something MSFT might fix in the future; yeah, it our dreams!), or if was a conscious design decision and why.