ThinkChat🤖让你学习和工作更高效,注册即送10W Token,即刻开启你的AI之旅 广告
# VBA: 用户自定义函数 (UDFs) 本教程将帮助您快速了解如何编写用户自定义函数。 >[info]注意 ·UDF目前仅在Windows上可用。 ·有关如何控制参数行为和返回值的详细信息,请查看[转换器和选项](converters.md)。 ·有关可用装饰器及其选项的全面概述,请查看相应的API文档:[api](api.md)。 ## 一次性Excel准备 1) 在`文件>选项>信任中心>信任中心设置>宏设置`下启用`信任访问VBA项目对象模型` 2. 通过命令提示符安装加载项:`xlwings addin install`(参见[加载项](addin.md))。 ## 工作簿准备 启动新项目的最简单方法是在命令提示符下运行`xlwings quickstart myproject`(请参阅[命令行客户端](command_line.md))。 这会自动将xlwings引用添加到生成的工作簿。 ## 一个简单的UDF 默认的插件设置需要一个Python源文件,其方式与`quickstart`一样: * 在与Excel文件相同的目录中 * 与Excel文件同名,但使用`.py`结尾而不是`.xlsm`。 或者,您可以通过xlwings功能区中的`UDF Modules`指向特定模块。 假设您有一个工作簿`myproject.xlsm`,那么您可以在`myproject.py`中编写以下代码: ~~~ import xlwings as xw @xw.func def double_sum(x, y): """返回两个参数之和的两倍""" return 2 * (x + y) ~~~ * 现在单击xlwings选项卡中的`Import Python UDFs`以获取对`myproject.py`所做的更改。 * 在单元格中输入公式`= double_sum(1,2)`,您将看到正确的结果: ![](https://i.vgy.me/WWFx3y.png) * 文档字符串(三引号)将在Excel中显示为函数描述。 >[info]注意 ·如果更改函数参数或函数名称,则只需重新导入函数。 ·自动拾取实际函数中的代码更改(即,在下一次计算公式时,例如,由`Ctrl-Alt-F9`触发),但导入的模块中的更改不会。 这是Python导入工作方式的行为。 如果要确保所有内容都处于新鲜状态,请单击`Restart UDF Server`。 ·当函数导入Excel时,`@xw.func`装饰器仅由xlwings使用。 它告诉xlwings它应该创建一个VBA包装函数的函数,否则它对函数在Python中的行为没有影响。 ## 数组公式:提高效率 在Excel中调用一个大数组公式比调用许多单元格公式更有效,因此使用它们通常是个好主意,特别是如果遇到性能问题。 您可以将Excel范围作为函数参数传递,而不是单个单元格,它将以列表的形式显示在Python中。 例如,可以编写以下函数,将1添加到Range内的每个单元格: ~~~ @xw.func def add_one(data): return [[cell + 1 for cell in row] for row in data] ~~~ 要在Excel中使用此公式, * 再次单击`Import Python UDFs` * 填写`A1:B2`范围内的值 * 选择范围`D1:E2` * 输入公式`=add_one(A1:B2)` * 按`Ctrl + Shift + Enter`创建一个数组公式。 如果您正确地完成了所有操作,您将看到括号括起来的公式,如此屏幕截图所示: ![](https://i.vgy.me/U9c8sf.png) ### 数组维数:ndim 上面的公式存在它期望“二维”输入的问题,例如 一个`[[1, 2], [3, 4]]`形式的嵌套列表。 因此,如果将公式应用于单个单元格,则会出现以下错误:`TypeError: 'float' object is not iterable`('float'对象不可迭代)。 要强制Excel始终为您提供二维数组,无论参数是单个单元格,列/行还是二维范围,您都可以像这样扩展上面的公式: ~~~ @xw.func @xw.arg('data', ndim=2) def add_one(data): return [[cell + 1 for cell in row] for row in data] ~~~ ## 使用NumPy和Pandas的数组公式 通常,您需要在UDF中使用NumPy数组或Pandas DataFrame,因为这可以释放Python用于科学计算的生态系统的全部功能。 要使用numpy数组定义矩阵乘法的公式,您可以定义以下函数: ~~~ 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 ~~~ >[info]注意 如果你没有使用NumPy> = 1.10的Python> = 3.5,请使用`x.dot(y)`而不是`x @ y`。 一个很好的例子,说明如何让Pandas工作就是创建一个基于数组的`CORREL`公式。 Excel的`CORREL`版本仅适用于2个数据集,如果您想快速获取几个时间序列的相关矩阵,则使用起来很麻烦。 Pandas基于数组创建了一个基于数组的`CORREL2`公式: ~~~ import xlwings as xw import pandas as pd @xw.func @xw.arg('x', pd.DataFrame, index=False, header=False) @xw.ret(index=False, header=False) def CORREL2(x): """与CORREL类似,但作为2个以上数据集的数组公式""" return x.corr() ~~~ ## @xw.arg 和@xw.ret 修饰 这些修饰符对UDF的作用就像`options`方法对`Range`对象的作用一样:它们允许您将转换器及其选项应用于函数参数(`@xw.arg`)和返回值(`@xw.ret`)。例如,要将参数`x`转换为pandas DataFrame并在返回时抑制索引,请执行以下操作: ~~~ @xw.func @xw.arg('x', pd.DataFrame) @xw.ret(index=False) def myfunction(x): # x is a DataFrame, do something with it return x ~~~ 有关详细信息,请参阅[转换器和选项](converters.md)。 ## 动态数组公式 >[info]注意 如果您的Excel版本支持新的原生动态数组,那么您不必执行任何特殊操作,也不应使用`expand`装饰器! 要检查您的Excel版本是否支持它,请查看是否有`= UNIQUE()`公式。 原生动态数组在2018年9月底在Office 365 Insider Fast中引入。 如上图所示,要使用Excel的数组公式,您需要先选择结果数组,然后输入公式,最后单击`Ctrl-Shift-Enter`来指定其前面的维度。实际上,这通常是一个很麻烦的过程,尤其是在处理动态数组(如时间序列数据)时。自v0.10以来,XLwings提供动态UDF扩展: 这是一个演示UDF扩展的语法和效果的简单示例: ~~~ import numpy as np @xw.func @xw.ret(expand='table') def dynamic_array(r, c): return np.random.randn(int(r), int(c)) ~~~ ![](https://i.vgy.me/hWJeXt.png) ![](https://i.vgy.me/z87ERs.png) >[info]Note ·扩展数组公式将在不提示的情况下覆盖单元格 ·Pre v0.15.0不允许将volatile函数作为参数,例如 你不能使用像`= TODAY()`这样的函数作为参数。 从v0.15.0开始,您可以使用volatile函数作为输入,但UDF将被调用超过1次。 ·动态数组已使用v0.15.0进行了重构,以便成为正确的旧数组:要编辑xlwings大于等于v0.15.0的动态数组,需要在左上角单元格中单击`ctrl-shift-enter`。请注意,当您第一次输入公式时,不必这样做。 ## Docstring文档字符串 下面的示例演示如何为函数和参数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) ~~~ ## “vba”关键字 获取调用单元格的地址通常是有帮助的。现在,最简单的方法之一就是使用`vba`关键字。实际上,`vba`允许您访问任何可用的`vba`表达式,例如`application`。但是,请注意,当前您正直接处理pywin32 com对象: ~~~ @xw.func @xw.arg('xl_app', vba='Application') def get_caller_address(xl_app): return xl_app.Caller.Address ~~~ ## 宏 在Windows上,作为通过[VBA: RunPython](vba.md)调用宏的替代方法,您还可以使用`@xw.sub`装饰器: ~~~ import xlwings as xw @xw.sub def my_macro(): """将工作簿的名称写入工作表1的(A1)Range""" wb = xw.Book.caller() wb.sheets[0].range('A1').value = wb.name ~~~ 单击`Import Python UDFs`后,您可以通过`Alt + F8`执行此宏或通过绑定它来使用此宏。 到一个按钮。 对于后者,请确保在`文件>选项>自定义功能区`下选择`开发工具`选项卡。 然后,在`开发工具`选项卡下,您可以通过`插入>表单控件`插入一个按钮。 绘制按钮后,系统将提示您为其指定一个宏,您可以选择“my_macro”。 ## 从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 新版本v0.14.0。 xlwings提供了一种在Excel中编写异步函数的简便方法。 异步函数立即返回`#N / A waiting ...`。 当函数正在等待其返回值时,您可以使用Excel执行其他操作,并且只要返回值可用,就会更新单元格值。 唯一可用的模式是`async_mode ='threading'`,这意味着它对I/O绑定任务很有用,例如当您通过Web从API获取数据时。 只需在函数装饰器中赋予它相应的参数,就可以使函数异步。 在此示例中,使用`time.sleep`模拟耗时的I/O绑定任务: ~~~ import xlwings as xw import time @xw.func(async_mode='threading') def myfunction(a): time.sleep(5) # long running tasks return a ~~~ 您可以像使用任何其他xlwings函数一样使用此函数,只需将`=myfunction("abcd")`放入一个单元格(在导入该函数后,将其关闭)。 请注意,xlwings不使用Excel 2010中引入的原生异步函数,因此任何版本的Excel都支持xlwings异步函数。