Python functions allow optional arguments to be specified by allocating a default value to each optional argument. The pyxll add-in allows Python functions called from Excel to work in the same way, so any argument with a default value may be omitted, in a similar way to using the “optional” key-word in VBA.

This raises the question, if an Excel function is being used to call a Python function with default arguments, and both functions require a default value to be specified, how do we ensure that the default values are consistent?

The obvious solution is to specify all the Excel defaults as “None”, so that the Python function applies its specified defaults; however this does not work. “None” is not the same as “nothing”, so the Python function treats the argument value as being “None”, rather than applying the default. An example is the Numpy “linspace” function, where the boolean “endpoint” argument has as default value of True, but setting all defaults to None will result in a default of False, so the resulting series will have one less value, with an increased space between the numbers.

Solutions that do work include:

1. Assign all the Python function default values to the Excel function.
2. Pass the optional arguments to the Excel function as a single dictionary, using the Python argument names. It is then not necessary to specify default values in the Excel function. Any missing arguments will then not be passed to the Python function, and the Python defaults will be used.
3. Assign a default value of “None” to all the optional arguments in the Excel function, then form a dictionary in the Excel code, of any arguments that have a value other than “None”.

Option 1 provides simple coding, but requires extra work in extracting the correct default value from the documentation (which is not always clearly stated).

Option 2 is also simple to code, but does not allow the creation of help for each argument when using the Excel function wizard.

Option 3 requires some extra coding, but allows the Python help for each argument to be copied to the function docstring, from where it can be accessed in Excel using the function wizard.

Typical code for a Scipy Stats function using Option 3 is shown below:

# pyxll decorators, allowing the function to be called from Excel
@xl_func
@xl_arg('a', 'numpy_array', ndim = 1)
@xl_arg('numbins', 'int')
@xl_arg('defaultreallimits', 'numpy_array', ndim = 1)
@xl_arg('weights', 'numpy_array', ndim = 1)
@xl_return('numpy_array')
# assign the default None to all optional arguments
def py_relfreq(a, numbins = None, defaultreallimits = None, weights = None):

#  Help that will appear in the Excel function wizard
    """
Return a relative frequency histogram, using the histogram function.
    :param a:        Input array.
    :param numbins:        The number of bins to use for the histogram. Default is 10.
    :param defaultreallimits:        The lower and upper values for the range of the histogram. If no value is given, a range slightly larger than the range of the values in a is used. Specifically ``(a.min() - s, a.max() + s)``, where ``s = (1/2)(a.max() - a.min()) / (numbins - 1)``.
    :param weights:        The weights for each value in `a`. Default is None, which gives each value a weight of 1.0
    """

    #  create a dictionary of each optional argument name, with the argument value
    kwargs = {'numbins': numbins, 'defaultreallimits': defaultreallimits, 'weights': weights}

    # extract the arguments that have a value other than None
    kwargs = {k:v for k,v in kwargs.items() if v is not None}

    # call the function, passing the dictionary using **kwargs
    rf = stats.relfreq(a, **kwargs)

    # convert the results to a 2D array that can be passed back to Excel
    ncols = len(rf[0])
    binwidth = rf[2]
    nrf = np.zeros((2,ncols))
    nrf[0,:] = rf[0]
    nrf[1,0] = rf[1]+binwidth
    for i in range(1, ncols):
        nrf[1,i] = nrf[1,i-1] + binwidth
    return nrf 

Use of this function from the Excel function wizard is shown in the screen-shot below (click the image for full-size view):

This code will generate the correct function defaults, but creates a problem in the case where “None” is a valid option, but not the default, since entering None as the argument value will result in the default value being used.  The next post will look at how to handle that situation.