小微企业中EXCEL VBA的开发应用

肖罡

摘 要：如今，大中型企业都斥巨资邀请专业的软件公司开发企业的专用软件。小微企业盈利有限，怎样少花钱也能拥有自己的专属软件，EXCEL中的vba开发可以助一臂之力。

关键词：小微企业；EXCEL VBA；开发；应用

无论大小企业里，办公用的各种表格数据是缺不了的，但大部分企业仅使用EXCEL中基本的表格公式等进行数据的处理，稍高级些的应用也只在诸如数据透视表、各种分析饼图等的使用上。深入使用EXCEL中vba开发的不是太多。

在此，将笔者使用EXCEL vba开发专属的应用软件的经验分享如下。

1 收集用户需求

不管使用何种软件开发系统，首先要收集企业对数据的需求，换句话说就是企业需要什么样的数据，通过怎样的处理能得到企业需要的结果。

这是一家小型的信贷公司，主要的业务就是通过替客户办理专项分期贷款，然后将客户的资料递交银行，把自己前期垫付给客户的贷款转换为客户从银行的分期贷款，由于这项贷款时限比较长，一般都是三年，由客户按月交款到公司，再由公司还上银行的分期贷款。每个客户从开始办理业务，正常按月向公司缴纳月供金额，连续36个月后，客户结清贷款，与公司履行合同完毕，该客户才从客户资料表中退出。

通过对该公司业务流程的梳理，公司的业务不是很复杂，但随着用户量的增加，再加之三年36个月的还款期，使数据量陡然增大，靠人工进行登记处理显然已经不现实了。

2 建立数据模型

经过收集用户的需求分析得出，需建立九个数据表、一个用户表和一个临时表。分别为客户资料表、月供表、还款表、银行余额表、银行卡贷方明细表、结清客户表、结清月供表、结清还款表、用户表、临时表等。其中客户资料表、月供表、还款表以客户身份证号码为关键数据相互连接，月供表、还款表、银行余额表以银行卡为关键数据连接，从而形成了数个主要数据表之间的逻辑关系

3 程序设计

项目的框架有了，下面正式进入了程序的设计和用户交互的界面了。

3.1 数据的录入维护

既然是一个项目，那该有的如菜单、按钮、显示界面、背景等等也应该有一点吧，所以选择了EXCEL vba中的窗体设计，因为追求实用、简捷，所以没有什么美术设计之类的参与，很直白的菜单，一看大体都知道是什么。

第一步当然是数据的录入了，数据录入是系统能正常运行的基石。所以对录入系统的原始数据必须有一个合法、合理、合规的判断，对合法、合理、合规的数据进入系统保存，非法输入的数据提示用户，直到数据合法，或者退出录入界面放弃录入客户资料中的客户姓名、身份证号码、电话号码等信息必须是文本格式，并且客户姓名必须是汉字，数字、字母等符号在姓名栏中的第一位是无法输入的，身份证号码必须满足首位是数字且必须是十八位（当然还有更严格的判断身份证号码是否符合身份证号码的编排规则，考虑加入这段代码会增大数据的体积和执行效率，没有使用）。当用户录入一个合法的身份证信息后，同时在月供表、还款表中同时加入这个信息，作为识别用户数据的关键关联数据。还有一个重要的数据就是客户开始办理业务的日期，这个日期的合法性直接影响系统生成报表和判断该客户是否有逾期缴纳月供的情形的主要依据。这个数据的格式是日期型。贷款本息数据、和为了方便生成报表数据的客户累计月供及累计期数也在客户资料表中存储。

銀行卡的初始信息也是要先期维护进银行余额表中，包括卡号、银行名称、开卡金额信息。此表中包括多张银行卡信息，用以接收客户交来的月供。

3.2 数据的日常处理

基础表信息有了后，可以开始日常的运营了。运营的流程是这样的：每月客户按时交来月供金额，将此金额及收到此金额的卡号、交来日期、客户的身份证号按照一定规则存入对应客户月供表中，同时将此金额按对应卡号累加进银行余额表中，并将该客户交来月供金额累加一次计入该客户的累计月供中。按此规则逐一处理完当月所有客户交来的月供款项。如果期间有新增客户，操作与之前步骤相同，维护进客户资料表中。如果期间银行卡有利息收入，也需维护进银行余额表中。

日常运营中，数据处理模块需要完成前述步骤的功能，采取批量导入客户的月供数据和单个客户月供数据的录入功能，根据用户的需要自由选择。对于批量导入客户，需建立设置好数据模板，按照客户姓名、身份证号码、还款卡号、金额及缴款日期的格式，由用户做成一张EXCEL表，使用vba中系统的打开文件函数读取表格，逐一处理表中的所有数据。同样的，对于表格中的每一行数据都要经过判断其合法、合规、合理，才能进入系统。对于错误的客户身份证、错误的卡号、非法的日期格式等等一系列错误，在临时表中对数据判断的结果予以记录，在处理完表中所有的数据后，以提示文件的形式展示给用户，成功处理了多少笔用户的月供数据，未成功处理的用户逐一列出错误，由用户修改后再次处理。

对于单个客户的数据录入，采取下拉框方式，输入客户姓名的拼音首字母，迅速筛选目标用户，便于快速定位客户，同时也规避了输入错误的用户身份证号的可能，银行卡号也使用下拉框的方式，日期采用日期控件，也同样规避了非法的格式或错误的数据。

在数据处理模块，数据的累加是经常要用到的，但对于录入系统的数据格式一定要进行相应的处理，比如：

Sheets（1）.Cells（k， 20） = Val（Sheets（1）.Cells（k， 18）） + Val（Sheets（1）.Cells（k， 20））

加入val函数，无论所在单元格是文本格式还是数字格式，都可以很好的进行累加，不至于出现系统报错或者导致计算错误。

当一个月的所有客户的数据处理完毕，各个银行卡的余额和银行的余额至少是一致的。所以对银行卡余额的核对也是对录入系统中数据正确与否的大致判断。之所以说是大致的判断，还有可能录入系统的数据有串户的可能，所以核对每个客户的数据是另一项重要的工作。

3.3 数据的查询、输出

这时对系统各个数据的查询就是非常必要的。为了能快捷、迅速地查询到目标客户的数据，同样采用了下拉框合并姓名首字母的方式，选定客户，并列出该客户的所有月供明细数据，便于核对。

这个是系统的一种查询方式，还有按身份证号、月供金额、贷款额等等的各种模糊查询方式。其中上图中客户月供列表的方式使用了ListView控件。这个控件在很多安装了Office套件的电脑上并不是默认就有的。需要在系统中注册一个MSCOMCTL.OCX文件，这个文件的版本为6.1.98.34。注册成功后，在工具箱中右键添加控件，在弹出的窗口中勾选MicrosoftListview Control 6.0 （SP6）后就出现了ListView控件。对控件进行初始化时，设定表头的内容及宽度，使用循环语句将数据加载到列表中。

查询模块中对于用户需要进一步得到完整全面的信息，可以综合客户资料表、月供表、银行余额表等的信息，可形成一个全部客户的贷款信息、月供缴纳情况、是否逾期等一个辅助的表格。

3.4 数据的修改、删除

在日常处理数据中，虽然通过对录入数据进行了各种各样的规范和过滤，防止不合规数据进入系统，但难免还会出现手下的错误操作或者粗心导致的各种错误。增加数据的修改、删除功能是另一道保证数据准确的补救措施。

修改删除包括客户资料、月供数据、银行卡信息等。但这些功能僅仅是一个发现错误的补救方式，不能滥用，否则难以保证数据的严肃性、准确性。为此可以设计增加不同的用户，使录入数据和修改删除数据的人员权限分离。甚至可以对删除修改操作建表来保存用户的操作记录。

4 对开发的vba应用系统的保护

毕竟EXCEL VBA只是一个简易的开发环境，应用软件和数据都在一个工作簿中保存，使程序的可移植性、通用性及数据的安全都大打折扣。另外vba程序是依托EXCEL软件运行，必须使EXCEL的宏安全级别降低才能启动。其次，数据和软件都暴露于用户面前，容易导致误操作损坏数据结构。鉴于此，用Visual Basic将程序入口打包编译成一个可执行程序，通过该执行程序启动工作簿，从而使工作表隐藏于后台，用户的操作都在窗体中进行，也不用关心当前EXCEL 中宏的安全级别。

总结，对于小微企业，使用常见的EXCEL vba来开发适合自己企业的专用软件，既大大提高了对大量数据的规范操作，又可以满足企业对处理大量数据的需求，还可以提高效率，降低使用人员的门槛，当然开发使用费用也是很低的。