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

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

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

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

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

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

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