VBA Now() In Excel

[davidgottlieb]

Is there anyway to view the date and time in real time. i.e. be able to view the time down to the second?

[parksie]

Why not just have a timer that checks every 1/2 a second, updating it with Now()?

[davidgottlieb]

Can you do that in VBA in Excel????

[parksie]

Is this to add it to a cell? Use the SetTimer and KillTimer API functions, and set the callback function to update the cell.

Open up a blank workbook. Add a new module, and enter this code:

Public Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Sub TimerProc(hwnd As Long, uMsg As Long, idEvent As Integer, dwTime As Long)
    Sheet1.Cells(1, 1) = Now
End Sub

Then, in your ThisWorkbook object, add this code:

Dim lTimer As Long
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    KillTimer 0, lTimer
End Sub
Private Sub Workbook_Open()
    lTimer = SetTimer(0, 1, 500, AddressOf TimerProc)
    Debug.Print lTimer
End Sub

You will see that it is the TimerProc function that makes the change, so you can add your custom action here.

[davidgottlieb]

Can you break that down a bit. I'm a beginner. I am not very experienced.

[parksie]

The SetTimer and KillTimer functions allow you to stop and start timers. If you give it the handle to a window, it sends it a message, saying that the timer went off. If you don't, then you can pass a function to be called - in this case, TimerProc. The code in the Workbook simply stops and starts the timer when it opens or closes.

[davidgottlieb]

I can't get this to work. How do I do this...Please be patient with me. I'm not very good.

[da_silvy]

try this, it will update the time on cell "a1" every second

Sub startit()
Dim MyStr As String
Range("a1").Select
MyStr = Format(Time, "hh:mm:ss AMPM")
ActiveCell.Formula = MyStr
Application.OnTime Now + TimeValue("00:00:01"), "startit"
End Sub