#NAME? error for UDF

**OnlyTheSnake** · 09-11-2012, 11:49 PM

I have created a UDF for doing a simple calculation for my colleague. I have tested it and it works fine in my own file (.xlsm). So I copied it and pasted into a module in my colleague's file (also .xlsm) but I keep getting a #NAME? error when calculating it. It is inserted in a module that has a different name from the function itself and everything is copied exactly to his file, but I still get the problem and I don't know what the difference is. We're both running Office 2007. Can anyone tell me what I'm doing wrong?

This is the UDF:

Function LaborHours(StartTime As Double, StopTime As Double)

LunchStart = 0.479167 '11:30
LunchStop = 0.510416 '12:15
Break1Start = 0.614585 '14:45
Break1Stop = 0.6249 '15:00
Break2Start = 0.732639 '17:35
Break2Stop = 0.743056 '17:50


    If StartTime > StopTime Then
        LaborHours = "Error"
    ElseIf StartTime < LunchStart Then
        If StopTime < LunchStart Then
            LaborHours = 24 * (StopTime - StartTime)
        ElseIf StopTime > LunchStop And StopTime < Break1Start Then
            LaborHours = 24 * (StopTime - StartTime) - 0.75
        ElseIf StopTime > Break1Stop And StopTime < Break2Start Then
            LaborHours = 24 * (StopTime - StartTime) - 1
        ElseIf StopTime > Break2Stop Then
            LaborHours = 24 * (StopTime - StartTime) - 1.25
        Else
            LaborHours = "Error"
        End If
    ElseIf StartTime > LunchStop And StartTime < Break1Start Then
        If StopTime > LunchStop And StopTime < Break1Start Then
            LaborHours = 24 * (StopTime - StartTime)
        ElseIf StopTime > Break1Stop And StopTime < Break2Start Then
            LaborHours = 24 * (StopTime - StartTime) - 0.25
        ElseIf StopTime > Break2Stop Then
            LaborHours = 24 * (StopTime - StartTime) - 0.5
        Else
            LaborHours = "Error"
        End If
    ElseIf StartTime > Break1Stop And StartTime < Break2Start Then
        If StopTime > Break1Stop And StopTime < Break2Start Then
            LaborHours = 24 * (StopTime - StartTime)
        ElseIf StopTime > Break2Stop Then
            LaborHours = 24 * (StopTime - StartTime) - 0.25
        Else
            LaborHours = "Error"
        End If
    ElseIf StartTime > Break2Stop Then
        If StopTime > Break2Stop Then
            LaborHours = 24 * (StopTime - StartTime)
        Else
            LaborHours = "Error"
        End If
    Else
        LaborHours = "Error"
    End If
        
LaborHours = Format(LaborHours, "#.000")
        
End Function

**TMS** · 09-12-2012, 03:16 AM

Copied into a standard module, it works fine for me.

I can get a #NAME? error if I create a second identical code module ... but I get Ambiguous Name detected, so that's pretty obvious.

I get a #NAME? error if the function call in the worksheet is spelt wrong.

I don't get a #NAME? error if I make it a Private Function or a Private Module.

But I do get a #NAME error if I put the function in a Sheet class module.

So, some options to look at ...

Regards, TMS

**JosephP** · 09-12-2012, 03:20 AM

also if you have macros disabled ;-)

**OnlyTheSnake** · 09-12-2012, 04:30 AM

Originally Posted by JosephP

also if you have macros disabled ;-)

Damn, it really was that simple...

Signed the macro, trusted it on his computer and it works fine now. Thanks for the tip (and for making me kick myself).

**TMS** · 09-12-2012, 03:27 AM

@JP: Ah, never thought of that. Started out in an existing workbook (without code) and just pasted the code in ... obviously, I could then run it. I'll be annoyed if that's the answer.