VBA Format() Function

Question

I am having a small problem with Format().  Consider:

Sub TimeFormat()
Dim t, st As String
t = TimeSerial(25, 13, 11)
With Range("A1")
    .Clear
    .NumberFormat = "[hh]:mm:ss"
    .Value = t
End With
st = Format(t, "[hh]:mm:ss")
MsgBox st
End Sub

The value in A1 is correctly set to:

25:13:11

and the NumberFormat is also correctly set.

However, the MsgBox displays:

:12:11

What is the problem and how do I fix it?

---

gsnu201208

Answer 1

Square brackets in a time format are valid in a cell format in Excel, but the Format function in VBA doesn't recognize them. Time formats in VBA are always clock time, there is no support for cumulative time.

You can use

    st = Range("A1").Text
    MsgBox st

to display the time as formatted in Excel.

---

Regards, Hans Vogelaar

Answer 2

Thanks!

---

gsnu201208

Answer 3

Wow!!  Hans, you really deserve that 'MVP' title!!

Good stuff!!!!!

---

Ryan Shuell

Answer 4

Another way, without using a Cell

Dim t As Date, st As String
t = TimeSerial(25, 13, 11)
st = Int(t * 24) & Format(t, ":nn:ss")
MsgBox st

Note nn not mm

Peter Thornton

Answer 5

Thank you Peter.

---

gsnu201208