Passing variable argument lists from Excel to Python via xlwings

When calling a Python function it is sometimes necessary to pass a variable number of arguments.  Examples from the xlw_Scipy spreadsheet include:

  • Curve fitting functions, for which the functions to be fitted may have any number of arguments.
  • The Scipy statistics and distance functions have different optional arguments, depending on the statistic or distance metric being used.

Options to provide the required data are:

  • Pass a single range to Python, and extract the required arguments in Python code.
  • Pass a single range to VBA, and extract the required arguments in VBA code, passing  to Python as a single array.
  • Use a VBA ParamArray argument to combine any number of VBA arguments into a single array which can be passed to Python.
  • Use a single range or ParamArray to pass pairs of argument names and values to Python, where it can be easily converted to a dictionary.
  • Use the xlwings Dictionary converter, that turns a 2 column range into Python dictionary format.

The advantages and disadvantages of these methods are discussed below, with examples in the spreadsheet PassParamarray.xlsb and associated Python code, which can be downloaded from PassParamarray.zip.

In the examples below data is passed to Python functions in various formats.  The functions return two columns with the value of each argument (or the value of the first element in the case of an array), and the argument data type.

In the first three examples the data is passed to VBA as a single column or row.  In the first two cases this array is passed directly to the Python function.  In the third any text defining a spreadsheet range is converted to a variant array, before being passed to Python.

The VBA code for the xl_Range1 function is:

Function xl_Range1(DRange As Variant)
Dim NRows As Long, i As Long, RangeA() As Variant

DRange = DRange.Value2
NRows = UBound(DRange)
ReDim RangeA(1 To NRows)
For i = 1 To NRows
On Error Resume Next
RangeA(i) = Range(DRange(i, 1)).Value2
If IsArray(RangeA(i)) = False Then
If RangeA(i) = 0 Then RangeA(i) = DRange(i, 1)
RangeA(i) = Array(RangeA(i))
End If
Next i
xl_Range1 = RtnRange1(RangeA)
End Function

xl_Range1 calls the auto-generated VBA function RtnRange1, which passes the data to the Python function:

@xw.func
@xw.arg('rng', ndim = 1)    
def RtnRange1(rng): 
    """
    Return a value and  data type from each value in rng
    rng contains arrays or values 
    """
    rtn = []
    i =0
    for val in rng:
        if type(val) is tuple:
            if type(val[0]) is tuple:
                rtn.append([val[0][0], str(type(val))])
            else:
                rtn.append([val[0], str(type(val))])
        else:
            rtn.append([val, str(type(val))])
    return rtn

Passing a single range, the arguments must be either single values, or range addresses or names that can be converted to arrays in the VBA code. An alternative is to use the VBA ParamArray object, that allows any number of values or ranges to be passed to VBA, as shown in the three examples below:

The xlwParamA function (first two examples above) converts each element of the ParamArray to an array of values.

Function xlwParamA(ParamArray x() As Variant)
Dim Res As Variant, i As Long
   On Error GoTo rtnerr:
   
    For i = 0 To UBound(x)
        If TypeName(x(i)) = "Range" Then x(i) = x(i).Value2
        If IsArray(x(i)) = False Then x(i) = Array(x(i))
    Next i
    
    Res = Py.CallUDF("PassParamarray", "RtnArray", Array(x), ThisWorkbook, Application.Caller) 'RtnArray(x)

    xlwParamA = Res
    Exit Function
rtnerr:
    xlwParamA = Err.Description
    
End Function

The ParamArray object is then passed to the Python RtnArray function:

@xw.func
def RtnArray(x): 
    """
    Return a value and  data type from each value in rng
    """
    rtn = []
    for val in x:
        if type(val) is tuple:
            if type(val[0]) is tuple:
                rtn.append([val[0][0], str(type(val))])
            else:
                rtn.append([val[0], str(type(val))])
        else:
            rtn.append([val, str(type(val))])
    return rtn

In the third example the data is passed to Python as a list of arguments, using the Python *args notation:

@xw.func
def RtnRange3(*args): 
    """
    Return a value and  data type from each value in rng
    rng contains arrays or values 
    """
    rtn = []
    i =0
    for val in args:
        if type(val) is list:
            if type(val[0]) is list:
                rtn.append([val[0][0], str(type(val))])
            else:
                rtn.append([val[0], str(type(val))])
        else:
            rtn.append([val, str(type(val))])
    return rtn

xlwings recognises Python *args as being equivalent to a VBA ParamArray, and automatically generates appropriate code for the UDF:

Function RtnRange3(ParamArray args())
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        ReDim argsArray(1 To UBound(args) - LBound(args) + 1)
        For k = LBound(args) To UBound(args)
        argsArray(1 + k - LBound(args)) = args(k)
        Next k
        RtnRange3 = Py.CallUDF("PassParamarray", "RtnRange3", argsArray, ThisWorkbook, Application.Caller)
        Exit Function
failed:
        RtnRange3 = Err.Description
End Function

The final set of examples transfer pairs of values, or values and ranges, in a format that can easily be converted to a Python dictionary and used as keyword arguments for Python functions. In these examples the Python function returns a three column range, with the dictionary keys, as well as the value and data type for each item:

The first three examples above are similar to the earlier single column examples, except with the addition of data for the dictionary keys (see the download file for VBA and Python code).

The next two examples make use of the VBA ParamArray object to pass an array of values or arrays. As before, the version using the Python *args argument allows the VBA code to be generated automatically, and is recommended:

@xw.func
def RangeDict3(*args): 
    """
    Convert paramarray to dictionary and return index, value and types 
    """
    kwargs = dict(args[i:i+2] for i in range(0, len(args), 2))
    rtn = []
    for indx in kwargs:
        rtnv = kwargs[indx]
        if type(rtnv)is list: rtnv = rtnv[0]
        if type(rtnv)is list: rtnv = rtnv[0]
        rtn.append([indx, rtnv, str(type(kwargs[indx]))])
    
    return rtn

Using the xlwings add-in Import Functions automatically generates the VBA code for the UDF:

Function RangeDict3(ParamArray args())
        If TypeOf Application.Caller Is Range Then On Error GoTo failed
        ReDim argsArray(1 To UBound(args) - LBound(args) + 1)
        For k = LBound(args) To UBound(args)
        argsArray(1 + k - LBound(args)) = args(k)
        Next k
        RangeDict3 = Py.CallUDF("PassParamarray", "RangeDict3", argsArray, ThisWorkbook, Application.Caller)
        Exit Function
failed:
        RangeDict3 = Err.Description
End Function

Finally the xlwings Dictionary converter allows a two column range to be passed as keys and values of a Python dictionary, without additional coding:

@xw.func
@xw.arg('args', dict)
def RangeDict(args): 
    """
    Return a specified item from a dictionary of range addresses
    """
    rtn = []
    
    for indx in args:
        rtnv = args[indx]
        rtn.append([indx, rtnv, str(type(rtnv))])
    
    return rtn
This entry was posted in Arrays, Excel, Link to Python, NumPy and SciPy, UDFs, VBA, xlwings and tagged , , , , , , , , . Bookmark the permalink.

2 Responses to Passing variable argument lists from Excel to Python via xlwings

  1. maurizio nardò says:

    I think it is worth to clarify that Excel and Python behave differently in how they treat default arguments. As an example, consider the scipy function brentq:

    scipy.optimize.brentq(f, a, b, xtol=1e-12, rtol=4.4408920985006262e-16, maxiter=100, full_output=False, disp=True, *args

    and assume the function is exposed in Excel clarifying there are default values for some arguments. Unfortunately, while Python is passing arguments by keyword, and it can skip those with a defaul valuet, Excel is only positional. If we call brentq from Excel with xtol and rtol unset:

    scipy.optimize.brentq(f,a,b,,,50)

    what Python will see is
    scipy.optimize.brentq(f,a,f,None,None,50)

    and of course this will result in an error, because xtol and rtol must be numbers (actually we wanted to keep their default values). The approach to build a dictionary in Excel with vba to solve the problem seems too much to me. My preferred way is to wrap brentq in Python

    def xl_brentq (f, a, b, xtol=None, rtol=None, maxiter=None, full_output=None, disp=None,*args):
    return scipy.optimize.brentq (**{k: v for k, v in locals().items() if v!=None})

    Now you can call it from Excel as xl_brentq(f,a,b,,,50) and it will work: the wrapper function is building the call to scipy.optimize.brentq using only arguments different from None; xtol and rtol are not given, and so scipy.optimize.brentq will use its defaults. In other words, the wrapper is building the right dictionary for us.

    Like

    • dougaj4 says:

      Thanks for the comment.
      In the case given in your example, I agree that using optional arguments in Python is now the easiest way to go, since xlwings will now generate the required optional arguments in the VBA UDF, and generate the correct input for the Python function.

      However for this to work we need to know the number of required and optional arguments. The purpose of the procedures given in the article is to use a single VBA UDF
      to call Python functions that have a variable number of optional arguments, or to use a single VBA function to call a large number of different Python functions, which may have different numbers of required and optional arguments. An example of the latter is calling the Scipy statistics functions (see https://newtonexcelbach.com/2018/07/10/scipy-statistics-functions-coding-and-getting-help/ for details).

      Another use is when the Python function has a large number of optional arguments, which is the case for many Scipy functions. This procedure allows the optional arguments to be passed as kwargs, rather than having to define them all as optional arguments in the wrapper function.

      Like

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.