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)