User-Defined Functions problem (arguments do not show up in Excel Menu)

[pecosvil]

Hi everyone!
I am using Excel 2010.
I just created several UDF. One of them (named fc3), however, is not working properly.
Basically it has 18 arguments. When I go to Excel spreadsheet I do the following:
Formulas > Insert function > then select User Defined > select the function.

Then it opens a window where you just input the different arguments. In the case of this problematic function fc3, there appears only one slot for arguments, and it has no name. Anyone has any idea of what could be happening??

Many thanks in advance!

 

[wigi]

It might be caused by the fact that FC3 is a cell in any Excel worksheet. It is not advised to take function names that are cell addresses in a sheet.

 

[pecosvil]

no, actually the name is not fc3, i just made it up for the question.
the name is "payment_customized"

 

[wigi]

What is the VBA code for that payment_customized function?

 

[pecosvil]

it's super long. basically a bunch of IF statements that take into account loan characteristics. Just to give you an idea, the first line is

Public Function Payment_Customized(loan_number As Integer, loan_amount As Double, maturity_date As Date, next_principal_payment_date As Date, next_principal_payment_period As Integer, payment_type As String, principal_payment_frequency As String, interest_rate_type As String, fixed_rate As Double, number_of_payments As Integer, principal_payment_annuity_payment As Double, period_cashflow As Integer, date_cashflow As Date, date_prepayment As Date, period_prepayment As Integer, amount_prepayment_OC As Double, maturity_period As Integer)

'then it follows a lot of if statements, and then the function ends with an
End Function


What strikes me is that the arguments do not appear in the Excel Insert Function menu.... so I can't even start running the function to debug it...

 

[wigi]

What strikes me is that the arguments do not appear in the Excel Insert Function menu.... so I can't even start running the function to debug it...

Why not? There is Debug.Print to output to the immediate window, or very easy to do:

Sub testing()

    MsgBox Payment_Customized(...) 'provide values for each argument

End Sub

Then hit F8 and step through the code.

Wigi

 

[wigi]

What strikes me is that the arguments do not appear in the Excel Insert Function menu

With only (sic) the first 13 arguments, it will work. 18 will be too much.

 

[pecosvil]

so is there a limit on the amount of arguments that can be passed to a user-defined function? i couldn't find any documentation on that...

 

[wigi]

Apparently, for the dialog screen "Insert function" it is...

But this will be a limit on the number of characters (argument names' length).
I bet that if you use shorter argument names it will work...

 

[pecosvil]

you are right. i changed the function to

Public Function pp_cstmzd(arg1 As Integer, arg2 As Double, arg3 As Date, arg4 As Date, arg5 As Integer, arg6 As String, arg7 As String, arg8 As String, arg9 As Double, arg10 As Integer, arg11 As Double, arg12 As Integer, arg13 As Date, arg14 As Date, arg15 As Integer, arg16 As Double, arg17 As Integer)


and it works. wonder why that happens. do you know any easy way of writing a description for each of the arguments? i need users to be able to easily select the relevant cells in the worksheet to pass the correct argument....


many thanks!!!!