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>

 

[Joe91042]

Spoke too soon. With the below function and a TimeCodeText of 01:00:00:00 and a FrameRate of 23.976 I get a FrameCount of 86313.6. This is correct. At a TimeCodeText of 02:00:00:00 and a FrameRate of 23.976 I get a FrameCount of 172626.224. It should be 172627.2.

It's correct at hour 3 but wrong at hour 4.

And while it's correct at hour 1 it's wrong at 01:30:00:00 showing a FrameCount of 129469.424 instead of the correct FrameCount of 129470.4.

It's got me a bit baffled. (I've also included the Frames2TC function below)


Function TC2Frames(TimeCodeText As String, Optional FrameRate As Double = 29.97) As Double

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 >= FrameRate Then MsgBox "Error. ff value of " & ff & " exceeds FrameRate parameter value of " & FrameRate

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

End Function

Function Frames2TC(FrameCount As Long, Optional FrameRate As Double = 29.97) As String

Dim hh As Long
Dim mm As Long
Dim ss As Long
Dim ff As Long

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

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

End Function

 

[John_w]

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

The above calculation involves mixed numeric data types (Double and Long) and is therefore losing precision. In such a calculation I think - I might be wrong - that VBA 'downgrades' doubles to long.

Try changing the hh, mm, etc. variables to Double. Also change the 60 and 3600 to 60.0 and 3600.0. When you type in 60.0, the VB editor changes it for display purposes to 60#. The # denotes a Double.

 

[shg]

At a TimeCodeText of 02:00:00:00 and a FrameRate of 23.976 I get a FrameCount of 172626.224. It should be 172627.2

Those numbers are the same to five significant digits, and the frame rates are only specified with four (29.97) or five (23.976) digits. It seems unlikely that those frame rates are exact. I think the 29.97 is actually 30/1.001 ~ 29.97003, an error of 1E-6.

I don't know where the 23.976 came from, but I suspect it is also not exact, that it may be some ratio (e.g., 1990/83) that would give a more exact value.

Also, the code doesn't appear to account for dropping two timecodes per minute like SMPTE timecodes do for NTSC, but maybe that's not relevant to this frame rate.

EDIT:

The above calculation involves mixed numeric data types (Double and Long) and is therefore losing precision. In such a calculation I think - I might be wrong - that VBA 'downgrades' doubles to long.

VBA coerces the smaller data types to the larger.

 

[Joe91042]

I tried what you recommended with the same results. Investigating further I noticed that when the 23.976 is not working 29.97 works fine and when 23.976 is working 29.97 is not working.


Function TC2Frames(TimeCodeText As String, Optional FrameRate As Double = 29.97) As Double

Dim hh As Double
Dim mm As Double
Dim ss As Double
Dim ff As Double

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

If ff >= FrameRate Then MsgBox "Error. ff value of " & ff & " exceeds FrameRate parameter value of " & FrameRate

TC2Frames = ff + (ss * FrameRate) + (mm * FrameRate * 60#) + (hh * FrameRate * 3600#)

End Function

 

[John_w]

A TimeCodeText of 01:00:00:00 and a FrameRate of 23.976 I get a FrameCount of 86313.6. This is correct. At a TimeCodeText of 02:00:00:00 and a FrameRate of 23.976 I get a FrameCount of 172626.224. It should be 172627.2.

It's correct at hour 3 but wrong at hour 4.

And while it's correct at hour 1 it's wrong at 01:30:00:00 showing a FrameCount of 129469.424 instead of the correct FrameCount of 129470.4.

Using Doubles instead of Longs gives your expected results for me:

Timecode, Frame rate, TC2Frames
01:00:00:00.000, 23.976, 86313.6
02:00:00:00.000, 23.976, 172627.2
01:30:00:00.000, 23.976, 129470.4

Function TC2Frames2(TimeCodeText As String, Optional FrameRate As Double = 29.97) As Double

    Dim hh As Double
    Dim mm As Double
    Dim ss As Double
    Dim ff As Double
    
    hh = Mid(TimeCodeText, 1, 2)
    mm = Mid(TimeCodeText, 4, 2)
    ss = Mid(TimeCodeText, 7, 2)
    ff = Mid(TimeCodeText, 10, 6)
    
    TC2Frames2 = ff + (ss * FrameRate) + (mm * FrameRate * 60#) + (hh * FrameRate * 3600#)

End Function

I've used ff.ffff (6 characters) for the frames, but that shouldn't make a difference because this part of the timecode is 00.000 in all cases.

 

[John_w]

VBA coerces the smaller data types to the larger.

Thanks! I couldn't remember which way it went.

 

[Joe91042]

Those numbers are the same to five significant digits, and the frame rates are only specified with four (29.97) or five (23.976) digits. It seems unlikely that those frame rates are exact. I think the 29.97 is actually 30/1.001 ~ 29.97003, an error of 1E-6.

I don't know where the 23.976 came from, but I suspect it is also not exact, that it may be some ratio (e.g., 1990/83) that would give a more exact value.

Also, the code doesn't appear to account for dropping two timecodes per minute like SMPTE timecodes do for NTSC, but maybe that's not relevant to this frame rate.

EDIT:

VBA coerces the smaller data types to the larger.

Still having issues with the calculation errors but not as often as before.

23.976 is a pulled down rate from 24FPS used in High Def video. It runs at the same speed as 29.97. There is no Drop Frame version of it.

Below is my attempt at dealing with 29.97DF code. I am trying to build a case that when mm is not 00,10,20,30,40,50 and ff < 16 to add 2 to ff and if ff >= to 16 and <=28 to add 1 to ff. This will give me a readout that does not include 00 or 01 in ff unless mm is 00,10,20,30,40,50.

It should also work if a case can be made that when mm single minutes is 0 to not execute the addition.

Thanks.


Function Frames2DFTC(FrameCount As Double, Optional FrameRate As Double = 30) As String

Dim hh As Double
Dim mm As Double
Dim ss As Double
Dim ff As Double

hh = Int((FrameCount / 3600#) / FrameRate)
mm = Int((FrameCount - (hh * 3600# * FrameRate)) / 60# / FrameRate)
ss = Int((FrameCount - (hh * 3600# * FrameRate + mm * 60# * FrameRate)) / FrameRate)
ff = Int(FrameCount - (hh * 3600# * FrameRate + mm * 60# * FrameRate + ss * FrameRate))

If Not mm = "00" And ff < 16 Then ff = ff + 2 Else If Not mm = "00" And ff >= 16 And ff <= 28 Then ff = ff + 1
'if Not mm = "10" And ff < 16 Then ff = ff + 2 Else If Not mm = "10" And ff >= 16 And ff <= 28 Then ff = ff + 1
'If Not mm = "20" And ff < 16 Then ff = ff + 2 Else if Not mm = "20" And ff >= 16 And ff <= 28 Then ff = ff + 1
'If Not mm = "30" And ff < 16 Then ff = ff + 2 Else If Not mm = "30" And ff >= 16 And ff <= 28 Then ff = ff + 1
'if Not mm = "40" And ff < 16 Then ff = ff + 2 Else If Not mm = "40" And ff >= 16 And ff <= 28 Then ff = ff + 1
'If Not mm = "50" And ff < 16 Then ff = ff + 2 Else If Not mm = "50" And ff >= 16 And ff <= 28 Then ff = ff + 1

Frames2DFTC = Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(ss, "00") & ";" & Format(ff, "00")

End Function

 

[Joe91042]

FYI: I did get the last module to work so that when converting frames to timecode, drop frame code displays properly using the below code.

Function Frames2DFTC(FrameCount As Double, Optional FrameRate As Double = 30) As String

Dim hh As Double
Dim mm As Double
Dim ss As Double
Dim ff As Double

hh = Int((FrameCount / 3600#) / FrameRate)
mm = Int((FrameCount - (hh * 3600# * FrameRate)) / 60# / FrameRate)
ss = Int((FrameCount - (hh * 3600# * FrameRate + mm * 60# * FrameRate)) / FrameRate)
ff = Int(FrameCount - (hh * 3600# * FrameRate + mm * 60# * FrameRate + ss * FrameRate))

Select Case mm
Case 0, 10, 20, 30, 40, 50
ff = ff
Case Else
If ff < 16 Then ff = ff + 2 Else If ff >= 16 And ff <= 28 Then ff = ff + 1
End Select

Frames2DFTC = Format(hh, "00") & ":" & Format(mm, "00") & ":" & Format(ss, "00") & ";" & Format(ff, "00")

End Function


I also created 2 modules to convert from feet and frames to frames and back.

Function Feet2Frames(TimeCodeText As String, Optional FrameRate As Double = 16) As Double

Dim feet As Double
Dim ff As Double

feet = Mid(TimeCodeText, 1, 4)
ff = Mid(TimeCodeText, 7, 2)

'If ff >= FrameRate Then MsgBox "Error. ff value of " & ff & " exceeds FrameRate parameter value of " & FrameRate

Feet2Frames = (feet * FrameRate) + ff

End Function

Function Frames2Feet(FrameCount As Double, Optional FrameRate As Double = 16) As String

Dim feet As Double
Dim ff As Double

feet = Int(FrameCount / FrameRate)
ff = Int(FrameCount - (feet * FrameRate))

Frames2Feet = Format(feet, "0000") & " +" & Format(ff, "00")

End Function

Thanks for your help and if there are any modifications that any one can think of send them along.

 

[roccoilricco]

Hi Everyone,
I'm sorry but I'm a newbie of this forum, I apologize in advance for possible mistakes.
I've read this interesting (and very old) topic and I'd like to ask you a further thing.
I've succeded in using this vba code, but I'd like to sum up a list of timecode not only add or subtrack a couple of them.
and then: How can I add this feature at this code??
as you can see in the picture, I have three columns E (timecode IN), F (tmecode OUT) and G (Duration, F - E)...
How can I sum Up all the timecodes in column G???

Thank you in advance...