How to use a list returned by a Python macro from VBA? – PyXLL

Using PyXLL you can write Excel macros in Python that can be called from Excel in the same way as any other Excel macro, including from VBA.

For simple, primitive, types (e.g. numbers, strings, etc) the values returned from Python map directly to VBA types. But what about Python functions that return a list of values?

Below is an example of a Python function, exposed as an Excel macro, that returns a list of value:

# Python
from pyxll import xl_macro

@xl_macro(": int[]")
def get_list():
    return [1, 2, 3]

This macro can be called from VBA as follows, but what type is the result?

# VBA
Sub ListTest()
    Result = Run("get_list")
End Sub

The Result is a 2d Variant Array. Even though the Python function returns a 1d list, it gets converted to a 2d Variant Array when returned to Excel. This is to match the grid structure of Excel as UDFs (User Defined Functions) are typically called from worksheet functions and the result is output to the Excel grid.

The following code shows how to iterate over the 2d array in VBA:

# VBA
Sub ListTest()
    Result = Run("get_list")
    For i = LBound(Result , 1) To UBound(Result , 1)
        For j = LBound(Result , 2) To UBound(Result , 2)
            Value = Result(i, j)
        Next j
    Next i
End Sub

Do you need to use VBA?

It is common to call a Python macro from VBA while in the process of transitioning from VBA to Python. It is a perfectly valid technique to use and avoids having to re-write everything in one go.

You don't need to use any VBA however. If you are not transitioning from a legacy VBA code base then there is no need to start writing new VBA code, you can do everything only using Python code.

PyXLL provides the convenience function xl_app, which returns the Excel Application object. This is the same Application object you would have used in VBA, and everything you need is available through the Application object.

For more details about using Python as a VBA replacement please see this page from the docs https://www.pyxll.com/docs/userguide/vba.html.

Do you need to use VBA?

Related articles