基于费曼学习法,来写一篇帖子形式的笔记。附上思路的来源:xlwings添加extension(using UDFs without xlsm)
逻辑是xlwings的扩展sql函数只要安装了xlwing模块并加载了xlwings.xlsm后,不管是在什么格式的Excel文档中,都可以如同是Excel自带的函数一样来使用。基于此函数可以修改为自己的UDF函数
xlwings.xlsm中的sql代码
Function sql(query, ParamArray tables())
If TypeOf Application.Caller Is Range Then On Error GoTo failed
ReDim argsArray(1 To UBound(tables) - LBound(tables) + 2)
argsArray(1) = query
For K = LBound(tables) To UBound(tables)
argsArray(2 + K - LBound(tables)) = tables(K)
Next K
If has_dynamic_array() Then
sql = Py.CallUDF("xlwings.ext", "sql_dynamic", argsArray, ActiveWorkbook, Application.Caller)
Else
sql = Py.CallUDF("xlwings.ext", "sql", argsArray, ActiveWorkbook, Application.Caller)
End If
Exit Function
failed:
sql = Err.Description
End Function
取不来名字ne 的实例代码
Function findnear(value, target, object, Optional default, Optional cutoff)
If TypeOf Application.Caller Is Range Then On Error GoTo failed
findnear = Py.CallUDF("xlwings.ext", "findnear", Array(value, target, object, default, cutoff), ActiveWorkbook, Application.Caller)
Exit Function
failed:
findnear = Err.Description
End Function
为了测试运行过程,我写的test.py
import xlwings as xw
@xw.func
def findnear(a,b):
c=a+b
return c*2
在__init__.py中导入
from .sql import sql, sql_dynamic
from .test import findnear
xlwings.xlsm中的findnear代码
Function findnear(a, b)
If TypeOf Application.Caller Is Range Then On Error GoTo failed
findnear = Py.CallUDF("xlwings.ext", "findnear", Array(a, b), ActiveWorkbook, Application.Caller)
Exit Function
failed:
findnear = Err.Description
End Function
结果
总结:
经过测试可以发现最关键的就是"Py.CallUDF()"这个方法起了作用,测试参数为:
Py.CallUDF(模块名,函数名,参数,目标工作簿,Application.Caller)
VBA中的函数名需要跟python中的函数名一致
流程
1.下载并安装xlwings
2.编辑自定义函数,在函数前使用"@xw.func"修饰符
3.在Excel中设置加载项"xlwings.xlsm"
4.使用xlwings.xlsm的Py.CallUDF()方法引用函数,储存在加载项中,下次直接使用就行了
此帖多为个人主观推测,用于方便个人理解,误以此帖逻辑作为客观解释~