vba对象常用属性和方法(用VBA模块计算课时费)

这篇技术文章我们讲一下:怎么用VBA模块计算一些稍微复杂的表。

(今天发的的几个短视频段子,播放量团灭,很难过。还是讲技术吧)

用AccessVBA模块来对表进行计算,相当于用Excel的函数来计算,二者在本质上没有区别。了解Excel函数的人能够理解我在讲什么。

vba对象常用属性和方法(用VBA模块计算课时费)(1)

用Access数据库计算的效果

和Excel函数相比,用VBA模块对表进行计算有利有弊:

优点是:

1.计算能力强。用Excel计算两三个字段就比较吃力了,在Excel单元格里写代码,会导致单元格比较臃肿。而Access的VBA模块在这方面能力强一些。

2.调整容易。用VBA模块计算,便于函数的维护和拓展,计算过程清楚不容易乱。接下来会用案例来解释这个问题

vba对象常用属性和方法(用VBA模块计算课时费)(2)

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个:

时间

老师

科目

课时

vba对象常用属性和方法(用VBA模块计算课时费)(3)

vba对象常用属性和方法(用VBA模块计算课时费)(4)

用Access来设计表的话,字段里不需要设计价格、没有星期。价格在VBA里体现,星期用查询(SQL)自动生成。

这就是Access框架的威力,Excel就很难做的这么清爽简约。据我所知,Excel做星期查询很难。

3.表的更新

这个计算做成之后,只要定期往表里更新数据就行,老师上一天课就更新一条,更新完之后,计算结果就自动生成了,报表也能自动化生成。

三、查询设计

简单来说,这个课时费计算的Access查询需要两步:

1.生成周查询表

2.在周查询基础上,加上计算字段,计算字段运用的是VBA的模块(VBA模块是本文的核心)

先要解释一下为什么要两次查询,因为这个需求的时间范围是按周汇总,如果一次查询完成计算的话,SQL会乱。

1.生成周查询

这个技术我之前做过,代码界面如下:

vba对象常用属性和方法(用VBA模块计算课时费)(5)

第一次查询

2.计算字段

计算字段包括5个

一类课时量

二类课时量

一类课时费

二类课时费

总课时费

vba对象常用属性和方法(用VBA模块计算课时费)(6)

第二次查询

一类课时就是每周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个计算字段

模块有两个总的变量,一个是每周的总课时(通过第一次查询计算得到的),第二个是科目。

vba对象常用属性和方法(用VBA模块计算课时费)(7)

两个变量

每个函数用方程表示就是

一类课时费=f(每周总课时,科目)

二类课时费=f(每周总课时,科目)

以此类推

vba对象常用属性和方法(用VBA模块计算课时费)(8)

VBA模块总界面

五、VBA代码设计

每个函数的代码设计,我承认对初学者有点难,但是学了一会就会觉得也不过如此,基本就是If Else语句判断。

我们以一类课时量为例

分为以下两种情况

1.当周上课没到20节

一类课时量=f(每周总课时)=当周课时

2.当周上课超过20节

一类课时量就是20节。

vba对象常用属性和方法(用VBA模块计算课时费)(9)

第一档课时数

二类课时量也分两种情况

1.当周上课没到20节,那么:

二类课时量=0

2.当周上课超过20节

二类课时量就是:周课时量-20

vba对象常用属性和方法(用VBA模块计算课时费)(10)

第二档课时数

(看懂了吗?没看懂公屏 1)

因为课时量不受科目的计算,所以方程里没有科目。如果计算课时费的话,就要加上科目,计算稍微复杂一些。

一类课时费:

1. 没到20节

数学语文外语

一类课时费=当周课时量*100

其他科目

一类课时费=当周课时量*80

vba对象常用属性和方法(用VBA模块计算课时费)(11)

第一档课时费

2.超过20节

数学语文外语

一类课时费=20*100

其他科目

一类课时费=20*80

逻辑就是这样,二类课时费、总课时费的VBA,大家照猫画虎做一下。

六、做报表

最好查询之后,就是界面的设计和美化了。这方面技术我讲的很多。不做赘述。

我建议用报表的形式,方便又快捷、简约又大方。

vba对象常用属性和方法(用VBA模块计算课时费)(12)

总体效果

有问题就在评论区交流哈~想学习、想领取这个案例给我留言哈~

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页