VBA Own Function
 

I have created two of my own functions that work nicely. But occasionally
they start to return #NAME? and this then filters through to every cell that
is dependent upon these functions. This seems to cut in when I sort the
sheet but is intermittent as sometimes the sort works OK???

In the past I have abandoned the current worksheet and gone back to an older
version where the functions worked. But his is not helping now, as the
error is also in the old version.

Anyone any ideas?

VBA Own Function
 

Nor without seeing the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris.F" wrote in message
...
I have created two of my own functions that work nicely. But occasionally
they start to return #NAME? and this then filters through to every cell
that
is dependent upon these functions. This seems to cut in when I sort the
sheet but is intermittent as sometimes the sort works OK???

In the past I have abandoned the current worksheet and gone back to an
older
version where the functions worked. But his is not helping now, as the
error is also in the old version.

Anyone any ideas?

VBA Own Function
 

OK code as requested.

Basically I have a situation where we raise an invoice for support and we
want to spread the income from that support into the month in which support
is given.
So I am calculating how much support has still been charge but not yet used,
in terms of calendar months - defermonths and the second function returns 1
if the invoice covers a current month.

Any help you can offer would be gratefully received.

Thanks in advance.

Chris

Public Function DeferMonths(InvDate As Date, StartDate As Date, CurrDate As
Date, Duration As Integer) As Integer

Dim InvMonth
Dim StartMonth
Dim CurrMonth
Dim EndMonth


DeferMonths = 0

'Convert dates to month numbers

InvMonth = Year(InvDate) * 12 + Month(InvDate)
StartMonth = Year(StartDate) * 12 + Month(StartDate)
CurrMonth = Year(CurrDate) * 12 + Month(CurrDate)
EndMonth = StartMonth + Duration

' See if contract has finished and return zero
If CurrMonth = EndMonth Then
DeferMonths = 0
GoTo Exit01
End If

' See if current month is before startmonth or invoice month
' and return zero

If CurrMonth < InvMonth Then
If CurrMonth < StartMonth Then
DeferMonths = 0
GoTo Exit01
End If

End If

'See if invoice and start months same
If InvMonth = StartMonth Then
DeferMonths = EndMonth - CurrMonth - 1
GoTo Exit01

End If

'See if Invoice month before start month
If InvMonth < StartMonth Then
If CurrMonth = InvMonth Then
DeferMonths = EndMonth - CurrMonth - 1
If DeferMonths Duration Then DeferMonths = Duration
GoTo Exit01
End If

End If

'See if invoice month after start month
If InvMonth StartMonth Then
If StartMonth CurrMonth Then
DeferMonths = 0
GoTo Exit01
End If
If InvMonth CurrMonth Then
DeferMonths = CurrMonth - InvMonth
GoTo Exit01
End If
If CurrMonth = StartMonth Then
DeferMonths = EndMonth - CurrMonth - 1
GoTo Exit01
End If
End If

Exit01:
End Function


Public Function CurrMonths(InvDate As Date, StartDate As Date, CurrDate As
Date, Duration As Integer) As Integer

Dim InvMonth As Integer
Dim StartMonth As Integer
Dim CurrMonth As Integer
Dim EndMonth As Integer

'Convert dates to month numbers

InvMonth = Year(InvDate) * 12 + Month(InvDate)
StartMonth = Year(StartDate) * 12 + Month(StartDate)
CurrMonth = Year(CurrDate) * 12 + Month(CurrDate)
EndMonth = StartMonth + Duration

' See if contract has finished and return zero
If CurrMonth = EndMonth Then
CurrMonths = 0
GoTo Exit01
End If

' See if current month is before startmonth
' and return zero

If CurrMonth < StartMonth Then
CurrMonths = 0
GoTo Exit01
End If


CurrMonths = 1


Exit01:
End Function


"Bob Phillips" wrote in message
...
Nor without seeing the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris.F" wrote in message
...
I have created two of my own functions that work nicely. But
occasionally
they start to return #NAME? and this then filters through to every cell
that
is dependent upon these functions. This seems to cut in when I sort the
sheet but is intermittent as sometimes the sort works OK???

In the past I have abandoned the current worksheet and gone back to an
older
version where the functions worked. But his is not helping now, as the
error is also in the old version.

Anyone any ideas?

VBA Own Function
 

You should always copy/paste your code here for comments.
Perhaps you put the udf into other than a regular module. Won't work in
sheet module.??

--
Don Guillett
SalesAid Software

"Chris.F" wrote in message
...
I have created two of my own functions that work nicely. But occasionally
they start to return #NAME? and this then filters through to every cell
that
is dependent upon these functions. This seems to cut in when I sort the
sheet but is intermittent as sometimes the sort works OK???

In the past I have abandoned the current worksheet and gone back to an
older
version where the functions worked. But his is not helping now, as the
error is also in the old version.

Anyone any ideas?

VBA Own Function
 

Hi Chris,
One way to get #NAME! is to incorrectly name the function
you are calling. Another is to try to get it from another workbook.
without providing a reference in one manner or another.
=personal.xls!MyFunction(A1)

look at the topic below #invokemacros for invoking formulas
http://www.mvps.org/dmcritchie/excel...m#invokemacros
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Don Guillett" wrote in message ...
You should always copy/paste your code here for comments.
Perhaps you put the udf into other than a regular module. Won't work in
sheet module.??

--
Don Guillett
SalesAid Software

"Chris.F" wrote in message
...
I have created two of my own functions that work nicely. But occasionally
they start to return #NAME? and this then filters through to every cell
that
is dependent upon these functions. This seems to cut in when I sort the
sheet but is intermittent as sometimes the sort works OK???

In the past I have abandoned the current worksheet and gone back to an
older
version where the functions worked. But his is not helping now, as the
error is also in the old version.

Anyone any ideas?