custom function
 

I have created a function for my co-workers. What I want is to make it feel
like a ready-made function, like:
1. When someone starts typing in a cell
=urpa(
in case of a readymade function one would see the arguments name in a
floating tip box. How can I do this with my custom function?
2. If someone types the function name in lowercase (like =urpa) how can I
convert it to uppercase in the function arguments forrm and formula bar?
3. How can I show helpful tips/information about arguments in the function
arguments form (like the ones for readymade functions)?

Thanks in advance.
~Asif

The function is given below, in case you'd like to see:

Function URPA(ByVal ExecAmount As Double, ExecDate As Date, Term As Byte,
IntRate As Double, Optional Mode = 1)

If ExecDate = 0 Then
URPA = "ExecDate Missing"
Exit Function
End If

Dim i As Integer
If Mode = 0 Then
i = -1
Else
i = 0
End If

Dim Rental As Double

Rental = Pmt(IntRate / 12, Term, ExecAmount, , Mode)
Rental = Abs(Application.WorksheetFunction.RoundUp(Rental, -1))
IntRate = Rate(Term, Rental, -ExecAmount, , 0)

Dim dtStep As Date
dtStep = ExecDate

Do
i = i + 1
dtStep = DateAdd("m", 1, dtStep)
Loop While dtStep < Date

If i = 0 Then
URPA = Format(ExecAmount, "##,##0")
Else
ReDim BalancePrincipal(i) As Double
ReDim Principal(i) As Double

Dim k As Integer
k = 1
BalancePrincipal(k) = ExecAmount - (Rental - ExecAmount * IntRate)

For k = 2 To i
BalancePrincipal(k) = BalancePrincipal(k - 1) - (Rental -
BalancePrincipal(k - 1) * IntRate)
Next k

URPA = Format(BalancePrincipal(i), "##,##0")

End If
End Function

custom function
 

Asif

See Laurent Longre's website for FUNCUSTOMIZE add-in.

http://longre.free.fr/english/index.html

Gord Dibben XL2002

On Sat, 29 Nov 2003 19:57:48 +0600, "Asif"
wrote:

I have created a function for my co-workers. What I want is to make it feel
like a ready-made function, like:
1. When someone starts typing in a cell
=urpa(
in case of a readymade function one would see the arguments name in a
floating tip box. How can I do this with my custom function?
2. If someone types the function name in lowercase (like =urpa) how can I
convert it to uppercase in the function arguments forrm and formula bar?
3. How can I show helpful tips/information about arguments in the function
arguments form (like the ones for readymade functions)?

Thanks in advance.
~Asif

The function is given below, in case you'd like to see:

Function URPA(ByVal ExecAmount As Double, ExecDate As Date, Term As Byte,
IntRate As Double, Optional Mode = 1)

If ExecDate = 0 Then
URPA = "ExecDate Missing"
Exit Function
End If

Dim i As Integer
If Mode = 0 Then
i = -1
Else
i = 0
End If

Dim Rental As Double

Rental = Pmt(IntRate / 12, Term, ExecAmount, , Mode)
Rental = Abs(Application.WorksheetFunction.RoundUp(Rental, -1))
IntRate = Rate(Term, Rental, -ExecAmount, , 0)

Dim dtStep As Date
dtStep = ExecDate

Do
i = i + 1
dtStep = DateAdd("m", 1, dtStep)
Loop While dtStep < Date

If i = 0 Then
URPA = Format(ExecAmount, "##,##0")
Else
ReDim BalancePrincipal(i) As Double
ReDim Principal(i) As Double

Dim k As Integer
k = 1
BalancePrincipal(k) = ExecAmount - (Rental - ExecAmount * IntRate)

For k = 2 To i
BalancePrincipal(k) = BalancePrincipal(k - 1) - (Rental -
BalancePrincipal(k - 1) * IntRate)
Next k

URPA = Format(BalancePrincipal(i), "##,##0")

End If
End Function

custom function
 

Thanks Gord!
looks like what I was looking for...

~Asif