XLPack for Python User’s Manual

1. Introduction

XLPack for Python (hereinafter abbreviated as XLPack PY) is the add-in program that enables calling Python from Excel VBA.

It is similar to Microsoft’s Python in Excel, but it works using a different mechanism. A comparison table is shown below.

In summary, “XLPack PY is the add-in for using Python from VBA programs. It requires Python to be installed locally on the PC, but it can be used even without an Internet connection.”

2. Example program

Let’s look at a simple example to get a better idea of how it works.

Prepare two cells each for A, B, and C in the worksheet as follows, and enter values into A and B.

Prepare the following VBA program.

Sub Test_PY()
    Call PY( _
        "from XLPackPy import *" + vbLf + _
        "a = xl('B3:B4')" + vbLf + _
        "b = xl('C3:C4')" + vbLf + _
        "c = []" + vbLf + _
        "for i in range(2):" + vbLf + _
        "  c.append(a[i] + b[i])" + vbLf + _
        "xl('D3:D4', c)")
End Sub

When you run this, the result of A + B will be entered into cell C as shown below.

As in this example, you can execute a Python program by joining each line into a single string separated by “vbLf” (you can also use “vbCrLf” or “vbNewLine”) and then calling “PY()”. The “_” indicates a line continuation in VBA. In general, this approach is best suited for running short programs.

If you want to run a longer program, it’s better to put the code in a file and call “PYF()”.

The “xl” function used in the Python program is for reading from and writing to Excel cells.

In VBA, a statement like “Call PY(‘…’)” can simply be written as “PY ‘…'”. Using this, you can also write it as follows.

Sub Test_PY()
    PY "from XLPackPy import *"
    PY "a = xl('B3:B4')"
    PY "b = xl('C3:C4')"
    PY "c = []"
    PY "for i in range(2):" + vbNewLine + _
       "  c.append(a[i] + b[i])"
    PY "xl('D3:D4', c)"
End Sub

This version may be easier to read. Note that Python is called at each step, so it may seem inefficient, but it’s not significant enough to be a concern. Also, keep in mind that a “for” loop is treated as a single step, including its contents.

3. A VBA subroutine for launching Python

A VBA subroutine for launching Python is provided. To use it, you need to enable the XLPack PY add-in.

3.1 PY() VBA subroutine

It executes a string containing a Python program.

Call PY("Python codes")

In the current version, if there is an error in the Python program, detailed error messages like those in standard Python are not displayed. Instead, a message box simply shows “Python error occurred.”

In other words, if an error occurs, you can only tell that an error happened at the level of the “PY()” call. Therefore, it may be preferable to use the second notation in the example above, where “PY ‘…'” is used, so that at least you can identify which step the error occurred in.

3.2 PYF() VBA subroutine

It executes a specified file containing a Python program.

Call PYF("file name")

It is suitable for running relatively large Python programs.

As with “PY()”, if there is an error in the Python program, a message box will simply display “Python error occurred.”

4. Python functions for exchanging data with Excel

Python functions for exchanging data with Excel will be provided. To use them, you need to import XLPackPy module as follows.

from XLPackPy import *

These functions work only within Python code invoked by PY() or PYF().

In the current version, the worksheet used for data exchange is limited to the currently open worksheet (current worksheet). No other workbook or worksheet can be specified.

Below, the usage of each function is explained. Assume that the worksheet contains the following data. Let the top left cell be cell B5.

4.1 xl() Python function

4.1.1 Reading data in Excel cells

This function reads cells from Excel and returns them as a list (equivalent to a two-dimensional array). The argument specifies the cells using a string in A1 notation.

a = xl(string specifying cells)

A single cell is specified like ‘B5’. Multiple cells are specified by separating the top-left and bottom-right cells with a colon like ‘B5:D8’.

This function normally returns a list (equivalent to a two-dimensional array). As exceptions, however,when a single cell is specified, it returns a single value, and when a single row or column is specified, it returns a list (equivalent to a one-dimensional array).

None is returned if there is an error.

The example data in the worksheet above would look like below.

xl('B5:D8')
= [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0], [10.0, 11.0, 12.0]]
xl('B5:B8')
= [1.0, 4.0, 7.0, 10.0]
xl('B5:D5')
= [1.0, 2.0, 3.0]
xl('B5:B5')
= 1.0
xl('B5')
= 1.0

4.1.2 Writing data to Excel cells

Writes the given Python data to Excel cells. The arguments are a cell reference in A1 notation and the data to be written.

ret = xl(string specifying cells, data)

The elements of the Python data must be integers, floating-point numbers, or strings. Any other types are ignored.

As with reading, the data is normally provided as a list (equivalent to a two-dimensional array). However, as exceptions, when a single cell is specified, a single value must be provided as the data, and when a single row or column is specified, a list (equivalent to a one-dimensional array) must be provided as the data.

True is returned if the write was successful. False is returned if unsuccessful.

See examples below.

xl('B5', 1)
xl('B5:D5', [1, 2, 3])
xl('B5:B8', [1, 4, 7, 10])
xl('B5:D8', [[1, 2, 3], [4, 5, 6], [7, 8, 9], [10, 11, 12]])
xl('B5', [[1]])   -> Not written
xl('B5:D5', [[1, 2, 3]])   -> Not written

4.2 xlc() Python function

In the xl() function, data is stored row-wise when viewed as a two-dimensional array, whereas languages such as VBA and Fortran store data column-wise. The xlc() function is a column-wise version that corresponds to this behavior. Other specifications are the same as for the xl() function.

4.2.1 Reading data in Excel cells

The example data in the worksheet above would look like below.

xlc('B5:D8')
= [[1.0, 4.0, 7.0, 10.0], [2.0, 5.0, 8.0, 11.0], [3.0, 6.0, 9.0, 12.0]]
xlc('B5:B8')
= [1.0, 4.0, 7.0, 10.0]
xlc('B5:D5')
= [1.0, 2.0, 3.0]
xlc('B5:B5')
= 1.0
xlc('B5')
= 1.0

4.2.2 Writing data to Excel cells

See examples below.

xlc('B5', 1)
xlc('B5:D5', [1, 2, 3])
xlc('B5:B8', [1, 4, 7, 10])
xlc('B5:D8', [[1, 4, 7, 10], [2, 5, 8, 11], [3, 6, 9, 12]])

4.3 xl_get() Python function

Reads cells from Excel and returns them as a list (equivalent to a two-dimensional array). The arguments specify the cells using row and column numbers starting from 0.

xl_get(row1, column1, row2, column2): Reads the cells in the specified rectangular area as a list (equivalent to a two-dimensional array).

xl_get(row1, column1, column2): Reads the cells in the specified row area as a list (equivalent to a one-dimensional array).

xl_get(row1, column1): Reads the specified single cell.

When four arguments (row1, column1, row2, column2) are specified, it always returns a list (equivalent to a two-dimensional array).

The example data in the worksheet above would look like below.

xl_get(4, 1, 7, 3)
= [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0], [10.0, 11.0, 12.0]]
xl_get(4, 1, 7, 1)
= [[1.0], [4.0], [7.0], [10.0]]
xl_get(4, 1, 4, 3)
= [[1.0, 2.0, 3.0]]
xl_get(4, 1, 4, 1)
= [[1.0]]
xl_get(4, 1, 3)
= [1.0, 2.0, 3.0]
xl_get(4, 1)
= 1.0

4.4 xlc_get() Python function

Reads cells from Excel and returns them as a list (equivalent to a two-dimensional array). The arguments specify the cells using row and column numbers starting from 0.

This is the column-wise version of xl_get().

xlc_get(row1, column1, row2, column2): Reads the cells in the specified rectangular area as a list (equivalent to a two-dimensional array).

xlc_get(row1, column1, row2): Reads the cells in the specified column area as a list (equivalent to a one-dimensional array).

xlc_get(row1, column1): Reads the specified single cell.

When four arguments (row1, column1, row2, column2) are specified, it always returns a list (equivalent to a two-dimensional array).

The example data in the worksheet above would look like below.

xlc_get(4, 1, 7, 3)
= [[1.0, 4.0, 7.0, 10.0], [2.0, 5.0, 8.0, 11.0], [3.0, 6.0, 9.0, 12.0]]
xlc_get(4, 1, 7, 1)
= [[1.0, 4.0, 7.0, 10.0]]
xlc_get(4, 1, 4, 3)
= [[1.0], [2.0], [3.0]]
xlc_get(4, 1, 4, 1)
= [[1.0]]
xlc_get(4, 1, 7)
= [1.0, 4.0, 7.0, 10.0]
xlc_get(4, 1)
= 1.0

4.5 xl_set() Python function

Writes Python data to cells specified by row and column numbers starting from 0.

The elements of the Python data must be integers, floating-point numbers, or strings. Any other types are ignored.

xl_set(row1, column1, row2, column2, list (equivalent to a two-dimensional array)): Writes list data (equivalent to a two-dimensional array) to the cells in the specified rectangular area.

xl_set(row1, column1, column2, list (equivalent to a one-dimensional array)): Writes list data (equivalent to a one-dimensional array) to the cells in the specified row area.

xl_set(row1, column1, data): Writes a single value to the specified cell.

Any data that extends beyond the specified row1, column1, row2, and column2 area is ignored. Also, if the data is insufficient, no writing is performed for the corresponding cells.

See examples below.

xl_set(4, 1, 7, 3, [[1, 2, 3], [4, 5, 6], [7, 8, 9], [10, 11, 12]])
xl_set(4, 1, 4, 3, [[1, 2, 3]])
xl_set(4, 1, 7, 1, [[1], [4], [7], [10]])
xl_set(4, 1, 4, 1, [[1]])
xl_set(4, 1, 3, [1, 2, 3])
xl_set(4, 1, 1)

4.6 xlc_set() Python function

Writes Python data to cells specified by row and column numbers starting from 0.

The elements of the Python data must be integers, floating-point numbers, or strings. Any other types are ignored.

This is the column-wise version of xl_set().

xlc_set(row1, column1, row2, column2, list (equivalent to a two-dimensional array)): Writes list data (equivalent to a two-dimensional array) to the cells in the specified rectangular area.

xlc_set(row1, column1, row2, list (equivalent to a one-dimensional array)): Writes list data (equivalent to a one-dimensional array) to the cells in the specified column area.

xlc_set(row1, column1, data): Writes a single value to the specified cell.

Any data that extends beyond the specified row1, column1, row2, and column2 area is ignored. Also, if the data is insufficient, no writing is performed for the corresponding cells.

See examples below.

xlc_set(4, 1, 7, 3, [[1, 4, 7, 10], [2, 5, 8, 11], [3, 6, 9, 12]])
xlc_set(4, 1, 7, 1, [[1, 4, 7, 10]])
xlc_set(4, 1, 4, 3, [[1], [2], [3]])
xlc_set(4, 1, 4, 1, [[1]])
xlc_set(4, 1, 7, [1, 4, 7, 10])
xlc_set(4, 1, 1)