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

Question

In VB within .NET I can use DateTime.Now.Ticks.ToString("x") to get a string ready for a multipart form boundary e.g. 8d4b7556f42792c

I'm trying to get the same thing in VBA within Excel.

In Excel VBA I can use DateTime.Now to get now in the format: 19/06/2017 20:54

That's not really helpful though, I need to convert it to string of ticks and then convert to hexadecimal. So far I've found that HEX(DateTime.Now) just gives me a four char return like A79A which is not what I need.

CAN I get ticks from DateTime.Now ?!

EDIT: I've just found this trick to get some sort of Tick Count:

Private Declare Function GetTickCount Lib "kernel32" () As Long
MsgBox (GetTickCount())

And that gives me a negative double or so... that might do for what I need, but is it TickCount as in NOW?

Maciej - yeah I just tried `MsgBox Now` without the `DateTime` and it worked. But it also works with it and gives me the intellisense list of possible methods etc. ? — jamheadart, Jun 19 '17 at 20:07
Check [this](https://stackoverflow.com/questions/939230/how-to-get-a-datediff-value-in-milliseconds-in-vba-excel) — Maciej Los, Jun 19 '17 at 20:16
@MaciejLos `DateTime` is the name of the module; the fully qualified name is `VBA.DateTime.Now`, where `VBA` is the library, `DateTime` is the module, and `Now` is a property getter that returns a `Date` value. Fully qualifying member calls is always a good idea. — Mathieu Guindon, Jun 19 '17 at 21:06

score 4 · Accepted Answer · edited Sep 21 '17 at 18:05

Excel is irrelevant, DateTime is a module from the VBA standard library, which is always referenced in any VBA project, regardless of the host.

A Date in VBA is essentially a Double where the integer part is the number of days since December 30, 1899:

?CDate(1)
12/31/1899

The decimal part represents the time part of that value, i.e. one hour is 1/24th, one minute is 1/60th of that, one second is 1/60th of that, and one millisecond would be 1/1000th of that...

You should read up on GetTickCount on MSDN before using it:

The elapsed time is stored as a DWORD value. Therefore, the time will wrap around to zero if the system is run continuously for 49.7 days. To avoid this problem, use the GetTickCount64 function. Otherwise, check for an overflow condition when comparing times.

If you're getting a negative tick count, it means your system has been up for quite a while and overflowed the DWORD size, 32 bits (i.e. 2³¹-1, or 2,147,483,647)

Instead of using GetTickCount and working with more or less meaningless "ticks" (being relative to system uptime [and only precise to ~15ms]), why not work with the actual system time and get actual milliseconds?

Define a SYSTEMTIME struct:

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

The retrieved value will be UTC time, so if you need local time, apply the appropriate offset.

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

1 Answers1