Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
Afternoon all,
Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
Private nextTime As Date
Sub UpdateTime() nextTime = Now + TimeValue("00:00:02") ' << change to 00:01:00 Application.OnTime nextTime, "TimeUp" End Sub Sub TimeUp() Static n As Long ' just for testing n = n + 1 Cells(n, 1) = Time UpdateTime End Sub Sub StopUpdate() If nextTime Then Application.OnTime nextTime, "TimeUp", , False nextTime = 0 End If End Sub Sub auto_close() ' be sure to stop in a close event StopUpdate End Sub Regards, Peter T "Richard Edwards" wrote in message ... Afternoon all, Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
thanks for that but it will not be available as a custom function...?
"Peter T" <peter_t@discussions wrote in message ... Private nextTime As Date Sub UpdateTime() nextTime = Now + TimeValue("00:00:02") ' << change to 00:01:00 Application.OnTime nextTime, "TimeUp" End Sub Sub TimeUp() Static n As Long ' just for testing n = n + 1 Cells(n, 1) = Time UpdateTime End Sub Sub StopUpdate() If nextTime Then Application.OnTime nextTime, "TimeUp", , False nextTime = 0 End If End Sub Sub auto_close() ' be sure to stop in a close event StopUpdate End Sub Regards, Peter T "Richard Edwards" wrote in message ... Afternoon all, Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
What do you mean by a custom function, what do you want it to return, what's
the objective. Regards, Peter T "Richard Edwards" wrote in message ... thanks for that but it will not be available as a custom function...? "Peter T" <peter_t@discussions wrote in message ... Private nextTime As Date Sub UpdateTime() nextTime = Now + TimeValue("00:00:02") ' << change to 00:01:00 Application.OnTime nextTime, "TimeUp" End Sub Sub TimeUp() Static n As Long ' just for testing n = n + 1 Cells(n, 1) = Time UpdateTime End Sub Sub StopUpdate() If nextTime Then Application.OnTime nextTime, "TimeUp", , False nextTime = 0 End If End Sub Sub auto_close() ' be sure to stop in a close event StopUpdate End Sub Regards, Peter T "Richard Edwards" wrote in message ... Afternoon all, Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
sorry - user defined function....so i can put a cell =customtime() and it
will update the time once a minute. "Peter T" <peter_t@discussions wrote in message ... What do you mean by a custom function, what do you want it to return, what's the objective. Regards, Peter T "Richard Edwards" wrote in message ... thanks for that but it will not be available as a custom function...? "Peter T" <peter_t@discussions wrote in message ... Private nextTime As Date Sub UpdateTime() nextTime = Now + TimeValue("00:00:02") ' << change to 00:01:00 Application.OnTime nextTime, "TimeUp" End Sub Sub TimeUp() Static n As Long ' just for testing n = n + 1 Cells(n, 1) = Time UpdateTime End Sub Sub StopUpdate() If nextTime Then Application.OnTime nextTime, "TimeUp", , False nextTime = 0 End If End Sub Sub auto_close() ' be sure to stop in a close event StopUpdate End Sub Regards, Peter T "Richard Edwards" wrote in message ... Afternoon all, Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
A UDF can only return a value, it cannot do or invoke anything that will
change the interface, such as writing a value to some other cell, or "update itself later" (albeit there are some trick type workarounds). For your needs why not assign a macro to a button (you could trap and store the activecell as the cell that will receive the future updates). Alternatively you could start/stop updates in sheet and/or workbook activate/deactivate events. For either approach, adapt the code I posted previously. Regards, Peter T "Richard Edwards" wrote in message ... sorry - user defined function....so i can put a cell =customtime() and it will update the time once a minute. "Peter T" <peter_t@discussions wrote in message ... What do you mean by a custom function, what do you want it to return, what's the objective. Regards, Peter T "Richard Edwards" wrote in message ... thanks for that but it will not be available as a custom function...? "Peter T" <peter_t@discussions wrote in message ... Private nextTime As Date Sub UpdateTime() nextTime = Now + TimeValue("00:00:02") ' << change to 00:01:00 Application.OnTime nextTime, "TimeUp" End Sub Sub TimeUp() Static n As Long ' just for testing n = n + 1 Cells(n, 1) = Time UpdateTime End Sub Sub StopUpdate() If nextTime Then Application.OnTime nextTime, "TimeUp", , False nextTime = 0 End If End Sub Sub auto_close() ' be sure to stop in a close event StopUpdate End Sub Regards, Peter T "Richard Edwards" wrote in message ... Afternoon all, Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel function to update time every minute
ok. thanks for your help.
rich "Richard Edwards" wrote in message ... Afternoon all, Is it possible to create custom function that updates the time every minute? I have the following code that will update the time: Sub UpdateTime() Application.OnTime Now + TimeValue("00:01:00"), "TimeUp" End Sub Sub TimeUp() [a1] = Time End Sub But i have no idea how to wrap this (if it is even possible!) into a custom function... Thank you. Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time and Minute formulas | Excel Programming | |||
HELP! time on a 45 minute Agenda | Excel Discussion (Misc queries) | |||
How do I get time elapsed in terms of minute? | Excel Discussion (Misc queries) | |||
Using time formats in minute units | Excel Discussion (Misc queries) | |||
convert time from 60 minute hour to 100 minute hour | Excel Discussion (Misc queries) |