vba 自定义function返回值_巧用VBA黑科技,让办公效率提升百倍

203f45b750b4173ce85e9fd3664c85bf.png

之前群里讨论过,为什么基础Excel函数教程之类的会成为月经首页热门,得到的结论是:基础用户比较多,看到了就想着说,以后说不定有用吧,点了收藏。

所以更高阶一点的东西,比如VBA,讨论结果是不会有什么热度,因为大部分人觉得说一辈子也用不着吧,就不会点收藏了。

这个论点是有道理的,因为我之前连载过罗技LUA脚本系列,看一下数据就知道了。

不过这次正好有征文,Visual Basic是其中一个选题。光写VB估计更不会有人看了,所以还是结合一下实际,写写一些简单的VBA吧。

一、开启Office当中的VBA

首先,VBA的全称是Visual Basic for Applications,本质上是嵌入于应用程序中,使用VB语法的脚本语言。

脚本语言的优势是保存即生效,不用编译。所以调整一些细节的时候还是很方便的。

不过,由于VB是微软自家的东西,给的权限远比LUA这种纯外部脚本大得多,所以安全问题一直是个困扰。事实上,VBS病毒一直都有。

所以想用得顺畅,有一些前置步骤是要做的,此处以Office 2007版本为例。

839c5d5d853836a15ec9e943ee6a5079.png

首先,出于安全考虑,OFFICE默认是不会把任何VBA的东西放出来的,连按钮都是。

我们先点击左上角,选择Excel选项。

5d7cb93b1c522c768c81717237f00f9a.png

来到自定义,右边找到开发工具选项卡,找到Visual Basic,拉到你想要的地方去。

72e314c4b353f99290487089485766df.png

如图,我直接拉到了左上角的快捷栏,点击这个按钮,就进入VBA的编辑界面。

8e0d4aa4d5bfdf83380abb8d33dc76e1.png

另外,默认的XLSX格式是不包含宏的,所以还要另存为XLSM格式。

3c4cf161add068044df711ed00754a39.png

另外,有时候我们会遇到脚本无论如何也没反应的情况,一般还是OFFICE的安全设置问题。

首先先到选项里,找到信任中心。

5cad0cec37a8cad9733b19499c790729.png

把你放含有脚本文件文档的目录添加进去,就可以了。

48a567d886f8bc1d8ddc1ce8ed24a314.png

另一种方式,直接在宏设置里,默认启用所有宏。

比较适合文件很多的朋友。

二、Excel妙用之高亮行与列

ed38f70bc4af4b8235d9396f98d3ca24.png

首先,我们通过之前添加的按钮进入VBA界面,默认会来到这么一个地方。

Sheet1、2、3是三张工作表,可以单独拥有独立的代码。

而最后的ThisWorkbook里的代码,则是整个文档共用的。

6d8196871602c061e63d8535a1a7011a.png

这里我们用全局通用的Workbook。

双击ThisWorkbook这条,默认会产生一个函数,这个函数是对象的默认函数Open,我们不需要,等下可以删除。

f6d8fb243a63c3bb9f936c14a7bb7ccd.png

这个例子用到的函数是SheetSelectionChange,顾名思义,当工作表选中区域改变时,触发此函数。

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Cells.FormatConditions.Delete

iColor = RGB(127, 127, 0)

With Target.EntireRow.FormatConditions

.Delete

.Add xlExpression, , "TRUE"

.Item(1).Interior.Color = iColor

End With

With Target.EntireColumn.FormatConditions

.Delete

.Add xlExpression, , "TRUE"

.Item(1).Interior.Color = iColor

End With

End Sub

代码如上。

效果如下。

c1a52c0485abb28b284d6a32eb78f983.gif

传上来底色好像有点问题,大家凑和哈~

颜色可以在 iColor = RGB(127, 127, 0) 这一句改。

这种效果只能用VBA做,很适合大量只读数据时避免眼花看错行的情况。

不过,有一点要注意的是:

VBA做的一切修改,都是无法撤销的,所以一定要慎重。就像代码里,修改了单元格的格式,如果没有额外写代码存储和恢复的话,就是无法还原的。

三、Excel妙用之自定义函数

之前的首页热门,推荐来推荐去就那几个函数,光vlookup函数我就见了几十次了。

不过也是,系统自带的函数,常用或好用的也就这些,再推荐能推荐出什么花来呢?

这里,我来教大家做自定义函数。

8188078fc9c994b0ba289cf674c13690.png

首先,自定义函数必须写在模块里,所以我们右击,插入,模块。

33b13f98ba49e0525d08a4323b187398.png

然后,我们写个简单的函数。

这个函数很简单,将引用单元格的值取出,作为文本,然后再串上“波导终结者”这个字符串,并返回。

VB函数的返回值赋值方法比较特别,其他大部分语言都是return XXX啥的,就VB是把函数本身作为一个变量去赋值。

总之,一个最简单的自定义函数就这样成了。

注意,函数前面要加上Public以方便外部调用。

0acc11dfb3fe3c6edda55e414ecd1b72.png

回到表中,打个等号,敲出自定义函数前面俩字母,可以看到,系统已经将自定义函数自动补完。

841220daa86e6e73d62c4f6cbbb1beab.png

输完回车,搞定。

可以看到,此单元格的内容,就是引用单元格的内容再串上波导终结者这个字样。

可能有的朋友会说,这函数也太简单了,来点花哨的吧?

但是,每个人的具体需求都各有不同,我只能教大家如何弄一个自定义函数,具体要做什么,肯定只能依照自己需求去写具体的代码了。

如果你有具体需求并且在编程上遇到问题,欢迎来打赏留言

四、将Excel打造成职场里的真·生产效率工具

如何才能真正的称作提高生产效率?

如果只是会用vlookup函数,或者会用CTRL+C代替右键菜单的复制,那这所谓的生产效率也太入门和没价值了。

事实上,很多时候,我们在职场上的生产效率,不仅取决于我们自己,也取决于协同岗位或者部门的效率。

举一个亲身经历,也是很多朋友会遇到的问题:程序和策划的协同。

以前我在网游公司写脚本,毫无疑问的要跟策划有非常多的沟通。很多时候,策划那边东西没定好,我们这边就没办法开做。

策划提供的东西如果太模糊,还得回头跟他重新确认。但是要太细的话,比如涉及到程序核心的一些数值,很多策划也懵。

044df60efb5dffb57ab54f622fb13eb3.png

就比如,现在要做一些新怪物,从程序的角度来讲,表里的大几十个字段各有各的用处。

从脚本的角度来讲,相关数值策划要是不给,我也不可能自己给你填。

从策划的角度来看,其实对他们有用的就几个:血蓝攻防外观等。

于是我就用VBA做了一个小工具。

首先,把所有字段列出来,默认值列出来,策划有用到的字段筛选出来。

e467d098452d95ab5c2e67daa1df3cbc.png

点击左上角的“生成怪物数据表”,此时就会把这些有用的字段筛选出来,生成一个EXCEL表。

策划只需要照着这个表里的数据填好数值就行了。

不过,如果只是策划填表,那策划自己也能做,这个生产效率并没有本质性的提高。

效率提高的部分,在于一键生成刷库SQL语句。

dde6dda2100009abaee6bd2909e7bbb5.png

点击生成Insert或者Update数据按钮之后,把刷库语句输出到文本文件里并自动打开,复制即可用。

原来的流程里,每个策划提供的数值格式各不同,当然,也不可能一键导入,于是每个案子,脚本都得把策划给的数值一个一个手动填,填完还得核对,然后再刷库试验……

使用了VBA之后,整个流程从策划设计数值,到脚本刷库成功的耗时,由原来的0.5至1天左右,减少至半小时(根据策划自己出数值的速度决定)。

脚本这边最麻烦的填数据步骤,耗时由3-5个小时缩短至2秒左右,数据的正确率为100%(除非策划自己填错)

后续如果数值有变动,甚至是服务器表结构变动(比如新增字段),只需要改一下EXCEL,重新点击按钮,耗时在半分钟以内。

e261b8b7be843d1a616067ccc392781b.png

处理这种有规律的、矩阵形式的数据,EXCEL非常擅长,但是光靠系统函数或者宏,最多只能做做排序筛选。

我花了2天的工作时间写这个脚本,应用之后,每天都可以给策划和脚本节省几百小时的工作时间,关键的是,准确率100%。

虽然我离职已久,不过这个脚本估计现在仍然在用吧。设计的时候就已经做成通用的,不同项目只要修改表字段、表名,即可通用。

由于代码应用太过具体,这里就象征性截张图上来。

关键的是,代码不能写死。比如项目最早是A游戏做,字段有50个,你如果写死50个,后面B项目的字段不一样,代码全部得重写。

这里一定要用循环以及内容判断,我不管你字段有几个,是什么内容,反正我就按照规则,把所有字段用循环筛选一遍即可。

aeacd110af95c34a23918fa12f737bef.png

在EXCEL里面放按钮也很简单。

选项里先把开发工具栏放出来,插入,底下就有按钮复选框等等控件。

点击设计模式,就能像在VB6里面一样设计,之后在按钮的点击事件里写代码即可。

五、在Word里统计字频

这里用一个比较普通的例子:在Word里统计字频。

如果你要统计一个字或者一个词,在一段WORD里面出现的次数,那么你网上搜方法,肯定都是告诉你查找替换法。

但如果你要把每个字都做字频统计,这种方法就行不通了。

使用代码,我们可以很轻松的做到。

7a028dfe9aff61ae1f376c0403ffdef9.png

这里由于是演示,我直接用Msgbox输出结果了。

代码效果就是,选中一段话,然后到VBA窗口里执行,这时候就会自动把所选中的这段文字,每个字出来的字频统计出来。

由于只是演示,我就直接做消息框弹出了,有需求的可以另行添加,比如扔EXCEL排序等等。

Sub bdzjz_tongji()

Dim i As Integer

Dim n As Integer

n = Selection.Characters.Count

Dim dict

Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To n

Dim s As String

s = Selection.Characters.Item(i)

If dict.Exists(s) Then

dict.Item(s) = dict.Item(s) + 1

Else

dict.Add s, 1

End If

Next i

Dim d_keys

d_keys = dict.keys

Dim d_items

d_items = dict.items

Dim sOut As String

For i = 0 To UBound(d_keys)

sOut = sOut & d_keys(i) & " 出现次数:" & d_items(i) & "次" & vbCrLf

Next

MsgBox sOut

End Sub

代码用到了字典类Dictionary,类似哈希表一样的结构。

将单字本身作为键值KEY,将字出现的次数存储和叠加,代码框架码好了之后就一劳永逸了。

六、在PPT里统计停留时间

以前在鞋厂做开发,曾经接到过运营的这么一个需求:

他们自己做了一个PPT,展示一些产品。现在,想在播放的时候,收集一下用户在具体某一页上停留的时间,以获得用户对产品的关注度。

PPT可以设置播放时各种延时效果,但是貌似没有统计的功能。

由于源文件找不到了,这里大概写一个原型。

a56ccf3e777108d132d4d936c94b9dac.png

Private Declare Function timeGetTime Lib "winmm.dll" () As Long

Dim dict

Sub OnSlideShowPageChange()

'记录当前页数

Dim iCut As Integer

iCut = SlideShowWindows(1).View.Slide.SlideIndex

Dim iCutTime As Long

iCutTime = timeGetTime()

'初始化字典

If IsNull(dict) Or (iCut = 1) Then

Set dict = CreateObject("Scripting.Dictionary")

End If

dict.Item(iCut) = iCutTime

If dict.Exists(iCut - 1) And (dict.Item(iCut - 1) > 0) Then

MsgBox "停留时间:" & (iCutTime - dict.Item(iCut - 1)) & "毫秒"

End If

End Sub

VBA自带的时间函数比较坑,只能精确到秒。而且是当天的秒,即晚上0点过后会重置,到时候还得判断是否跨天。

所以这里干脆用了系统API,timeGetTime获得的是开机到现在经过的毫秒数。

然后将PPT设置为手动播放,此时程序会自动记录每一次切换幻灯片时的时间,并且和上一张的时间相减,得出用户在上一张幻灯片里停留的时间。

ede59a8f5c2d9913b49eda11e73efb12.png

由于是原型,这里就简单的减去序号上一张的时间,一般人播放就是滚轮或者鼠标一张一张点。如果是跳着播放的,比如从第1张直接跳到第3张等特殊情况,代码需要改动。

如图,我从第2张跳到第3张时,得到第二张幻灯片的停留时间是5124毫秒。

得到数据后要怎么用,那就看具体需求了。

事实上,VBA甚至可以直接连接数据库,不过写了估计没人看也看不懂,就先略过了。

七、总结与提醒

可能有的朋友听说过宏,宏与脚本的区别是什么呢?

一个宏,可以用一行或者一段脚本来实现。事实上,微软提供的“录制宏”功能,就是把操作录制成一段代码,然后作为宏来调用。

但是反过来,代码能做的事情多了去了,熟练运用了之后,比宏要强上百倍。

在应用性方面,Excel肯定是最常用的,放些文本框下拉框按钮啥的,甚至可以当成简易的程序来用。之前就帮别人做过简单的出题答题系统,太复杂应该没人看,就不提了。

Word其次,由于排版上的问题,控件不好放。而且由于不涉及太多运算,大多数功能通过软件原生就能实现。

PPT最次,需要强调的是,PPT运行时虽然能触发VBA脚本,但是却无法调试。PPT前台播放,和后台的脚本是多线程异步的,没有出错信息,也断不了断点。而且PPT支持的事件也比较少。

支持VBA的,也不一定只有微软自家的Office,像鼎鼎有名的AutoCAD也支持。

不过,由于是自家的东西,微软给VBA开放了太多的权限,VBA、VBS病毒在历史上还是有一定地位的。大家如果网上找代码啥的,一定要多加小心。

一些平日经常用的,软件却又没办法简单实现的功能,都可以考虑用VBA来实现。涉及多人、多个部门的工作,如果能达成共识统一好格式,一个脚本可以顶好几天的工作量,还不会错。以前项目里做过从策划,设计,关卡,脚本,资源,测试一条龙的VBA,涉及过深这里就不细谈了。

当然,VBA编程需要对VB语法有基础,并且不同软件的事件、属性都不同,查资料是难免的,肯定不像一些基础操作和函数一样人人都能用。但是如果能用好,那才真的是效率飞升了。

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
目录 '1.函数作用:返回 Column 英文字........................9 '2.函数作用:查询某一值第num次出现的值................9 '3.函数作用:返回当个人工资薪金所得为2000元(起征点为850元)时的应纳个人所得税税额.............................10 '4.函数作用:从形如"123545ABCDE"的字符串中取出数字....11 '5.函数作用:从形如"ABCD12455EDF"的字符串中取出数字...11 '6.函数作用:按SplitType取得RangeName串值中的起始位置12 '7.函数作用:将金额数字转成中文大写....................13 '8.函数作用:计算某种税金..............................18 '9.函数作用:人民币大、小写转换........................19 '10.函数作用:查汉字区位码.............................20 '11.函数作用:把公元年转为农历.........................21 '12.函数作用:返回指定列数的列标.......................42 '13.函数作用:用指定字符替换某字符.....................43 '14.函数作用:从右边开始查找指定字符在字符串中的位置...43 '15.函数作用:从右边开始查找指定字符在字符串中的位置...44 '16.函数作用:计算工龄.................................44 '17.函数作用:计算日期差除去星期六、星期日...........45 '18.函数作用:将英文字反转的自定函数...................46 '19.函数作用:计算个人所得税...........................46 '20.函数作用:一个能计算是否有重复单元的函数...........47 '21.数字金额转中文大写................................48 '22.函数作用:将数字转成英文...........................49 '23.函数作用:人民币大小写转换.........................52 '24.函数作用:获取区域颜色值...........................53 '25.函数作用:获取活动工作表名.........................53 '26.函数作用:获取最后一行行数.........................54 '27.函数作用:判断是否连接在线.........................54 '28.函数作用:币种转换.................................54 '29.函数作用:检验工作表是否有可打印内容...............55 '30.函数作用:查找一字符串(withinstr)在另一字符串中(findstr1)中某一次(startnum)出现时的位置返回零表示没找到。..................................................57 '31.函数作用:增加文件路径最后的“\”符号..............58 '32.函数作用:计算所得税...............................58 '33.函数作用:从工作表第一行的标题文字以数字形式返回所在列号..................................................58 '34.函数作用:在多个工作表中查找一个范围内符合某个指定条件的项目对应指定范围加总求和..........................59 '35.函数作用:返回 Column 英文字.......................60 '36.函数作用:查找指定列名的列数.......................60 '37.函数作用:文字格式的时间(分:秒)转化为数字格式(秒)..61 '38.函数作用:将"hh:mm:ss"格式的时分秒数转换成秒数.....62 '39.函数作用:金额中文大写转数字.......................62 '40.函数作用:把角度转为度秒分、弧度等显示.............63 '41.函数作用:身份证号码侦测...........................64 '42.函数作用:显示公式............................
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页