ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 4587|回复: 29

[分享] vba里使用python自定义函数。

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2018-12-21 20:16 | 显示全部楼层 |阅读模式
本帖最后由 fxl447098457 于 2018-12-22 10:41 编辑

      今天讲一下怎么在vba里面使用python的自定义函数。在vba板块提下python主要是python的包很多。对于我们来说,直接拿轮子过来用用肯定是很好的事情,复杂的原理,背后的算法与我无关。通过沟通python和excel主要是通过一个名为xlwings的加载项作为连接的纽带,从而实现互通。下面主要写以下几点:
1.如何搭建excel和python交互的环境
      python软件是不可或缺的。我们需要使用python的包和解释器。你可以选择原生的python或者anaconda(带了大量的科学计算的包和软件)或者minconda(anconda的精简版,很多包需要自己安装)。我使用的是miniconda配上python3.6.7的环境(安装路径:F:\ProgramData\Miniconda3,这个你自便,我是方便后面引用.),对于生手我建议直接安装anaconda或者miniconda,安装管理少了很多折腾.安装完python以后我们需要安装xlwings包。下面的教程我都以anaconda/miniconda来解说了。
找到开始菜单里面anaconda/minconda 的快捷方式Anaconda Prompt命令提示符打开,使用pip install xlwings或者conda install --y xlwings。如下图我使用conda,由于我安装过了,所以提示already installed.其他需要安装的包依旧是这么安装的。
TIM截图20181221191023.png


安装完以后我们需要安装xlwings 加载项。这个是加载到excel里面的。使用xlwings addin install命令即可以完成安装。
6.png

然后我们重新打开excel文件就可以发现excel里面已经加载成功了。如:
5.png
    然后我们需要配置一下解释器路径了。就是上图的interpreter方框和pythonpath方框。第一个填的是python.exe/pythonw.exe的路径。我用的是pythonw.exe,它在运行时候不会出现窗口。带上完整的安装路径。例如我的interpreter方框里面填写的是:F:\ProgramData\Miniconda3\envs\python36\pythonw.exe。然后pythonpath是要运行的py代码文件所在的文件夹路径。你使用哪个文件夹集中管理存放你的python代码文件,就填写文件夹所在路径.我这里是用了F:\Desktop作为python代码文件存放的地方。然后我们再看udf modules哪个方框。那里面填写的是此工作簿要调用的python代码文件名称,不需要带上后缀名。

下面我们来简单测试一个加载项是否能用。xlwings加载项扩展了一个sql函数。这个函数我们可以直接用。就像excel里面的函数一样。sql这个函数第一个参数是sql语句。第二个不固定的参数,代表sql语句使用的表的范围。
如下图我要查询“原始表格”工作表里产品名称为“气球”的所有数据放到“需求末班”工作表里:

2.png

如下图:我在“需求末班”工作表里的A18单元格输入=sql("select * from a where 产品名称='气球'",原始表格!A2:J33)并按下enter键后,结果自动生成了数组公式,并得到我要的查询结果。sql语句中的a为第一个参数范围表的别名,自动对应的。如果你在sql语句中使用多个别名,那么后面的需要多个参数表一一对应sql语句的。看下第二图里面的双表查询,应该可以理解了吧。到了这里运行ok的话也说明我们的python和xlwings加载项都成功安装了。该进入下一个环节了。

11.png

sql.png

评分

参与人数 14鲜花 +27 技术 +1 收起 理由
c3a139 + 2 优秀作品
hehex + 2 优秀作品
ww87725244 + 2 值得肯定
angelashark + 2 优秀作品
wanghmalan + 1 优秀作品

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-12-21 20:19 | 显示全部楼层
本帖最后由 fxl447098457 于 2018-12-21 20:53 编辑

2.如何使用有返回值python自定义函数

按照vba的规则来讲,我们有返回值的算作function,没有返回值的算作sub.
对于python来讲有返回值就是需要return 计算结果。下面我来自定义一个python函数。pythonpath里面填写的文件及路径下新建一个txt并打开,然后复制下面的代码进去:
import xlwings as xw
import os
@xw.func
def cwf():
    return os.getcwd()
      上面这段代码是定义了一个名字叫做cwf的函数,目的是获取当前文件夹的路径。def是函数标记,就像vba里面的自定义函数都带着一个function一样。所有在vba里调用返回值的函数需要加入@xw.func来修饰.这里我就不提什么修饰符的概念了。用vba里面来讲就是表明这是一个函数,因为有返回值。

然后将文件另存为code.py.还是保存在pythonpath填写的路径名下。下面我们来在单元格里试试这个函数。首先在udf module方框里输入code,表明我们要调用的文件名。现在知道pythonpath的用处了吧,用于在pythonpath目录下找到名字叫code的py文件。然后点下图箭头所指的fx(import functions)图标。我们就可以在excel里使用这个code文件里cwf函数了。
TIM截图20181221204005.png

1.单元格里直接使用函数,看下图结果
5.png
2。在vba里面调用函数

2018-12-21_204823.jpg

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-12-21 20:20 | 显示全部楼层
本帖最后由 fxl447098457 于 2018-12-21 21:59 编辑

3.如何使用无返回值的python函数      

      按照vba的规则来讲,没有返回值的就是sub过程。如第二节自定义函数前面加入@xw.func修饰。无返回值的自定义函数就需要加入@xw.sub来修饰。下面我来写一个简单的生成二维码的python自定义函数。

       因为要调用生成二维码的qrcode包,我们可以在anaconda Prompt 命令行里输入 pip install qrcode 安装好。我们可以新建一个txt依照二楼的步骤创建一个py文件。也可以继续在二楼的code.py文件里面写自定义函数。那我就继续在刚才的code文件里面写代码.如下图:
937.png

保存code文件的同时别忘了点击加载项上的import functions图标以便将修改变化反馈到xlwings addin。qr这个自定义函数,就是创建二维码并以数据作为保存的图片名。没有返回值的过程我们vba里需要call.现在调用python里面的过程我们就需要runpython.我们需要在vba编辑器下勾选工具---引用--xlwings.
39.png
下面写调用生成二维码函数的vba代码。运行代码后达到目的。:

Sub 生成二维码()
runpython ("import code;code.qr('ExcelHome')")
End Sub

222.png
好了,基本的东西就讲到这里了。希望大家能有所收获吧。

TA的精华主题

TA的得分主题

发表于 2018-12-21 21:12 来自手机 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-12-21 22:03 | 显示全部楼层
万一微软真内置了py,excel可以直接调用py的各种库......那安装文件就要10G+了.......

TA的精华主题

TA的得分主题

发表于 2018-12-21 23:06 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-12-21 23:44 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-12-21 23:45 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-12-22 08:33 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2018-12-23 09:45 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-10-24 01:54 , Processed in 1.095529 second(s), 29 queries , Gzip On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表