Help passing/recieving multi-dimensional lists to/from Python functions

[Part-Timer]

Can you help me understand how to send and receive multi-dimensional lists
via PyCall?

Sending to a Python function:

Per you wiki example I'm able to send a simple list to a python method via
PyTuple (e.g. PyTuple("value1", "value2", "value3"). I can't figure out how
to send the following examples without breaking the array into it's
individual elements:

• A single worksheet row passed to a Python function expecting a
  simple list. For example, passing
  PyTuple(Worksheets("Test").Range("A1").Value2,
  Worksheets("Test").Range("B1").Value2) works but passing
  PyTuple(Worksheets("Test").Range("A1:B1").Value2)) does not.

• an array of arrays or multi-row spreadsheet range (e.g.
  Worksheets("Test").Range("A1:B5").Value2) passed as a list of lists

Could you post examples of how to do this?

Receiving from a Python function:

Your example is clear on how to receive a simple list via the
PyStr(PyGetItem. functions (e.g. Worksheets("Test").Range("A4:B4").Value2 =
PyVar(PyGetItem(res, "sorted")), but how you receive a multi-dimensional
list into a multi-row worksheet range? Could you post an example?

Thanks:

Thank you for creating this tool, the parts I have working are wonderful and
are fast! I was having freezing/crashing issues with Excel/VBA, and have
wanted to migrate to Python for some time and just use Excel for display and
ad hoc analysis and this tool enables me to do that.

[Eric Reynolds]

Hi,
thanks for the question. I'll give you a quick answer then a longer explanation.

Quick answer

• Try PyCall(..., "f", PyTuple(PyObj(Worksheets("Test").Range("A1:B1").Value2, 1)))
  and you should find that the function f is called with a single argument which is a list, equivalent to a statement like f([1, 2]).
• If you call PyCall(..., "f", PyTuple(Worksheets("Test").Range("A1:B2").Value2)) you should find that the function f is called with a single argument which is a list of lists, equivalent to a statement like f([[1, 2], [3, 4]]).
• Try the following to write a multi-dimensional array to a worksheet range: Worksheets("Test").Range("A1:B2").Value = PyVar(PyEval("[[1, 2], [3, 4]]"), 2).

Explanation

Let me give you a bit of background. Python functions essentially take two types of arguments, positional and keyword. In ExcelPython this is reflected in the fact that in PyCall you can either pass a tuple to Args or a dictionary to KwArgs (or both). Therefore PyCall(..., "f", Args:=PyTuple(1,2,3)) is equivalent to the Python statement f(1,2,3) because you are passing three positional arguments.

Now, the ExcelPython function PyTuple(...) builds a tuple comprised of the arguments you pass, in the process converting each one to a Python object if it isn't one already. Actually this is true of all ExcelPython functions, wherever a Python object is expected, if you pass a VBA object it will first convert it.

How does the conversion work? ExcelPython takes its best guess at what the corresponding Python object should be for the given VBA object. Obviously VBA strings are converted to Python str objects, numbers are converted to float or int and so on. VBA arrays are converted into lists if they are 1-dimensional, and into lists of lists if they are 2-dimensional. The function PyObj serves to do exactly this conversion, so the following are equivalent statements: PyTuple(1, "A") and PyTuple(PyObj(1), PyObj("A")).

So far so good - the problem arises due to Excel's oddities when dealing with range values.

Any expression which selects a single cell will return a scalar value, not an array so PyObj(Sheet1.Range("A1:A1").Value2) will always be a scalar, e.g. a string or a number etc.

Any expression which selects multiple cells will return a 1-dimensional array of 1-dimensional arrays regardless of whether the range selected is really a square rather than just a row or a column, so both PyObj(Sheet1.Range("A1:B1").Value2) and PyObj(Sheet1.Range("A1:B4").Value2) will generate a list of lists.

This is where the PyObj function's optional parameter comes in: you can specify what dimensionality you want the converted Python object to have. So:

• if you want a list of list regardless of the range's size you can do
  PyObj(Sheet1.Range("A1:A1").Value2, 2) or PyObj(Sheet1.Range("A1:B1").Value2, 2) or PyObj(Sheet1.Range("A1:A4").Value2, 2) or PyObj(Sheet1.Range("A1:B4").Value2, 2) and the result will always be a list of lists.

• if you want a list of scalars regardless of the range's size you can do PyObj(Sheet1.Range("A1:A1").Value2, 1) or PyObj(Sheet1.Range("A1:B1").Value2, 1) or PyObj(Sheet1.Range("A1:A4").Value2, 1) or PyObj(Sheet1.Range("A1:B4").Value2, 1) and you'll always get a 1D list, except in the last case which will fail.

You can test these by typing, for example,
?PyStr(PyObj(Sheet1.Range("A1:A4").Value2, 1))
or
?PyStr(PyObj(Sheet1.Range("A1:A4").Value2, 2))
into the immediate window.

The inverse of the PyObj function is the PyVar function which converts a Python object to a VBA object. This also has an optional dimension parameter which enables you to specify, for example, whether you want a list of lists to be converted into a 2D VBA array or a 1D array of 1D arrays.

[Part-Timer]

Eric, Thank you for your very detailed response. I now have everything working per your instructions.

At some point it would be useful to have a function help document like the Python library function list here http://docs.python.org/2/library/functions.html. That way users could better self-serve and not bug you as much.

Thanks again.

[Eric Reynolds]

Yeah I guess, but it sounds like a lot of effort :D

Plus I like people bugging me, at least I get a bit of feedback!

Regards,

Eric.