Author Topic: How to deal with too many parameters in UDF's  (Read 696 times)

uwmc

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
How to deal with too many parameters in UDF's
« on: February 20, 2015, 02:19:27 AM »
Hi,

I am new to pyxll. I wrote a function in python that needs to take 26 parameters which are in different type (i.e. string, float, date and cached_object). But in Excel this function cannot be accessed and no error message is shown in the log, so I am guessing the number of parameters exceeds the maximum allowed by Pyxll. Does anyone have a solution to this issue? Thank you very much! :)

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 574
  • Karma: +13/-1
    • View Profile
Re: How to deal with too many parameters in UDF's
« Reply #1 on: February 20, 2015, 10:39:15 AM »
Hi,

Excel *should* allow up to 255 arguments from Excel 2007, and up to 30 before that... and if there's no error in the log that suggests PyXLL thinks your function should be ok, but maybe it's failing to register with Excel for some other reason (possibly the limit is lower for macro sheet equivalent functions, which yours will be since you're using the cached_object example).

Anyway, for functions that take a large number of arguments I find it's often better to have the function take a table instead so that the arguments are laid out in the sheet - it can make things a bit clearer. For example, your function could take a table with two columns, the first column being the parameter names and the second column being their values. You would then process that table in your python code to get the actual function parameters (and can default any missing or return an error if they can't be defaulted).

As you need to pass different types, you can make the table of type "var[]" and use the pyxll function get_type_converter to convert from 'var' to whatever types you're expecting (see https://www.pyxll.com/docs/index.html?highlight=get_type_converter#pyxll.get_type_converter).

If you don't like the idea of having a table of parameters in the sheet, another option would be to group related parameters and pass them in as arrays. You could even have a utility function that takes a number of parameters and constructs a 2d array from them if it's not convenient to have any parameters next to each other in a block on the sheet (eg a function that takes in total 6 parameters could be re-written to take two arrays of 3, and a function 'create_array' could create an array from some other arguments: "=my_func(create_array(A1,A3,A5), create_array(C1, C3, C5))" ).

I hope this helps,

Best regards,
Tony

uwmc

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: How to deal with too many parameters in UDF's
« Reply #2 on: February 20, 2015, 03:20:45 PM »
Hi,

Thanks very much for the response!
You said the table should be a type of var[], but can var take non-standard types? (eg cached_object and custom types?)

Thanks again!

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 574
  • Karma: +13/-1
    • View Profile
Re: How to deal with too many parameters in UDF's
« Reply #3 on: February 20, 2015, 05:06:16 PM »
Hi,

custom types all end up getting returned to Excel as one of the types Excel understands (e.g. cached_object is returned to Excel as a string). When passed as a var type your Python function will get it as the basic underlying type (so a string in the case of cached_object), but then you use get_type_converter to find the function that converts from 'var' to the custom type, e.g.:

@xl_func("var x: var")
def my_func(x):
   # x is a string
   var_to_cached_object = get_type_converter("var", "cached_object")
   obj = var_to_cached_object(x)
   # obj is the object fetched from the object cache

Best regards,
Tony