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
Posted in Arrays, Excel, Link to Python, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , , | Leave a comment

Two songs = 2/3 Wikipedia

It is said that a picture is worth a 1000 words.

On the same basis, a poem is worth 1000 pictures, and a song is worth 1000 poems.

So two songs are worth 2 billion words, which is about 2/3 of all the words in Wikipedia.

Enjoy:

Another Okenyo song with video directed by my daughter:

… and from Anais Mitchell “Before the Eyes of Storytelling Girls“:

I could tell you stories like the government tells lies
ah, but no one listens anymore
in the rooms, the women come and go
talking on the mobile phones
and the television talks about the war
when i was a baby, there was laughter in my house
my daddy smoked domestic cigarettes
and Thursday nights on the radio
live in concert- live from Cairo:
mother of Egypt!

mama, mama, be with me
with the music in your breast
in your glittering evening dress
and the white flag in your fist trembling

I could tell you stories like the past was dead and
gone
but I know nothing changes in this world
every day the muezzin calls
the sun comes up and Baghdad falls
before the eyes of storytelling girls
she was just a poor man’s daughter
going down into the sultan’s bed
he was desert, she was water
and he remembered every word she said, that she said,

and I say, grandma, grandma, be with me
in your tragic wedding gown
with your long hair hanging down
and the stories tumbling out, tumbling

I could tell you stories like the government tells lies
ah, but no one listens anymore
in the rooms the women come and go
talking on the mobile phones
and the television talks about the war, about the war
the television talks about the war

Posted in Bach | Tagged , , | 3 Comments

New Links – 2018

Some new links to active Excel related blogs:

David Hagar has been active in the Excel community for many years, and has published the Excel For You blog since 2013.  I was led to it by a recent post on a UDF generating a list of antonyms.Excel For You.

Excel Matters is the blog of self-confessed Excel addict Rory Archibald.

My Spreadsheet Lab is run by Kevin Lehrbass and has a wide range of useful articles, plus numerous links valuable links.

 

 

Posted in Computing - general, Excel | Tagged , , , , | Leave a comment

Returning Arrays From VBA and Python

Most  of the  user defined functions (UDFs) presented on this blog return a multi-cell array, that must be entered by selecting the output range then pressing Ctrl-shift-enter to display all the results.  This process has several inconveniencies:

  • You need to know the size of the array, or work by trial and error.
  • It is easy to increase the size of the output range, by selecting the new range and re-entering, but there is no easy way to reduce the size.  You need to delete the array and re-enter.
  • If the size of the output array changes it must be re-sized manually, or it will display #N/A results, or only part of the array.

These problems can be resolved, at least partially, using VBA macros, or when using Python and xlwings, using the xlwings dynamic array functions.  Both methods are illustrated below using the Python Numpy spreadsheet from the previous post.

To use the VBA macros, enter the function in the top-left cell of the output range, and enter in the usual way:

xlw_numpy3-1

Then press Ctrl-Shift-S and all of the UDF results will be displayed.  Note that any existing data within the output range will be deleted.

xlw_numpy3-2

To re-size the array, select the required range and press Ctrl-Shift-R:

xlw_numpy3-3

These VBA functions can be added to any Excel file containing UDFs that return array functions.  The code may be copied from the xlw_Numpy spreadsheet:

xlNumpy.zip

When using xlwings, dynamic arrays are available.  Simply add an @xw.ret(expand =’table’) decorator to each function where a dynamic array is required:

@xw.func
@xw.arg("x", np.array, ndim = 2, dtype = np.float64)
@xw.ret(expand='table')
def xl_isfinite(x):
    return np.isfinite(x)

The function may then be entered in the top left cell of the output range:

xlw_numpy3-4

and the results will automatically be generated over the full extent of the table:

xlw_numpy3-5

The results will automatically write over any data within the output range, and also the column to the right and the row below.   The data will automatically resize to show the full extent of the array, but the output range cannot be adjusted to show only part of the array (other than by re-sizing the array returned from Python).

At the time of writing (1st June 2018), the code does not always fully recalculate after a global recalculation of the sheet or workbook:

xlw_numpy3-6

and if results are deleted or overwritten the arrays do not always recalculate:

xlw_numpy3-7

These problems are a recognised bug,  which should be fixed in the next release.

Posted in Arrays, Excel, Link to Python, UDFs, VBA, xlwings | Tagged , , , , , , , | Leave a comment

More Numpy with Excel and xlwings

The spreadsheet presented in the last post on sorting also includes links to many other Numpy functions.  The spreadsheet and associated Python file can be downloaded from:

xlNumpy.zip

The Numpy functions require xlwings and Numpy to be installed, which are included in the (free) Anaconda Python default installation.

All the Numpy functions in this spreadsheet return an array.  The whole array may be displayed either by entering as an array function, or using xlwings dynamic array formulas.  The spreadsheet also includes two VBA macros to display the entire array function (press Ctrl-Shift-S), or to re-size the display to the selected range (press Ctrl-Shift-R).  The next post on this topic will give more details.

The Numpy functions included are shown in the screenshots below.  See the Numpy Documentation for more details.:

Polynomial functions:

  • xl_PolyRoots
  • xl_PolyFromRoots
  • xl_PolyVal

xl_PolyFit fits a polynomial to scattered data

  • xl_PolyCompanion
  • xl_PolyDer
  • xl_PolyInt
  • xl_PolyAdd
  • xl_PolySub
  • xl_PolyMul
  • xl_PolyDiv
  • xl_PolyPow

Maths functions operating on arrays:

  • xl_add
  • xl_subtract
  • xl_multiply
  • xl_divide
  • xl_logaddexp
  • xl_logaddexp2
  • xl_true_divide
  • xl_floor_divide
  • xl_negative
  • xl_positive
  • xl_power
  • xl_remainder

  • xl_mod
  • xl_fmod
  • xl_divmod
  • xl_absolute
  • xl_fabs
  • xl_rint
  • xl_sign
  • xl_heaviside
  • xl_conj
  • xl_exp
  • xl_exp2
  • xl_log
  • xl_log2
  • xl_log10
  • xl_expm1
  • xl_log1p

  • xl_sqrt
  • xl_square
  • xl_cbrt
  • xl_reciprocal

Trigonometric Functions:

  • xl_sin
  • xl_cos
  • xl_tan
  • xl_arcsin
  • xl_arccos
  • xl_arctan
  • xl_arctan2
  • xl_hypot

  • xl_sinh
  • xl_cosh
  • xl_tanh
  • xl_arcsinh
  • xl_arccosh
  • xl_arctanh
  • xl_deg2rad
  • xl_rad2deg

Floating Point Functions:

  • xl_isfinite
  • xl_isinf
  • xl_isnan
  • xl_fabs
  • xl_signbit
  • xl_copysign
  • xl_nextafter
  • xl_spacing

  • xl_modf
  • xl_ldexp
  • xl_frexp
  • xl_fmod
  • xl_floor
  • xl_ceil
  • xl_trunc

Numerical Ranges:

  • xl_arange
  • xl_linspace

  • xl_logspace
  • xl_geomspace

  • xl_meshgrid

 

 

Posted in Arrays, Excel, Link to Python, Maths, Newton, NumPy and SciPy, UDFs, VBA, xlwings | Tagged , , , , , , , , , , | 1 Comment

More Lowlands

Sometimes YouTube suggestions are hopeless, and sometimes they are spot on.  Here is one of the latter:

It seems that Ali Darragh is yet to be deemed Wiki-worthy, but there is a good article about her here.

That link led me to a more traditional version by The Corries:

… and then back to the Anne Briggs unaccompanied version, which has featured here before:

Posted in Bach | Tagged , , , | Leave a comment

Office Insiders and Power Pivot

I recently discovered another benefit of the Office Insiders program through the ExcelUnplugged blog, which has a feature on the new linked data types in Excel.  More on those another day, but in addition to JavaScript UDFs, the added feature that I found to be of most immediate interest was that after signing up for the program Power Pivot becomes available on Office 365 Home and (I presume) the business plans where it was previously excluded.

See more about Power Pivot.

 

Posted in Computing - general, Excel, Javascript | Tagged , , , | Leave a comment