Consulting

Results 1 to 7 of 7

Thread: #VALUE error

  1. #1

    #VALUE error

    I'm trying to write timecode calculation functions for excel.

    In the function, the cell that gets passed to the function as the variable TC contains a string in the form hh:mm:ss:ff, where hh is hours, mm is minutes, ss is seconds, and ff is frames (24 frames per second). e.g. "01:23:45:19".

    I parse the string into integers for the hours, minutes, seconds and frames.

    I want my function to return the total number of frames from 00:00:00:00 as a integer.

    Function TC24ToFrames(TC As String) As Integer
    Dim F As Integer
    Dim S As Integer
    Dim M As Integer
    Dim H As Integer
    F = Int(Right(TC, 2))
    S = Int(Mid(TC, 7, 2))
    M = Int(Mid(TC, 4, 2))
    H = Int(Left(TC, 2))
    TC24ToFrames = F + (S * 24) + (M * 24 * 60) + (H * 60 * 60 * 24)
    End Function

    I get a #VALUE error when I use this. I've texted every part of the function by commenting out sections and the only part that is causing an error is the final calculation.

    Does anyone know what is going wrong and how to fix it?

  2. #2
    VBAX Regular
    Joined
    Jul 2014
    Posts
    20
    Location
    Change the function assignment in the last part to:

    TC24ToFrames = F + (S * 24#) + (M * 24 * 60#) + (H * 60 * 60# * 24)

    This converts the values to Double. Also, the function has to be declared to return Double values:

    Public Function TC24ToFrames(TC As String) As Double

  3. #3
    Nailed it! Thanks dxider! Can you explain the placement of the symbol "#"? Why is it after some numbers and not others?

  4. #4
    VBAX Regular
    Joined
    Jul 2014
    Posts
    20
    Location
    The # symbol is to let excel know that you are representing the number as a Double value, when the expression is evaluated, it also evaluates the type of the value (in you case, everything was evaluated as Integer).
    The Integer type has a Max positive value of 32767, so any calculation beyond that, resulted in an overflow of the type, that was the reason you were receiving a #VALUE error.

    To test this, you can use this method:

    Public Function maxInteger() As Integer
    maxInteger = 32767
    End Function

    If you change the value, you will see that after changing it to 32768, the function returns error.

    The integer value has a storage capacity of 2^16 (this means 65,536), but the half is used to represent negative values, and the other part for positive.

    The Double type has a longer capacity, that's the reason it worked after changing the return type and the explicit change of type with the # symbol.

  5. #5
    Thanks for the info.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    You can let Excel do more of the work for you

    Option Explicit
    Sub test()
        MsgBox TC24ToFrames("01:23:45:19")
    End Sub
    
    'HH:MM:SS:FF
    Function TC24ToFrames(TC As String) As Long
        Dim v As Variant
        v = Split(TC, ":")
        TC24ToFrames = v(3) + (v(2) * 24) + (v(1) * 24 * 60) + (v(0) * 60 * 60 * 24)
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Quote Originally Posted by Paul_Hossler View Post
    You can let Excel do more of the work for you

    Option Explicit
    Sub test()
        MsgBox TC24ToFrames("01:23:45:19")
    End Sub
    
    'HH:MM:SS:FF
    Function TC24ToFrames(TC As String) As Long
        Dim v As Variant
        v = Split(TC, ":")
        TC24ToFrames = v(3) + (v(2) * 24) + (v(1) * 24 * 60) + (v(0) * 60 * 60 * 24)
    End Function
    I didn't know about the split function. Thanks, that is a lot easier. It will also help because sometimes people leave out the leading "0"s when writing time code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •