Addin02.md

Array arguments

You can pass a range as a function argument, as opposed to a single cell. Its value will be converted to a tuple of tuples.

• Add the following code to Book1.py from the previous tutorial

  @xlfunc
  def MyUDF(x):
      return repr(x)

  This function simply returns its argument converted to string representation. This will allow us to explore how formula arguments are converted into Python objects.

• Click 'Import Python UDFs' to pick up the changes

• Modify the workbook as below

  

As you can see the value of the 2x2 range F1:G2 has been convert to a tuple containing tuples representing the range's two rows.

At this point it is worth talking about one of Excel's oddities, namely that the value of a 1x1 range is always a scalar, whereas the value of any range larger than 1x1 is represented by a two-dimensional array.

ExcelPython provides a mechanism for normalizing the input arguments so that your function can safely make assumptions about their dimensionality.

• Modify Book1.py as follows

  @xlfunc
  @xlarg("x", dims=2)         # add this line
  def MyUDF(x):
  	return str(x)

• Click 'Import Python UDFs' to pick up the changes.

• Now 1x1 ranges are passed as two-dimensional

  

At other times it you may want to assume that an argument that is one-dimensional

• Modify Book1.py as follows

  @xlfunc
  @xlarg("x", dims=1)         # modify this line
  def MyUDF(x):
  	return str(x)

• Click 'Import Python UDFs' to pick up the changes.

  

  

  

• Clearly having specified the argument as one-dimensional, an error is raised if a two-dimensional range is passed

  

To continue move onto the next tutorial.