Format system time to milliseconds in VBA

[MMcCarthy]

I found I needed this recently when creating a file which had to be uploaded by ftp. The file extension was a timestamp of hhnnssms and the ms part was formatted to 4 digits. As the Now() function doesn't return milliseconds I used the following function to calculate it.

Option Compare Database
Option Explicit

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


Private Declare Sub GetSystemTime Lib "kernel32" _
(lpSystemTime As SYSTEMTIME)

Public Function TimeToMillisecond() As String
Dim tSystem As SYSTEMTIME
Dim sRet

    On Error Resume Next
    
    GetSystemTime tSystem
    
    sRet = Hour(Now) & Minute(Now) & Second(Now) & Format(tSystem.wMilliseconds, "0000")
    
    TimeToMillisecond = sRet

End Function

As usual all advice, critique and enhancements welcome :)

Mary

[topher23]

I don't know how I missed this post... I was trying to do this very thing on Friday using SYSTEMTIME, got frustrated and just stuck with seconds. Thanks for providing a usable function, Mary!

[MMcCarthy]

You're welcome !