#NAME? when using custom defined function

[Jules]

In Module1 of an xls file, I added a function like:

Function DiffWeekDays(a,b)
DiffWeekDays = somecalculatedvalue
End Function

I use this function in some cells and it works perfect.

However after some days, I open the file again.
In the cells I used the function, I see now: #NAME?
The function hasn't been changed in the meanwhile.

Has anybody some idea what's causing this?

Many thanks in advance!

best regards,
Joris.

 

[Bob Phillips]

Where is the function stored?

What is the code for somecalculatedvalue?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jules]

It is stored in the module1 (is there a possibility to store it elsewhere?)

The code worked and is still the same, so I guess the problem it is not
there?

The code:
Function DiffWeekDays(startdate, starttime, enddate, endtime)
'' returns the nb of hours!
Dim opentime, closetime
opentime = TimeValue("09:00:00")
closetime = TimeValue("17:00:00")
Dim startsecs, endsecs, nb
nb = -1
If IsBusinessDay(startdate) Then
If DateDiff("s", closetime, starttime) > 0 Then
' after business hours
startsecs = 0
Else
' started before/in business hours
startsecs = DateDiff("s", opentime, starttime)
If startsecs < 0 Then
' before
startsecs = 8 * 60 * 60
Else
startsecs = 8 * 60 * 60 - startsecs
End If
End If
Else
' Not started on a business day
startsecs = 0
End If

If IsBusinessDay(enddate) Then
If DateDiff("s", closetime, endtime) > 0 Then
' after business hours
endsecs = 8 * 60 * 60
Else
' ended before/in business hours
endsecs = DateDiff("s", opentime, endtime)
If endsecs < 0 Then
endsecs = 0
End If
End If
Else
' Not ended on a business day
endsecs = 0
End If
secs = startsecs + endsecs
If secs >= 8 * 60 * 60 Then
secs = secs - 8 * 60 * 60
nb = 0
End If
If (DateDiff("s", startdate, enddate) < 0) Or ((DateDiff("s", startdate,
enddate) = 0) And (DateDiff("s", starttime, endtime) < 0)) Then
DiffWeekDays = "ERROR: start after end"
Else
DiffWeekDays = MyDiffWeekDays(startdate, enddate, nb, secs)
End If
End Function

Private Function IsWeekDay(mydate)
Dim myday
myday = Weekday(mydate)
If ((myday = "1") Or (myday = "7")) Then
IsWeekDay = False
Else: IsWeekDay = True
End If
End Function

Private Function IsHoliday(mydate)
Dim holidays, holiday, tmp
tmp = False
holidays = Array("01/01/2000" & _
"", "01/01/2004" & _
"", "09/04/2004" & _
"", "12/04/2004" & _
"", "01/05/2004" & _
"", "20/05/2004" & _
"", "31/05/2004" & _
"", "21/07/2004" & _
"", "15/08/2004" & _
"", "01/11/2004" & _
"", "11/11/2004" & _
"", "24/12/2004" & _
"", "25/12/2004" & _
"", "26/12/2004" & _
"", "31/12/2004" & _
"", "01/01/2005")
For i = 0 To UBound(holidays)
holiday = DateValue(holidays(i))
If DatePart("d", holiday) = DatePart("d", mydate) And DatePart("m",
holiday) = DatePart("m", mydate) And DatePart("y", holiday) = DatePart("y",
mydate) Then
tmp = True
End If
Next
IsHoliday = tmp
End Function

Private Function IsBusinessDay(mydate)
If (Not (IsWeekDay(mydate)) Or IsHoliday(mydate)) Then
IsBusinessDay = False
Else: IsBusinessDay = True
End If
End Function

Private Function MyDiffWeekDays(startdate, enddate, nb, secs)
If DateDiff("d", startdate, enddate) = 0 Then
Dim tmp
tmp = nb * 8 + secs / (60 * 60)
If tmp < 0 Then
MyDiffWeekDays = tmp + 8
Else
MyDiffWeekDays = tmp
End If
Else
If IsBusinessDay(startdate) Then
MyDiffWeekDays = MyDiffWeekDays(DateAdd("d", 1, startdate), enddate,
nb + 1, secs)
Else
MyDiffWeekDays = MyDiffWeekDays(DateAdd("d", 1, startdate), enddate,
nb, secs)
End If
End If
End Function

 

[Dave Peterson]

First, I don't have a real good guess.

I think Bob was concerned that if you put your code behind a worksheet (or
thisworkbook), that could cause your error.

My guess is that there is another thing with that name.

Maybe you have a range with that name. Maybe you have a module that was renamed
to DiffWeekDays (instead of Module2).

If you do have any module with that name, change the module name:

Mod_DiffWeekDays
(make it unique)

(any chance????)

And second, I think I'd be careful with this section:

holidays = Array("01/01/2000" & _
"", "01/01/2004" & _
"", "09/04/2004" & _
"", "12/04/2004" & _
"", "01/05/2004" & _
"", "20/05/2004" & _
"", "31/05/2004" & _
"", "21/07/2004" & _
"", "15/08/2004" & _
"", "01/11/2004" & _
"", "11/11/2004" & _
"", "24/12/2004" & _
"", "25/12/2004" & _
"", "26/12/2004" & _
"", "31/12/2004" & _
"", "01/01/2005")

VBA is very USA centric. 12/04/2004 was treated as Dec 4, 2004 when I stepped
through the code.

Maybe something less ambiguous like:

Private Function IsHoliday(mydate)
Dim holidays, holiday, tmp
tmp = False
holidays = Array(DateSerial(2000, 1, 1), _
DateSerial(2004, 1, 1), _
DateSerial(2004, 4, 9), _
DateSerial(2004, 4, 12), _
DateSerial(2004, 5, 1), _
DateSerial(2004, 5, 20), _
DateSerial(2004, 5, 31), _
DateSerial(2004, 7, 21), _
DateSerial(2004, 8, 15), _
DateSerial(2004, 11, 1), _
DateSerial(2004, 11, 11), _
DateSerial(2004, 12, 24), _
DateSerial(2004, 12, 25), _
DateSerial(2004, 12, 26), _
DateSerial(2004, 12, 31), _
DateSerial(2005, 1, 1))

For i = 0 To UBound(holidays)
If mydate = holidays(i) Then
tmp = True
Exit For
End If
Next i

IsHoliday = tmp

End Function

 

[Bob Phillips]

Dave is absolutely correct about my thinking, but your answer negated that.

I also found a situation where I generated a #Name error, caused by
something to do with the startsecs variable. Getting rid of Option Explicit
sorted it, putting it back gave a compile error, that is I couldn't
re-produce it so I didn't revert with that.

As to Dave's holiday suggestion, I would go further and suggest that you put
it in a worksheet range, and pass that range to the function. It will make
it easier to maintain.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

And instead of looping through the range, application.match() could be used.

(But that still leaves the real question with only guesses.)