#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?

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

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

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.

Thanks for the info.

You can let Excel do more of the work for you

Code:

---

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

---

Quote:

---

Originally Posted by Paul_Hossler

You can let Excel do more of the work for you

Code:

---

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.