Excel function to update time every minute

R

Richard Edwards

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
 
P

Peter T

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
 
R

Richard Edwards

thanks for that but it will not be available as a custom function...?


Peter T said:
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 said:
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
 
P

Peter T

What do you mean by a custom function, what do you want it to return, what's
the objective.

Regards,
Peter T

Richard Edwards said:
thanks for that but it will not be available as a custom function...?


Peter T said:
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 said:
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
 
R

Richard Edwards

sorry - user defined function....so i can put a cell =customtime() and it
will update the time once a minute.

Peter T said:
What do you mean by a custom function, what do you want it to return,
what's the objective.

Regards,
Peter T

Richard Edwards said:
thanks for that but it will not be available as a custom function...?


Peter T said:
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

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
 
P

Peter T

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 said:
sorry - user defined function....so i can put a cell =customtime() and it
will update the time once a minute.

Peter T said:
What do you mean by a custom function, what do you want it to return,
what's the objective.

Regards,
Peter T

Richard Edwards said:
thanks for that but it will not be available as a custom function...?


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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top