Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perplexing string manipulation problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perplexing string manipulation problem
It looks to me that you are using Dim wrong, They should be Dim Days as
Date, not integer the same for the others. "Russ Jones" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perplexing string manipulation problem
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perplexing string manipulation problem
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! "Skinman" wrote: It looks to me that you are using Dim wrong, They should be Dim Days as Date, not integer the same for the others. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Perplexing string manipulation problem
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! "Rick Rothstein (MVP - VB)" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
String manipulation problem | Excel Programming | |||
Importing Long String - String Manipulation (INVRPT) (EDI EANCOM 96a) | Excel Programming | |||
Importing Long String - String Manipulation (EDI EANCOM 96a) | Excel Programming | |||
String manipulation | Excel Programming | |||
Help with perplexing form problem | Excel Programming |