Get Milliseconds or Seconds from System Time with VBA

One could create a private type of “SYSTEMTIME”  to use with an API call to obtain the system time.  In the following example GetSystemTime is used.  You can use it in your own vba module to obtain system time milliseconds (which I have used for randomization purposes).

Although VBA has ways of getting this information too, you might want to ‘go outside’ of VBA to get the milliseconds and system time directly from the windows operating system.

How does one get the system time using windows api call to the Windows API kernal32?

In the top of the module type in the private type.

Private Type SYSTEMTIME

Year As Integer

Month As Integer

DayOfWeek As Integer

Day As Integer

Hour As Integer

Minute As Integer

Second As Integer

Milliseconds As Integer

End Type

Now, declare a function that references the the Windows API kernal32

Private Declare Sub GetSystemTime Lib “kernel32″ (lpSystemTime As SYSTEMTIME)

Now that you have declared GetSystemTime you can use it in a VBA function:

VBA Milliseconds Function

Public Function GetMyMilSeconds() As String

Dim tSystem As SYSTEMTIME

Dim sRet

GetSystemTime tSystem

sRet = Format(tSystem.Milliseconds, “000”)

GetMyMilSeconds = sRet

End Function

One note: VBA also provides a method of obtaining the milliseconds.

It is simpler to write. For example you can use this:

format(now(),”ms”)

 

Even so, if you want to rely on the more reliable windows OS time, it’s not hard to customize the GetMyMilSeconds function.

It is simple to create other functions that return other useful information like minutes, hours or seconds.

It is simple because of the Private Type SYSTEMTIME. Any of the items in that type can be used. Just change tSystem.Milliseconds in the line with  sRet = Format(tSystem.Milliseconds, “000”) to another value.

Here are possible functions.

VBA Seconds Function

This VBA Routine returns the seconds of the system time.

Public Function GetMySeconds() As String

Dim tSystem As SYSTEMTIME

Dim sRet

GetSystemTime tSystem

sRet = Format(tSystem.Second, “00”)

GetMySeconds = sRet

End Function

VBA Day Function

This function returns the day. For example if today is September 5 then it would return 05.

Public Function GetMyDay() As String

Dim tSystem As SYSTEMTIME

Dim sRet

GetSystemTime tSystem

sRet = Format(tSystem.Day, “00”)

GetMyDay = sRet

End Function

 

DayOfWeek VBA Function

This function returns the DayOfWeek. For example if today is Monday, it would normally return 1.

Public Function GetMyDayOfWeek() As String

Dim tSystem As SYSTEMTIME

Dim sRet

GetSystemTime tSystem

sRet = Format(tSystem.DayOfWeek, “00”)

GetMyDayOfWeek = sRet

End Function

 

 You can create the other stystem time components of Year and Month too.

Month from System Time

Public Function GetMyMonth() As String

Dim tSystem As SYSTEMTIME

Dim sRet

GetSystemTime tSystem

sRet = Format(tSystem.Month, “00”)

GetMyMonth = sRet

End Function

 

 Year from System Time

To obtain the system time year value you could create this function.

Public Function GetMyYear() As String

Dim tSystem As SYSTEMTIME

Dim sRet

GetSystemTime tSystem

sRet = Format(tSystem.Year, “0000”)

GetMyYear = sRet

End Function

 

How to Get the Hour and Year in VBA.

This relies on VBA so its easier to call. You might be better served using this simple code.

VBA HOUR

format(now,”HH”)

VBA YEAR

format(now,”YYYY”)

Editor’s Note: If you run into an error concerning ‘left hand assignment’ you might want to check out this post.