0

This is the code. It converts timestamp to total seconds. It works just fine. Here it outputs 42.2143012

Module VBModule
    Sub Main()
        Console.WriteLine(ConvertToSeconds("00:00:42.2143012"))
    End Sub
    
    Function ConvertToSeconds(timestamp As String) As Double
    Dim hours As Integer, minutes As Integer, seconds As Integer, milliseconds As Integer
    Dim parts() As String, parts2() As String
    
    parts = Split(timestamp, ":")
    parts2 = Split(parts(2), ".")
    
    hours = Val(parts(0))
    minutes = Val(parts(1))
    seconds = Val(parts2(0))
    milliseconds = Val(parts2(1))
    
    ConvertToSeconds = (hours * 3600) + (minutes * 60) + seconds + (milliseconds / 10000000)
End Function

End Module

But in Excel, it outputs #VALUE!, which is unfortunately not what it should be doing. Of course I'm only using the function, not entire code with main. It says nothing more, so it's impossible for me to debug.

Walt21
  • 3
  • 3
  • 1
    As we were trying to get to in your last question. There is something wrong with your data. You will need to step through the function and check each line as it processes and see if it is returning what you expect. – Scott Craner Mar 31 '23 at 20:09
  • 2
    Change `Integer` to `Long`. `milliseconds = Val(parts2(1))` overflows otherwise. Note that Visual Basic, aka VB.Net, is not the same as VBA. If you want to debug your function, then one option is to call it from another subroutine, not from the worksheet. `Sub adfasd()` `Debug.Print ConvertToSeconds("00:00:42.2143012")` `End Sub` shows the problematic line immediately. – BigBen Mar 31 '23 at 20:09
  • 1
    I cannot reproduce. I copied the string from your code and pasted it in a cell then ran the function pointing at that cell and it worked without issue. – Scott Craner Mar 31 '23 at 20:12
  • 1
    @ScottCraner - hmm, `milliseconds As Integer` would definitely overflow with a value of `2143012`? – BigBen Mar 31 '23 at 20:15
  • 1
    yes, sorry it works if I change to Long. @BigBen – Scott Craner Mar 31 '23 at 20:18

0 Answers0