Perplexing string manipulation problem

[Russ Jones]

I seem to have an inexplicable error which I hope someone can help me solve.
I am using Excel 2003 running on a Windows XP Professional platform.

I wrote the following user-defined function to convert to seconds an elapsed
time stored as a text string, BuildTime, which has the format dd:hh:mm:ss.
Only the necessary positions in the string are filled, i.e., one minute is
stored as 1:00, ten minutes as 10:00, one hour as 1:00:00, ten hours as
10:00:00, one day as 1:00:00:00, ten days as 10:00:00:00, etc. Values of
less than one minute are stored as 0:ss. Therefore, one and only one of the
procedure's "If StringLen =" tests is true for any value of BuildTime.

The problem is that the procedure invariably returns a #VALUE! error when,
and only when, StringLen = 8, and I am a completely baffled as to why. Any
help would be greatly appreciated!

Dim Days As Integer
Dim Hours As Integer
Dim Minutes As Integer
Dim Seconds As Integer
Dim StringLen As Integer

Function TimeInSeconds(BuildTime As String)
Days = 0 'Initializing these variables makes no difference; I was
Hours = 0 'merely trying everything I could think of.
Minutes = 0
Seconds = 0
StringLen = Len(BuildTime)
If StringLen = 11 Then
Days = Val(Mid(BuildTime, 1, 2))
Hours = Val(Mid(BuildTime, 4, 2))
Minutes = Val(Mid(BuildTime, 7, 2))
End If
If StringLen = 10 Then
Days = Val(Mid(BuildTime, 1, 1))
Hours = Val(Mid(BuildTime, 3, 2))
Minutes = Val(Mid(BuildTime, 6, 2))
End If
If StringLen = 8 Then
Days = 0
Hours = Val(Mid(BuildTime, 1, 2))
Minutes = Val(Mid(BuildTime, 4, 2))
End If
If StringLen = 7 Then
Days = 0
Hours = Val(Mid(BuildTime, 1, 1))
Minutes = Val(Mid(BuildTime, 3, 2))
End If
If StringLen = 5 Then
Days = 0
Hours = 0
Minutes = Val(Mid(BuildTime, 1, 2))
End If
If StringLen = 4 Then
Days = 0
Hours = 0
Minutes = Val(Mid(BuildTime, 1, 1))
End If
Seconds = Val(Right(BuildTime, 2))
TimeInSeconds = (Days * 86400) + (Hours * 3600) + (Minutes * 60) + Seconds
End Function

 

[Skinman]

It looks to me that you are using Dim wrong, They should be Dim Days as
Date, not integer the same for the others.

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure what your problem is, but I think this function does what you
are attempting to do with your function (plus it is a little bit
shorter<g>)...

Function TimeInSeconds(BuildTime As String) As Long
Dim X As Long
Dim Factors As Variant
Dim Parts() As String
Factors = Array(1, 60, 3600, 86400)
Parts = Split(BuildTime, ":")
For X = 0 To UBound(Parts)
TimeInSeconds = TimeInSeconds + Factors(X) * Parts(UBound(Parts) - X)
Next
End Function

Rick

 

[Russ Jones]

Perhaps you're right, but I'm really trying to deal with elapsed time as a
number of days, hours, minutes, and seconds rather than as a calendar
date/time. Although I've already received a most elegant solution, I remain
perplexed as to why my code only fails when the length of the string equals
eight, and I plan to experiment with your suggestion if for no other reason
than as a learning exercise, so I do truly appreciate your input. Thanks!

 

[Russ Jones]

Thank you so much, Rick! This works beautifully, and is ever so much more
elegant than my attempt. Do you suppose anyone can tell that I'm just an
old, rusty COBOL programmer who's not particularly conversant in VBA? LOL!

Now on to the flip side of this coin: trying to convert seconds back into a
dd:hh:mm:ss text value. We'll see if this old dog has learned any new tricks!