Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > #NAME? when using custom defined function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default #NAME? when using custom defined function
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.




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default #NAME? when using custom defined function
Where is the function stored?

What is the code for somecalculatedvalue?

--

HTH

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


"Jules" wrote in message
...
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.






Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default #NAME? when using custom defined function
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

"Bob Phillips" wrote in message
...
Where is the function stored?

What is the code for somecalculatedvalue?

--

HTH

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


"Jules" wrote in message
...
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.








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default #NAME? when using custom defined function
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

Jules wrote:

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

"Bob Phillips" wrote in message
...
Where is the function stored?

What is the code for somecalculatedvalue?

--

HTH

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


"Jules" wrote in message
...
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.







--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default #NAME? when using custom defined function
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" wrote in message
...
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

Jules wrote:

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

"Bob Phillips" wrote in message
...
Where is the function stored?

What is the code for somecalculatedvalue?

--

HTH

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


"Jules" wrote in message
...
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.







--

Dave Peterson


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,758
Default #NAME? when using custom defined function
And instead of looping through the range, application.match() could be used.

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

Bob Phillips wrote:

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" wrote in message
...
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

Jules wrote:

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

"Bob Phillips" wrote in message
...
Where is the function stored?

What is the code for somecalculatedvalue?

--

HTH

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


"Jules" wrote in message
...
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.







--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Custom Format for User Defined Function [email protected] Excel Discussion (Misc queries) 5 August 7th 06 11:27 PM
User Defined/Custom Input Bonnie. Excel Discussion (Misc queries) 2 October 14th 05 09:04 AM
User-defined (Custom) Charts BHARATH RAJAMANI Charts and Charting in Excel 1 August 23rd 05 08:43 AM
Custom lists - not following defined order Darren Excel Worksheet Functions 1 February 21st 05 02:27 PM
Custom defined formula billQ Excel Programming 2 July 15th 03 08:44 AM


All times are GMT +1. The time now is 07:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement