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
Newton Excel Bach, not (just) an Excel Blog 