Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
custom function
Thanks Gord!
looks like what I was looking for... ~Asif |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
custom function | Excel Discussion (Misc queries) | |||
Ned Help W/ Custom Function | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Custom Function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |