Python optional arguments from Excel – Part 2

The previous post provided a method for using pyxll to pass optional arguments from Excel to Python whilst preserving the default values of any called Python function for arguments that were omitted in the Excel function.

One condition where this does not work is for Python functions where “None” is a valid argument, but it is not the default. For instance, many Scipy functions working with multi-dimension array input have an “axis” argument that allows “None” as input, but the default is 0. In this case the default value for the Excel function should be set to 0, and the data type set to variant. Before calling the Python function two additional steps are then required:

  • If the argument is a number, convert it from “float” to “int”.
  • If the argument is the string “None” convert it to the Python None object.

Typical code is shown below:

@xl_func
@xl_arg('a', 'numpy_array', ndim = 2)
@xl_arg('axis', 'var')
@xl_arg('dtype', 'str')
@xl_return('numpy_array')
def py_hmean(a, axis = 0, dtype = None):
    ...
    if axis != 'None': axis = int(axis)
    kwargs = {'axis': axis, 'dtype': dtype}
    kwargs = {k:v for k,v in kwargs.items() if v is not None}
    if axis == 'None': kwargs['axis'] = None
    res = stats.hmean(a, **kwargs)

This entry was posted in Excel, Link to Python, NumPy and SciPy, PyXLL, UDFs, VBA and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.