how to get Now() formated with millisecond ?

Question

Dear all,

I need to get the date time return by NOW(9 to be formatted using milliseconds as well, what is the way to do that using VBA ?

thnaks for advide

regards

Answer 1

The VBA function Now doesn't return milliseconds. You can use the Timer function for this purpose, for example:

Dim s As String
s = Format(Now, "yyyy-mm-dd hh:mm:ss") & Right(Format(Timer, "0.000"), 4)

P.S. the last digit will always be 0.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

(down vote) Incomplete answer. Timer not defined.

Google: Can I get VBA equivalent of DateTime.Now.Ticks?

Private Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Declare the GetSystemTime procedure:

Private Declare Sub GetSystemTime Lib "Kernel32" (ByRef lpSystemTime As SYSTEMTIME)
Call it by passing a SYSTEMTIME value by reference:

Public Sub Test()
    Dim t As SYSTEMTIME
    GetSystemTime t
    Debug.Print t.wYear, t.wMonth, t.wDay, t.wHour, t.wMinute, t.wSecond, t.wMilliseconds
End Sub

Answer 3

Timer is a built-in VBA function: see https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/timer-function

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

How Do You Convert Excel DateTime from Double to mm/dd/yyyy hh:mm:ss.000 format?

Please run the following simple sub:

Public Sub TimeWithMS()
Dim Timestamp As Variant
Timestamp = Evaluate("Now()")
Debug.Print Tab(0); "Timestamp:"; Tab(15); Timestamp
Debug.Print Tab(0); "How do you format the Timestamp output in the immediate window as: mm/dd/yyyy hh:mm:ss.000? (milliseconds required)"
Debug.Print Tab(0); "Thank you!"
End Sub