Need to make a UDF from a long formula

**marksd80** · 06-14-2013, 04:41 PM

Hi I have two long formulas that I want to make into a UDF so that it's easy to work with.

The first formula calculates the duration in Frames per seconds from two time codes is:
=("0:"&TEXT(DOLLARFR(ROUND(((LEFT(D3,8)*(86400*30)+RIGHT(D3,2))-(LEFT(B3,8)*(86400*30)+RIGHT(B3,2))),0)/30,30),"0.00"))+0

I want to have a function that asks the user for the start time and end time

THe second formula adds the total duration:
=SUMPRODUCT(FLOOR(F3:F4,"0:1.0"))+("0:"&TEXT(DOLLARFR(SUMPRODUCT(ROUND(MOD(F3:F4*86400,1)*100,0))/30,30),"0.00"))

I want an argument that prompts for the range to be added.

I tried making the function with parameters and kept getting #Value as my result.

I did:

Public Function frameDur(fStart, fEnd)

frameDur=("0:"&TEXT(DOLLARFR(ROUND(((LEFT(fEnda,8)*(86400*30)+RIGHT(fEnd,2))-(LEFT(fStart,8)*(86400*30)+RIGHT(fStart,2))),0)/30,30),"0.00"))+0

End Function

for each function I added Application.WorksheetFunction.(Function Name) and that didn't help.

Any help would greatly be appreciated.

**judgeh59** · 06-14-2013, 05:12 PM

on your first left there is fEnda....should that be fEnd?

EDIT: in the Public Function....

**marksd80** · 06-14-2013, 05:13 PM

yes fEnd I was typing really fast on here.

**judgeh59** · 06-14-2013, 05:15 PM

but it's correct in the actual code?....

**marksd80** · 06-14-2013, 05:16 PM

Yes i have everything typed in correct in code.

**judgeh59** · 06-14-2013, 05:18 PM

is it possible to upload an .xlsm file to look at?....

**marksd80** · 06-14-2013, 05:18 PM

Also an example of the time code is:
01:06:00:29

Format is hh:mm:ss.00

**marksd80** · 06-14-2013, 05:26 PM

Here it is

**marksd80** · 06-15-2013, 04:50 PM

have you figured out what I did wrong?

**judgeh59** · 06-17-2013, 02:31 PM

sorry it's taken so long to get back to you....I think I have figured out why the function doesn't work but I haven't figure how to fix it....when you take fEnd/fStart and multiply it by 86400*30 you get a data mismatch error. I believe it's because you are trying multiply a number times a string (TEXT)....there are a few functions out there but don't convert your format to something that works....you wold have to write a converter the string and makes it an actual excel time stamp....sorry I couldn't help....Maybe/probably somebody knows a better way.....