CODEWITHSUNDEEP
vbaexcelexcel-2010
extensa5620
extensa5620

Reputation: 699

Storing an array of time in VBA array

i want to store an array of time in VBA, like this but VBA rejects.

Dim timeA() As Variant
timeA = Array(01:42:32.75, 02:26:34.22, 05:03:23.54)

Instead I have to store it as serial instead like this.

Dim timeA() As Variant
timeA = Array(0.0712123842592593, 0.101784953703704, 0.21068912037037)

What can i do to store time in hh mm ss . msms?

Upvotes: 0

Views: 2565

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Timeserial will convert hours, minutes, and seconds into a Time, but it doesn't handle milliseconds. If you don't need ms, you could use

Sub storetime()

    Dim vaTime As Variant
    Dim i As Long

    vaTime = Array(TimeSerial(1, 42, 32.75), TimeSerial(2, 26, 34.22), TimeSerial(5, 3, 23.54))

    For i = LBound(vaTime) To UBound(vaTime)
        Debug.Print Format(vaTime(i), "hh:mm:ss")
    Next i

End Sub

You might add ms like

vaTime = Array(TimeSerial(1, 42, 32) + (0.75 / 24 / 60 / 60), TimeSerial(2, 26, 34) + (0.22 / 24 / 60 / 60), TimeSerial(5, 3, 23) + (0.54 / 24 / 60 / 60))

Inspecting those values seems like it works, but I can't get the milliseconds to display, so I'm not sure what good it does you.

Upvotes: 0

Excel Developers
Excel Developers

Reputation: 2825

Try this:

Dim timeA() As Variant
timeA = Array(#01:42:32#, #02:26:34#, #05:03:23#)

Unfortunately this won't work with fractions of seconds.

Upvotes: 1

Belial09
Belial09

Reputation: 694

try this, does it work?

Dim timeA() As Variant
timeA = Array("01:42:32.75", "02:26:34.22", "05:03:23.54")

Upvotes: 0

Related Questions