A
Asif
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
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