Video Timecode Functions VBA

[iamhans70]

I've been trying to create some VBA functions to calculate timecode entries.

Timecode is represented in the format:
hh:mm:ss:ff

Where hh = hours, mm = minutes, ss = seconds, and ff=frames

I am dealing with video that runs at 30 frames per second, so possible values for ff are 0 to 29.

In order to do simple arithmetic, I think the easiest thing is to translate any timecode entry to the total number of frames, do the arithmetic, then convert it back to timecode.

I'm trying to create VBA functions to do this for me.

Here's what I have so far:

Function NumFrames(TimeCodeCell As Range)
Dim hh As Integer
Dim mm As Integer
Dim ss As Integer
Dim ff As Integer

TimeCodeText = TimeCodeCell.Value

hh = Mid(TimeCodeText, 1, 2)
mm = Mid(TimeCodeText, 4, 2)
ss = Mid(TimeCodeText, 7, 2)
ff = Mid(TimeCodeText, 10, 2)

NumFrames = Frames + (ss * 30) + (mm * 30 * 60) + (hh * 30 * 60 * 60)
End Function

To get to the number of frames.

The problem is that it will only calculate up to 00:18:12:07 or 32,767 frames. After that point, I get #VALUE!.

I also need help with the reverse conversion.

So far I have:

Function TCString(NumFramesCell As Range)
Dim hh As Integer
Dim mm As Integer
Dim ss As Integer
Dim ff As Integer

NumFramesValue = NumFramesCell.Value

hh = Int(NumFramesValue / 30 / 60 / 60)
mm = Int((NumFramesValue - hh * 30 * 60 * 60) / 30 / 60)
ss = Int((NumFramesValue - (hh * 30 * 60 * 60 + mm * 30 * 60)) / 30)
ff = Int(NumFramesValue - (hh * 30 * 60 * 60 + mm * 30 * 60 + ss * 30))

TCString = CStr(hh) & ":" & CStr(mm) & ":" & CStr(ss) & ":" & CStr(ff)
End Function


This code has two problems. The first is the same as with NumFrames in that it won't calcualte more than 00:18:12:07 or 32,767 frames.

The second problem is in the string output. I need all three numbers to be two digit numbers even if that means a preceeding zero.

Thanks for your help.
<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="75"><tbody><tr height="13"><td class="xl24" x:num="32767.0" align="right" height="13" width="75">
</td> <!--EndFragment--> </tr> </tbody></table>

 

[PA HS Teacher]

1. Dim your variables as Long, not Integer.

2. Instead of CStr(hh)
Try Format(hh,"00")

 

[iamhans70]

Thanks!! The two functions now work fantastically by themselves.

One last problem. I want to nest the functions. That is, I would like to, in once Excel cell, convert two cells to frames, subtract them, and then convert back to timecode.

The formula looks like this (Note that I changed the names of the functions)

=Frames2TC(TC2Frames(B1)-TC2Frames(A1))

I get #VALUE! and can't figure out why.

Code for the two functions as follows.

Function TC2Frames(TimeCodeCell As Range)
Dim hh As Long
Dim mm As Long
Dim ss As Long
Dim ff As Long

TimeCodeText = TimeCodeCell.Value

hh = Mid(TimeCodeText, 1, 2)
mm = Mid(TimeCodeText, 4, 2)
ss = Mid(TimeCodeText, 7, 2)
ff = Mid(TimeCodeText, 10, 2)

TC2Frames = ff + (ss * 30) + (mm * 30 * 60) + (hh * 30 * 60 * 60)
End Function

Function Frames2TC(NumFramesCell As Range)
Dim hh As Long
Dim mm As Long
Dim ss As Long
Dim ff As Long

NumFramesValue = NumFramesCell.Value

hh = Int(NumFramesValue / 30 / 60 / 60)
mm = Int((NumFramesValue - (hh * 30 * 60 * 60)) / 30 / 60)
ss = Int((NumFramesValue - (hh * 30 * 60 * 60 + mm * 30 * 60)) / 30)
ff = Int(NumFramesValue - (hh * 30 * 60 * 60 + mm * 30 * 60 + ss * 30))

Frames2TC = Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(ss, "00") & ":" & Format(ff, "00")
End Function

 

[John_w]

Thanks!! The two functions now work fantastically by themselves.

One last problem. I want to nest the functions. That is, I would like to, in once Excel cell, convert two cells to frames, subtract them, and then convert back to timecode.

The formula looks like this (Note that I changed the names of the functions)

=Frames2TC(TC2Frames(B1)-TC2Frames(A1))

I get #VALUE! and can't figure out why.

Because your Frames2TC is expecting a Range and the result of TC2Frames(B1)-TC2Frames(A1) is a Long. Therefore change the Frames2TC function parameter to a Long:

Function TC2Frames(TimeCodeCell As Range) As Long
    Dim hh As Long
    Dim mm As Long
    Dim ss As Long
    Dim ff As Long
    Dim TimeCodeText As String
    
    TimeCodeText = TimeCodeCell.Value
    
    hh = Mid(TimeCodeText, 1, 2)
    mm = Mid(TimeCodeText, 4, 2)
    ss = Mid(TimeCodeText, 7, 2)
    ff = Mid(TimeCodeText, 10, 2)
    
    TC2Frames = ff + (ss * 30) + (mm * 30 * 60) + (hh * 30 * 60 * 60)
End Function

Function Frames2TC(NumFramesValue As Long) As String
    Dim hh As Long
    Dim mm As Long
    Dim ss As Long
    Dim ff As Long
        
    hh = Int(NumFramesValue / 30 / 60 / 60)
    mm = Int((NumFramesValue - (hh * 30 * 60 * 60)) / 30 / 60)
    ss = Int((NumFramesValue - (hh * 30 * 60 * 60 + mm * 30 * 60)) / 30)
    ff = Int(NumFramesValue - (hh * 30 * 60 * 60 + mm * 30 * 60 + ss * 30))
    
    Frames2TC = Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(ss, "00") & ":" & Format(ff, "00")
End Function

 

[iamhans70]

Oh. Cool! Thanks for your help.

 

[Joe91042]

Hi. I know this thread was awhile ago and has been very helpful. I would also like to be able to pull in different frame rates other than 30 for a TC conversion function. Is this simple to do or do I need to create a function for each frame rate?

 

[John_w]

All the '30' values in the code represent 30 frames per second. So just modify the code to accept the frame rate as a parameter and use this wherever '30' appears in the code.

 

[Joe91042]

Here is what I have. Not sure how to pull in FrameRate from another cell maybe in the format of [TC2Frames (A1,A2)]. A1 being Timecode and A2 being the frame rate. Also very new to VBA so when you say make it a parameter not sure how to go about that. (FYI: I did open a new thread with this Q).

Function TC2Frames(TimeCodeCell As Range) As Long

Dim hh As Long
Dim mm As Long
Dim ss As Long
Dim ff As Long
Dim TimeCodeText As String
Dim FrameRate As Integer

TimeCodeText = TimeCodeCell.Value
FrameRate = 29.97

hh = Mid(TimeCodeText, 1, 2)
mm = Mid(TimeCodeText, 4, 2)
ss = Mid(TimeCodeText, 7, 2)
ff = Mid(TimeCodeText, 10, 2)

TC2Frames = ff + (ss * FrameRate) + (mm * FrameRate * 60) + (hh * FrameRate * 60 * 60)
End Function

Function Frames2TC(NumFramesValue As Long) As String

Dim hh As Long
Dim mm As Long
Dim ss As Long
Dim ff As Long
Dim FrameRate As Integer

FrameRate = 30

hh = Int(NumFramesValue / FrameRate / 60 / 60)
mm = Int((NumFramesValue - (hh * FrameRate * 60 * 60)) / FrameRate / 60)
ss = Int((NumFramesValue - (hh * FrameRate * 60 * 60 + mm * FrameRate * 60)) / FrameRate)
ff = Int(NumFramesValue - (hh * FrameRate * 60 * 60 + mm * FrameRate * 60 + ss * FrameRate))

Frames2TC = Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(ss, "00") & ":" & Format(ff, "00")
End Function


Thanks

 

[John_w]

Here is the code with the additional FramesPerSecond parameter, which defaults to 30 if not specified. I've changed the first parameter of TC2Frames to a string, so that it uses the cell value directly, and added a warning if the ff value exceeds the FramesPerSecond parameter. Example uses:

=TC2Frames(A2) 'timecode in A2, 30 fps
=frames2tc(C2) 'total frames in C2, 30 fps
=TC2Frames(A3,B3) 'timecode in A3, fps in B3
=Frames2tc(C3,B3) 'total frames in C3, fps in B3

Option Explicit

Function TC2Frames(TimeCodeText As String, Optional FramesPerSecond As Long = 30) As Long
    Dim hh As Long
    Dim mm As Long
    Dim ss As Long
    Dim ff As Long
    
    hh = Mid(TimeCodeText, 1, 2)
    mm = Mid(TimeCodeText, 4, 2)
    ss = Mid(TimeCodeText, 7, 2)
    ff = Mid(TimeCodeText, 10, 2)
    
    If ff >= FramesPerSecond Then MsgBox "Error.  ff value of " & ff & " exceeds FramesPerSecond parameter value of " & FramesPerSecond
    
    TC2Frames = ff + (ss * FramesPerSecond) + (mm * FramesPerSecond * 60) + (hh * FramesPerSecond * 60 * 60)
End Function

Function Frames2TC(NumFramesValue As Long, Optional FramesPerSecond As Long = 30) As String
    Dim hh As Long
    Dim mm As Long
    Dim ss As Long
    Dim ff As Long
        
    hh = Int(NumFramesValue / FramesPerSecond / 60 / 60)
    mm = Int((NumFramesValue - (hh * FramesPerSecond * 60 * 60)) / FramesPerSecond / 60)
    ss = Int((NumFramesValue - (hh * FramesPerSecond * 60 * 60 + mm * FramesPerSecond * 60)) / FramesPerSecond)
    ff = Int(NumFramesValue - (hh * FramesPerSecond * 60 * 60 + mm * FramesPerSecond * 60 + ss * FramesPerSecond))
    
    Frames2TC = Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(ss, "00") & ":" & Format(ff, "00")
End Function

Post a link to your new thread if you want me to see it.

 

[Joe91042]

Thanks John. That works well.. I did have to change Framespersecond As Long to Framespersecond As Double because 29.97 and 23.976 are frame rates that are very common.