Saturday, August 04, 2012 3:30 PMModerator
I am having a small problem with Format(). Consider:
Dim t, st As String
t = TimeSerial(25, 13, 11)
.NumberFormat = "[hh]:mm:ss"
.Value = t
st = Format(t, "[hh]:mm:ss")
The value in A1 is correctly set to:
and the NumberFormat is also correctly set.
However, the MsgBox displays:
What is the problem and how do I fix it?
Saturday, August 04, 2012 4:29 PM
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
- Marked As Answer by Gary's Student MVPMVP, Moderator Saturday, August 04, 2012 4:45 PM
Saturday, August 04, 2012 4:46 PMModeratorThanks!
Saturday, August 04, 2012 5:08 PM
Wow!! Hans, you really deserve that 'MVP' title!!
Monday, August 06, 2012 12:09 PMModerator
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
- Marked As Answer by Gary's Student MVPMVP, Moderator Monday, August 06, 2012 12:39 PM
Monday, August 06, 2012 12:40 PMModerator
Thank you Peter.