Search code examples
vbafunctionoffice365

Get System Up Time in VBA excel

I need to obtain the system uptime in excel vba, this is available on the Task Manager as such: TASK MANAGER

I found the below online from 7 years ago:

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long

Sub test()
Debug.Print GetTickCount()
End Sub

this outputs : 206329937

if i devide this by 8400 i get 15 days when it should be 2 days 9 hours etc.

Not sure whats going wrong. Also read online that I should use an IF formula to declare if its 64 bit or 32 bit but im also not sure how to do this?

Any advice would be great thank you.

UPDATE: Turns out the first 6 numbers : 206329 will convert to the right time. I have no idea what the remaining numbers mean does anyone know?

Dim NewTime
NewTime = (Left(GetTickCount(), 6)) / 86400

Debug.Print NewTime

I tried to convert the time to dd:hh:mm:ss format and it only works when its formatted on the worksheet as a custom cell format.

Very weird no idea again why this is the case but this is the result i get when using :

Debug.Print Format(NewTime, "DD:MM:HH:SS")

01:01:09:27

very confusing !

Solution

  • Your main error is that you divide by 8400.

    The ticks are measured in ms (milliseconds). So to get the number of days, you need to divide by 24 * 60 * 60 * 1000 (hours, minutes, seconds, milliseconds).

    That would give 2.388 days in your case.

    To get the number of days, hours, minutes and seconds, you just need some math. The logic is to calculate the full number of days, subtract the number of ticks used by that from the original number of ticks. Now do the same for hours (but divide only by (60 * 60 * 1000) and so on.

    The following code does that with the help of a small function - note that the first parameter (ticks) is passed ByRef and modified in that function. Also note that I used 24& to force VBA to use a long value, else 24 * 60 * 60 will throw an overflow error.

    Sub test()
    Dim ticks  As Long
    ticks = GetTickCount()
    ' Just to test OP value:
    ticks = 206329937

    Dim days As Long, hours As Long, minutes As Long, seconds As Long
    days = getTimepart(ticks, 24& * 60 * 60)
    hours = getTimepart(ticks, 60 * 60)
    minutes = getTimepart(ticks, 60)
    seconds = getTimepart(ticks, 1)
    Debug.Print days, hours, minutes, seconds
End Sub

Function getTimepart(ByRef ticks As Long, divisor As Long) As Long
    divisor = divisor * 1000
    getTimepart = Int(ticks / divisor)
    ticks = ticks - (getTimepart * divisor)
End Function

    In your case, this prints

     2             9             18            49