Format user input from inputbox into a lap time

[reyreyreyes]

Hi all

I have an inputbox that flashes up when a button is clicked asking a user for a lap time in 00:00.000 (minutes, seconds, milliseconds) format. However, because I know a lap time may be entered as 00:00:000, I'd like to have it converted so it can be reported on in a consistent way later.

So far, I have:



mystr1 = InputBox("Please state the new fastest lap in 00:00.000 format", "Fastest Lap Updater")
mystr1 = Format(mystr1, "#0:#0.000")


But, it's not working. Frustratingly, I had this partially working last night but I changed it again after and forgot what I'd done (noob).

Any ideas?

Thanks.

[GarysStudent]

The usual way to handle times in VBA is to Dim the variable as Date. However Date variable do not handle milliseconds very well. You might have better luck using Double:


Sub dural()
Dim s As String, db As Double
Dim mill As Double
Dim secs As Double, mins As Double

s = Application.InputBox("Please state the new fastest lap in 00:00.000 format", "Fastest Lap Updater", Type:=2)
ary = Split(s, ".")
mill = CDbl(ary(1))
mins = CDbl(Split(ary(0), ":")(0))
secs = CDbl(Split(ary(0), ":")(1))

db = mins / (24# * 60#) + secs / (24# * 60# * 60#) + mill / (24# * 60# * 60# * 1000#)
ActiveCell.Value = db
ActiveCell.NumberFormat = "hh:mm:ss.000"
End Sub

[Paul_Hossler]

GarysStudent is correct if you want to compute with them you need to have the times in a number

Anyway, I see a lot of error checking

No colon entered
A space in the middle
No dot entered
lap time < 1 min so an entry of 45.23
etc.


If you just want consistent format, you could force a correct format to be entered by using Like and a mask



Option Explicit
Sub test()
Dim mylap As Date
Dim mystr1 As String
mystr1 = "1:2.34"
' If Not mystr1 Like "##:##.###" Then MsgBox "No Good"

mystr1 = "01:2"
' If Not mystr1 Like "##:##.###" Then MsgBox "No Good"


mystr1 = "01:02.123"
If Not mystr1 Like "##:##.###" Then MsgBox "No Good"


End Sub

[reyreyreyes]

Thanks guys.

GarysStudent, thanks very much. If that does what I want it to then great! However, I'm quite clueless when it comes to understanding how on earth that code is working, and I try to only use code I actually get! :)

Paul, that looks like the way forward, so thanks.

To anyone/everyone, I thought there'd be a simpler way to validate a ##:##.###. I thought maybe a custom function could easily be created but that really isn't the case? Is this a limitation of VBA, Excel, or both?

[Paul_Hossler]

To anyone/everyone, I thought there'd be a simpler way to validate a ##:##.###. I thought maybe a custom function could easily be created but that really isn't the case? Is this a limitation of VBA, Excel, or both?


It all depends what you mean by 'Validate'

The simple Like in my example will match (i.e. accept) a certain pattern

Like will do more sophisticated pattern matching



mystr1 = "01:92.123"
If Not mystr1 Like "[0-3][0-9]:[0-5][0-9].###" Then MsgBox "No Good"


The first minutes char must be 0,1,2, or 3; then second between 0 and 9

The first seconds char must be between 0 and 5, and second between 0 and 9

The MS can be and 3 chars 0-9


So mystr1 = "01:92.123" fails because of the 9

So mystr1 = "91:22.123" fails because of the 9

So mystr1 = "01:12.123" would 'pass'

Telling the user that they need to correct their input would be up to you

I've written functions that do more complicated checking and return True or False

You could write a function that is more sophisticated and normalizes an input if it is possible
So a user input like
"9:4.6" could be normalized into your 'standard' i.e. "09:04.600"
"4.6" could be normalized into your 'standard' i.e. "00:04.600"
"9:4" could be normalized into your 'standard' i.e. "09:04.000"


Again, you would need to communicate back to the user that their input is bad

If you're interested in something like that, ask here and I'm sure you're get a lot of ideas

[reyreyreyes]

mystr1 = "01:92.123"
If Not mystr1 Like "[0-3][0-9]:[0-5][0-9].###" Then MsgBox "No Good"


The first minutes char must be 0,1,2, or 3; then second between 0 and 9

You could write a function that is more sophisticated and normalizes an input if it is possible
So a user input like
"9:4.6" could be normalized into your 'standard' i.e. "09:04.600"
"4.6" could be normalized into your 'standard' i.e. "00:04.600"
"9:4" could be normalized into your 'standard' i.e. "09:04.000"


Again, you would need to communicate back to the user that their input is bad

If you're interested in something like that, ask here and I'm sure you're get a lot of ideas

Love that Paul. I get the 'Like' statements 100%.

As for the function side of things, I don't think I'm quite there yet but may well take your advice and question that a little later, if research doesn't help me.

Thanks again Paul -- absolutely perfect!

[Paul_Hossler]

NP -- you can play or start with something like this

It just takes a variety of inputs (with some assumptions) and tries to return a nn:nn.nnn formatted string.

You can't do any math with this (use GarysStudent's macro if you need)



Option Explicit

Sub drv()
Dim s As String

' MsgBox NormalizeLaptimes("1:34.a67")
' MsgBox NormalizeLaptimes("12:34.567")
' MsgBox NormalizeLaptimes("2:34.567")
' MsgBox NormalizeLaptimes(":34.567")
' MsgBox NormalizeLaptimes("34.567")
' MsgBox NormalizeLaptimes("4.567")
' MsgBox NormalizeLaptimes("4.5")
' MsgBox NormalizeLaptimes(".5")
' MsgBox NormalizeLaptimes("12:.5")

s = NormalizeLaptimes("1:34.a67")
If Len(s) = 0 Then
MsgBox "Bad Input"
Else
MsgBox s
End If
End Sub



Function NormalizeLaptimes(s) As String
Dim iColon As Long, iDot As Long
Dim iMin As String, iSec As String, iMilli As String

'set the default return
NormalizeLaptimes = vbNullString
iColon = InStr(s, ":")
iDot = InStr(s, ".")

Select Case iColon
Case 0, 1
iMin = "00"
Case 2
iMin = "0" & Left(s, 1)
Case 3
iMin = Left(s, 2)
Case Else
Exit Function
End Select

'must be a number
If Not IsNumeric(iMin) Then Exit Function

'minutes must be >= 0 and <= 20
If iMin < "00" Or iMin > "20" Then Exit Function


Select Case iDot - iColon - 1
Case 0
iSec = "00"
Case 1
iSec = "0" & Mid(s, iColon + 1, 1)
Case 2
iSec = Mid(s, iColon + 1, 2)
Case Else
Exit Function
End Select

'must be a number
If Not IsNumeric(iSec) Then Exit Function

'seconds must be >= 0 and <= 59
If iSec < "00" Or iSec > "59" Then Exit Function

Select Case Len(s) - iDot
Case 0
iMilli = "000"
Case 1
iMilli = Right(s, 1) & "00"
Case 2
iMilli = Right(s, 2) & "0"
Case 3
iMilli = Right(s, 3)
Case Else
Exit Function
End Select

'must be a number
If Not IsNumeric(iMilli) Then Exit Function

NormalizeLaptimes = iMin & ":" & iSec & "." & iMilli
End Function