vba对象常用属性和方法(用VBA模块计算课时费)
这篇技术文章我们讲一下:怎么用VBA模块计算一些稍微复杂的表。
(今天发的的几个短视频段子,播放量团灭,很难过。还是讲技术吧)
用AccessVBA模块来对表进行计算,相当于用Excel的函数来计算,二者在本质上没有区别。了解Excel函数的人能够理解我在讲什么。
用Access数据库计算的效果
和Excel函数相比,用VBA模块对表进行计算有利有弊:
优点是:
1.计算能力强。用Excel计算两三个字段就比较吃力了,在Excel单元格里写代码,会导致单元格比较臃肿。而Access的VBA模块在这方面能力强一些。
2.调整容易。用VBA模块计算,便于函数的维护和拓展,计算过程清楚不容易乱。接下来会用案例来解释这个问题
Access数据库VBA模块
用VBA模块计算也有缺点:
1.学习成本高。得了解什么叫变量,Function怎么用。编程就没有轻松的,会有一些坑。
2.会被鄙视。搞编程的码农会鄙视你,说你用VBA很low,搞得你很烦。(很奇怪,按这个原理,搞Excel函数不是更“low”吗?)
下面讲一下这个案例:
一、本案例的需求介绍
这个案例是一所高中的真实案例:以周为单位,计算每名老师的课时费。
这个工作用Excel完成有些麻烦(但不是不行),主要计算需求如下(为了循序渐进的教学,进行了适当的简化):
1.该学校的教课老师有100多名,有6个学科:
语文
数学
英语
物理
化学
生物
2.每周20小时内,语文数学英语100元小时,物理化学生物80元1小时
3.每周超过20小时,所有科目都是200元每小时。
在实际的案例中,每个老师教的班级还有快慢之分,给快班讲课的补助价格更高一些。但为了讲解方便,我今天先省略这个需求。
二、表设计思路
1.必须设计成一维表形式
用Excel也可以这个计算,用函数就可以。
但是表的设计必须遵循一维表的形式,无论是Excel还是Access的表都必须遵循这个原则,不然设计容易乱。
尤其是Excel设计更容易乱。
2.表的字段要严格控制
这个表的字段一共就4个:
时间
老师
科目
课时
用Access来设计表的话,字段里不需要设计价格、没有星期。价格在VBA里体现,星期用查询(SQL)自动生成。
这就是Access框架的威力,Excel就很难做的这么清爽简约。据我所知,Excel做星期查询很难。
3.表的更新
这个计算做成之后,只要定期往表里更新数据就行,老师上一天课就更新一条,更新完之后,计算结果就自动生成了,报表也能自动化生成。
三、查询设计
简单来说,这个课时费计算的Access查询需要两步:
1.生成周查询表
2.在周查询基础上,加上计算字段,计算字段运用的是VBA的模块(VBA模块是本文的核心)
先要解释一下为什么要两次查询,因为这个需求的时间范围是按周汇总,如果一次查询完成计算的话,SQL会乱。
1.生成周查询
这个技术我之前做过,代码界面如下:
第一次查询
2.计算字段
计算字段包括5个
一类课时量
二类课时量
一类课时费
二类课时费
总课时费
第二次查询
一类课时就是每周20节以内的课时,二类课时就是每周超过20节的课时。
四、VBA模块设计
下面是我们本文的核心,VBA模块设计。本需求我采用的是一个模块,下面四个函数,四个函数共用两个变量就可以。
其实VBA模块一点都不神秘,核心就是设计方程、解方程:
y=f(x1,x2)
再详细一点,y=Function(x1,x2)
这个Function英语就是方程(也叫函数)的意思,也就是VBA模块里我们要设计的一种计算方法,原理没什么难的。
比如,我们设计一个函数
x1=2
x2=2
y=f(x1,x2)=(x1 x2)^2
x1和x2相加再平方这个过程,就是一个函数(Function)计算。
Excel里要计算的话,就是直接新起个单元格,单元格里输入:
=(A1 B1)^2
用Excel来计算这个计算课时费需求也完全可以,但是如前所述,Excel很难计算复杂情况,比如我们本次需求就不这么简单,至少有两个判断:
1.判断这个老师教的是什么课,数学外语语文的课时费就高一些。
2.判断这个老师这周上没上够20节,上够是一个钱、没上够是一个钱。
用Excel来算就显得不那么简单了。
更何况这个需求,比我实际交给客户的需求还简化的多:客户要求的需求还有不同班级的不同费用,等于多了一个维度的计算。用Excel做就非常吃力了(这也是为什么客户找到我)。
我们这个需求的VBA代码有5个函数(Function),分别对应上个章节的5个计算字段
模块有两个总的变量,一个是每周的总课时(通过第一次查询计算得到的),第二个是科目。
两个变量
每个函数用方程表示就是
一类课时费=f(每周总课时,科目)
二类课时费=f(每周总课时,科目)
以此类推
VBA模块总界面
五、VBA代码设计
每个函数的代码设计,我承认对初学者有点难,但是学了一会就会觉得也不过如此,基本就是If Else语句判断。
我们以一类课时量为例
分为以下两种情况
1.当周上课没到20节
一类课时量=f(每周总课时)=当周课时
2.当周上课超过20节
一类课时量就是20节。
第一档课时数
二类课时量也分两种情况
1.当周上课没到20节,那么:
二类课时量=0
2.当周上课超过20节
二类课时量就是:周课时量-20
第二档课时数
(看懂了吗?没看懂公屏 1)
因为课时量不受科目的计算,所以方程里没有科目。如果计算课时费的话,就要加上科目,计算稍微复杂一些。
一类课时费:
1. 没到20节
数学语文外语
一类课时费=当周课时量*100
其他科目
一类课时费=当周课时量*80
第一档课时费
2.超过20节
数学语文外语
一类课时费=20*100
其他科目
一类课时费=20*80
逻辑就是这样,二类课时费、总课时费的VBA,大家照猫画虎做一下。
六、做报表
最好查询之后,就是界面的设计和美化了。这方面技术我讲的很多。不做赘述。
我建议用报表的形式,方便又快捷、简约又大方。
总体效果
有问题就在评论区交流哈~想学习、想领取这个案例给我留言哈~
,免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com