ユーザー定義関数 (UDFs)

このチュートリアルではユーザー定義関数(User Defined Functions, UDFs)の作成方法を学びます。

注釈

  • UDFが使えるのは、現在Windowsのみです。

  • For details of how to control the behaviour of the arguments and return values, have a look at Converters and Options.

  • For a comprehensive overview of the available decorators and their options, check out the corresponding API docs: UDF decorators.

Excelの準備（1回だけ）

  1. Enable Trust access to the VBA project object model under File > Options > Trust Center > Trust Center Settings > Macro Settings. You only need to do this once. Also, this is only required for importing the functions, i.e. end users won't need to bother about this.

  2. Install the add-in via command prompt: xlwings addin install (see Add-in & Settings).

ワークブックの準備

The easiest way to start a new project is to run xlwings quickstart myproject on a command prompt (see Command Line Client (CLI)). This automatically adds the xlwings reference to the generated workbook.

シンプルなUDF

The default add-in settings expect a Python source file in the way it is created by quickstart:

  • Excel ファイルと同じディレクトリ

  • with the same name as the Excel file, but with a .py ending instead of .xlsm.

Alternatively, you can point to a specific module via UDF Modules in the xlwings ribbon.

  • The Image below shows the correct input for the "UDF Modules" field in the xlwings ribbon with a module called "my_udf.py":

    _images/udf_modules.png

  • If the module is not within the same directory as the Excel file, you point to it via the "PYTHONPATH" field. The image below shows the configuration if the module was in the folder "C:\py_folder" (just an example so it fits in the field window):

    _images/pythonpath.png

  • For reference, this is how your xlwings.conf file would look like with these settings:

    _images/pythonpath_conf.png

Let's assume you have a Workbook myproject.xlsm, then you would write the following code in myproject.py:

import xlwings as xw

@xw.func
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

  • Now click on Import Python UDFs in the xlwings tab to pick up the changes made to myproject.py.

  • Enter the formula =double_sum(1, 2) into a cell and you will see the correct result:

    _images/double_sum.png

  • （トリプル クォートで囲われた）docstringは、Excelでは関数の説明に表示されます。

注釈

  • 再度インポートする必要があるのは、関数の引数や名前を変えた時のみです。

  • Code changes in the actual functions are picked up automatically (i.e. at the next calculation of the formula, e.g. triggered by Ctrl-Alt-F9), but changes in imported modules are not. This is the very behaviour of how Python imports work. If you want to make sure everything is in a fresh state, click Restart UDF Server.

  • The @xw.func decorator is only used by xlwings when the function is being imported into Excel. It tells xlwings for which functions it should create a VBA wrapper function, otherwise it has no effect on how the functions behave in Python.

配列数式: 効率的に計算する

多数のセルから個別に数式を呼び出すよりも、1つの大きな配列数式を呼び出す方が効率的です。一般的に配列数式の使用は良い方法であり、パフォーマンスに問題があるケースでは特に効果があります。

単独セル以外にも、ExcelのRangeを関数の引数にできます。その際、RangeはPythonではリストのリストになります。

For example, you can write the following function to add 1 to every cell in a Range:

@xw.func
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

この数式を使うには

  • Click on Import Python UDFs again

  • Fill in the values in the range A1:B2

  • Select the range D1:E2

  • Type in the formula =add_one(A1:B2)

  • Press Ctrl+Shift+Enter to create an array formula. If you did everything correctly, you'll see the formula surrounded by curly braces as in this screenshot:

    _images/array_formula.png

配列の次元数: ndim

The above formula has the issue that it expects a "two dimensional" input, e.g. a nested list of the form [[1, 2], [3, 4]]. Therefore, if you would apply the formula to a single cell, you would get the following error: TypeError: 'float' object is not iterable.

To force Excel to always give you a two-dimensional array, no matter whether the argument is a single cell, a column/row or a two-dimensional Range, you can extend the above formula like this:

@xw.func
@xw.arg('data', ndim=2)
def add_one(data):
    return [[cell + 1 for cell in row] for row in data]

Using type hints, the same can be written like this:

from typing import Annotated

@xw.func
def add_one(data: Annotated[list[list[float], {"ndim": 2}]]):
    return [[cell + 1 for cell in row] for row in data]

If you want to reuse that type hint for other functions, you can simplify things like this:

from typing import Annotated

List2d = Annotated[list[list[float], {"ndim": 2}]]

@xw.func
def add_one(data: List2d):
    return [[cell + 1 for cell in row] for row in data]

NumpyおよびPandasでの配列数式

科学計算分野のPythonのエコシステムを最大限に活用するために、Numpyの配列やPandasのDataFrameをUDFで使用する方法を紹介します。

To define a formula for matrix multiplication using numpy arrays, you would define the following function:

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('x', np.array, ndim=2)
@xw.arg('y', np.array, ndim=2)
def matrix_mult(x, y):
    return x @ y

and again the same with type hints:

from typing import Annotated
import xlwings as xw
import numpy as np

Array2d = Annotated[np.ndarray, {"ndim": 2}]

@xw.func
def matrix_mult(x: Array2d, y: Array2d):
    return x @ y

注釈

If you are not on Python >= 3.5 with NumPy >= 1.10, use x.dot(y) instead of x @ y.

A great example of how you can put Pandas at work is the creation of an array-based CORREL formula. Excel's version of CORREL only works on 2 datasets and is cumbersome to use if you want to quickly get the correlation matrix of a few time-series, for example. Pandas makes the creation of an array-based CORREL2 formula basically a one-liner:

import xlwings as xw
import pandas as pd

@xw.func
@xw.arg('df', pd.DataFrame, index=False, header=False)
@xw.ret(index=False, header=False)
def CORREL2(df):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return df.corr()

and the same again with type hints:

from typing import Annotated
import xlwings as xw
import pandas as pd

@xw.func
def CORREL2(df: Annotated[pd.DataFrame, {"index": False, "header": False}]):
    """Like CORREL, but as array formula for more than 2 data sets"""
    return df.corr()

デコレーター@xw.argと@xw.ret

These decorators are to UDFs what the options method is to Range objects: they allow you to apply converters and their options to function arguments (@xw.arg) and to the return value (@xw.ret). For example, to convert the argument x into a pandas DataFrame and suppress the index when returning it, you would do the following:

@xw.func
@xw.arg("df", pd.DataFrame)
@xw.ret(index=False)
def myfunction(df):
    # df is a DataFrame, do something with it
    return df

For further details see the Converters and Options documentation.

Using type hints instead of decorators

Added in version 0.32.0.

Since v0.32.0, xlwings has supported type hints that you can use instead of or in combination with decorators:

import xlwings as xw
import pandas as pd

@xw.func
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
     # df is a DataFrame, do something with it
    return df

In this example, the return type (-> pd.DataFrame) is optional, as xlwings automatically checks the type of the returned object.

If you need to provide additional conversion arguments, you can either provide them via an annotated type hint or via a decorator. Note that when you use type hints and decorators together, decorators override type hints for conversion.

To set index=False for both the argument and the return value, you can annotate the type hint like this:

from typing import Annotated
import xlwings as xw
import pandas as pd

@xw.func
def myfunction(
    df: Annotated[pd.DataFrame, {"index": False}]
) -> Annotated[pd.DataFrame, {"index": False}]:
    # df is a DataFrame, do something with it
    return df

As this might be a little harder to read, you can extract the type definition, which also allows you to reuse it like so:

from typing import Annotated
import xlwings as xw
import pandas as pd

Df = Annotated[pd.DataFrame, {"index": False}]

@xw.func
def myfunction(df: Df) -> Df:
    # df is a DataFrame, do something with it
    return df

Alternatively, you could also combine type hints with decorators:

from typing import Annotated
import xlwings as xw
import pandas as pd

@xw.func
@xw.arg("df", index=False)
@xw.ret(index=False)
def myfunction(df: pd.DataFrame) -> pd.DataFrame:
    # df is a DataFrame, do something with it
    return df

Legacy Dynamic Arrays

注釈

If your version of Excel supports the new native dynamic arrays, then you don't have to do anything special, and you shouldn't use the expand decorator! To check if your version of Excel supports it, see if you have the =UNIQUE() formula available. Native dynamic arrays were first introduced at the end of 2018.

As seen above, to use Excel's array formulas, you need to specify their dimensions up front by selecting the result array first, then entering the formula and finally hitting Ctrl-Shift-Enter. In practice, it often turns out to be a cumbersome process, especially when working with dynamic arrays such as time series data. Since v0.10, xlwings offers dynamic UDF expansion:

単純な例でシンタックスとUDF拡張の効果を説明してみます。以下をご覧ください。

import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(r, c):
    return np.random.randn(int(r), int(c))

and the same with type hints:

from typing import Annotated
import numpy as np

@xw.func
def dynamic_array(r: int, c: int) -> Annotated[np.ndarray, {"expand": "table"}]:
    return np.random.randn(int(r), int(c))
_images/dynamic_array1.png _images/dynamic_array2.png

注釈

  • 配列数式の拡張はプロンプトせずにセルを上書きします。

  • Pre v0.15.0 doesn't allow to have volatile functions as arguments, e.g. you cannot use functions like =TODAY() as arguments. Starting with v0.15.0, you can use volatile functions as input, but the UDF will be called more than 1x.

  • Dynamic Arrays have been refactored with v0.15.0 to be proper legacy arrays: To edit a dynamic array with xlwings >= v0.15.0, you need to hit Ctrl-Shift-Enter while in the top left cell. Note that you don't have to do that when you enter the formula for the first time.

Docstrings

関数および引数x、yのdocstringを書いて、Excelの関数ウィザードに表示させる方法を以下の例で示します。

import xlwings as xw

@xw.func
@xw.arg('x', doc='This is x.')
@xw.arg('y', doc='This is y.')
def double_sum(x, y):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

And the same with type hints:

from typing import Annotated
import xlwings as xw

@xw.func
def double_sum(
    x: Annotated[float, {"doc": "This is x."}],
    y: Annotated[float, {"doc": "This is y."}],
):
    """Returns twice the sum of the two arguments"""
    return 2 * (x + y)

"caller" 引数

You often need to know which cell called the UDF. For this, xlwings offers the reserved argument caller which returns the calling cell as xlwings range object:

@xw.func
def get_caller_address(caller):
    # caller will not be exposed in Excel, so use it like so:
    # =get_caller_address()
    return caller.address

Note that caller will not be exposed in Excel but will be provided by xlwings behind the scenes.

"vba" キーワード

By using the vba keyword, you can get access to any Excel VBA object in the form of a pywin32 object. For example, if you wanted to pass the sheet object in the form of its CodeName, you can do it as follows:

@xw.func
@xw.arg('sheet1', vba='Sheet1')
def get_name(sheet1):
    # call this function in Excel with:
    # =get_name()
    return sheet1.Name

Note that vba arguments are not exposed in the UDF but automatically provided by xlwings.

マクロ

On Windows, as an alternative to calling macros via RunPython, you can also use the @xw.script decorator:

import xlwings as xw

@xw.script
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name

After clicking on Import Python UDFs, you can then use this macro by executing it via Alt + F8 or by binding it e.g. to a button. To do the latter, make sure you have the Developer tab selected under File > Options > Customize Ribbon. Then, under the Developer tab, you can insert a button via Insert > Form Controls. After drawing the button, you will be prompted to assign a macro to it and you can select my_macro.

Note: previously, xw.script was called xw.sub, which is now deprecated.

VBAからUDFを呼び出す

インポートした関数はVBAからも利用できます。2次元配列を返す関数の例を以下に示します:

Sub MySub()

Dim arr() As Variant
Dim i As Long, j As Long

    arr = my_imported_function(...)

    For j = LBound(arr, 2) To UBound(arr, 2)
        For i = LBound(arr, 1) To UBound(arr, 1)
            Debug.Print "(" & i & "," & j & ")", arr(i, j)
        Next i
    Next j

End Sub

非同期UDF

注釈

これは実験的な機能です

Added in version v0.14.0.

xlwings offers an easy way to write asynchronous functions in Excel. Asynchronous functions return immediately with #N/A waiting.... While the function is waiting for its return value, you can use Excel to do other stuff and whenever the return value is available, the cell value will be updated.

The only available mode is currently async_mode='threading', meaning that it's useful for I/O-bound tasks, for example when you fetch data from an API over the web.

You make a function asynchronous simply by giving it the respective argument in the function decorator. In this example, the time consuming I/O-bound task is simulated by using time.sleep:

import xlwings as xw
import time

@xw.func(async_mode='threading')
def myfunction(a):
    time.sleep(5)  # long running tasks
    return a

You can use this function like any other xlwings function, simply by putting =myfunction("abcd") into a cell (after you have imported the function, of course).

なお、xlwingsはExcel 2010で導入されたネイティブな非同期関数を使用していないので、xlwingsの非同期関数は全バージョンのExcelで使えます。