本帖最后由 cooloaky 于 2012-1-13 22:39 编辑
学习Vba大概有六七年了。其间的痛苦和快乐不一一赘述，这里主要谈谈基于Exbel作程序开发的一些心得，以期抛砖引玉。 论坛上相关的帖子很少，希望大家能贡献自己的想法。
一、VBA的优劣 VBA的优劣一目了然。从使用者数量来看，可知其优势在于应用广泛，简明易学，无需安装（有excel即可）。其最大劣势从其命名可见一斑：visual basic for application，也就是说它是依赖于application的，独立性和安全性没有保障，这造成其功能受版本和本机引用库的影响，代码安全性不高（易破解）等等。笔者认为这是造成VBA使用者广泛、但市面上鲜见商业化应用程序的主要原因。现代化的公司对数据安全性提出了极高的要求，而VBA本身在安全性上有致命的缺陷。试想，VBA被破解后，写入其代码的数据库账号还会安全吗？或许有人说，制作成DLL后不就可以强化安全性了吗？虽然这是可行的，但大多数公司对电脑安全性有很大限制，如非认可的软件/控件，其安装和推广并不容易。且这样损伤了VBA的便捷性（通常excel文件即程序，无需额外安装的）。 其实excel最大的长处在于强大的数据输出功能（譬如外部数据查询、数据透视变等）。结合VBA的优劣，我个人觉得VBA最适合扮演的角色是充当专业数据库/软件的插件，即在数据库和excel之间充当数据通道，将数据输出到excel界面，从而定制各种各样的报表。通常企业级数据库的安全性有较高要求，但我们可以通过只读的账号访问部分内容，然后输出到excel工作表或图标中。笔者是从事财务工作的，以前制作报表必须登录财务系统，将账务数据导出到文本文件，再从文本文件读入数据到excel，然后在对数据进行加工，过程繁琐且费时颇长。通过VBA的ADO,我可以直接从财务数据库中读取需要的数据，并输出到定制的excel模板文件就有成品的报表产生了，整个过程只需要点击一个按钮即完成，时效和准确性上有了大大的提高。窃以为这就是VBA的魅力所在了。 二、程序的实现方案 从程序需实现的功能需求来看，可以将其划分为单机版、联机版及服务器版。单机版指程序文件及数据存放均在本地盘。联机版指主要数据存放在文件服务器上，多个用户均可读。服务器版则指数据存放于数据服务器上，用户通过ODBC等数据通道来接触数据。从用户数量来看，可以将其分为单用户和多用户。这两个分类互有交叉，单机版的可以有多用户，服务器版也可能是单用户。单用户的程序比较简单，因一个人使用，想怎么弄就怎么弄，这里不多赘述，主要谈谈多用户的方案。笔者学VBA是半路出家（我不是计算机相关专业出身），无法处理巨大数量的用户应用，以下从几个方面简单谈谈不超过10个人的多用户程序的实现方案。 1、程序实现方式。我接触VBA的时间不算长，对XLL、DLL、宏表之类的不太熟悉，通常是用xls(xlsx)或xla(xlam）两种方式来作为程序文件的载体。具体哪种方式要结合功能需求及具体的IT环境来实现。通常来说，使用频率不高的程序一般都是用xls（xlsx）来实现，好处是维护和初始化比较方便，一个压缩包就可以解决更新和安装的问题。对使用频率较高的程序，需要通过加载宏的方式了。好处是打开excel就可以开启各种功能，缺点是维护起来比较麻烦，每次升级后需要指导用户如何先卸载加载宏，然后找到指定目录，再用新程序文件覆盖，最后再重新加载。很多初级的用户连加载宏文件储存在哪里都不是很清楚。 2、数据的存放。数据的存放有几个方案，具体采用哪种方案需要考虑存量的大小（因对程序运行的速度有影响）以及程序布置的IT环境。我的经验主要有3种：直接将数据储存在excel文件中、将数据储存在access数据库中以及将数据储存在server中。对数据量很小或无需存放数据（指直接依存于专业数据库）的，可以考虑将一些信息直接存放于程序文件中。有一些数据储存要求，但数据量不大的可以考虑server或mdb (accdb）中。当然，IT环境对实现何种方案有很大的制约。多用户的程序最理想的方案当然是利用数据库，但作为我们这些非专业开发人士，公司很难将数据库开放给我们使用。给个只读的ID已经是很不容易了，奢望完全掌控一个数据库？谈何容易！所以我的经验最多在于利用access数据库来作数据存储空间，以下的解决方案往往也是基于access数据库来讲。 3、应用参数的配置。每个程序一定会有一些配置信息，如server的配置、报表输出的选项、加载宏文件需指定系统路径等等。这些信息量很小，但程序初始化的时候通常都要用（特别是加载宏方式实现的时候，系统路径通常和主程序不在同一个目录下，需特别指定系统路径）。对于这种调用数据库之前就要指定的信息，我往往通过3种方法的实现：一是通过excel的name对象，将路径等信息指定给其referto或comment属性。二是通过读写注册表。三是通过同主程序同一路径下得ini文件。可将固定信息写死在程序中，也可以通过程序语言在组织用户定义相关信息。 4、设置、映射等信息的维护。我将数据归纳为3类：一是配置信息，指系统路径等一些常量数据；二是维护参数信息，指由用户维护，并写入数据存储空间的信息，譬如财务的会计科目表等；三是变量数据，即数量最大的数据，往往报表就是提取这些数据来制作的，譬如财务上的明细账等。上面第3点讲得是常量数据部分，这里提到的是第二种信息。这部分信息有些特殊，因为是用户编辑的，因此如何通过友好、稳定且高效的方式来实现颇有讲究。通常，就xls(xlsx)方式的程序，我往往直接在文件中建立一张工作表，专门用来进行参数信息的读取（可通过QueryTable来实现）、编辑及保存。具体操作在2003版可通过commandbar对象触发，在worksheet_activate和worksheet_deactivate事件中决定commandbar对象是否可见；在2007版下通过ribbon的Invalidate或InvalidateControl对指定Group的Visible来进行操作。当然，也可以直接建立一些控件（按钮）等来触发事件。如果程序是xla(xlam)方式的话，需要在系统目录下定制xlsm文件，通过打开该xlsm文件来触发操作。同样在该文件的workbook_activate及workbook_deactivate事件中显示或隐藏commandbar及ribbonControl。 5、数据的交换及同步。前面讲了参数信息的维护，但就单机版的程序，如何保证大家使用的参数及配置信息都是同步的呢？而且一般分支机构多的公司还有数据合并的需求。这里提到数据交换的概念，即在程序中设置一个发布和接受数据的借口，将一些数据通过系统定义格式发送给用户或更新本地数据。数据格式一般用txt或xml，当然也可以用excel。比较倾向用xml，是因为可以结合xsd来验证数据的有效性。无论什么格式的文件，都可以通过加密解密算法来提高信息安全。笔者为公司开发的监管报表系统以及预算系统均是通过这种方案来进行总公司的合并以及参数信息同步的。 6、数据的输出及报表。数据有了，参数信息也有了，接下来就是我们写程序的根本目的：报表。报表的实现方案有2个选择：预设模板和不用模板。预设模板适用于一些运算逻辑比较复杂的报表，可以将报表的规则定义为配置信息，然后按照规则抽取及输出数据；也可以将在模板中建立数据页并隐藏，报告页的公式预设，程序要做的事情只是打开模板并将数据输出到数据页即可。值得一提的是报表也可以通过第5点提到的数据交换来进行发布及更新。笔者在开发监管报表系统的时候，即是将模板连同报表信息作为参数信息进行发布，以达到各分支机构共享的目的。不用模板则适用于一些简单输出的报表，从财务角度讲，试算平衡表、明细科目表等简单报表可以通过新建一工作簿或工作表来直接输出数据。当然，你也可以通过VBA来对报表也进行一些加工，以达到更美观的效果。在2007及以后版本中，引入了ListObject对象，可以很方便的获得漂亮的样式。最后，对于一些数据量巨大的报表，也可以通过数据透视表的形式来完成，即通过pivotcache定义外部数据，然后生成pivotTable或pivotChart。 除了报表之外，配合各种不同的应用程序，我们还可以参照数据交换的概念，对系统数据进行扩展输出（即interface）。通过定义格式来输出数据，供其他的程序使用。同理，系统也可以开放数据接口，从其他系统导入数据。 7、安装及升级包 这是最近在思考的问题。零零碎碎开发的程序也蛮多了，每次实现了新需求必然会面临升级的问题，另外还有一些电脑白痴的安装问题都让人挺头疼的。初步设想有2个解决方案：1是通过一个类似Program Manager的加载宏，按联机版设立数据库，开发者将新的程序文件及批处理代码存入数据库中，用户端只用点选更新就可以从数据库中下载新版程序文件并覆盖当前程序，一些配置常量的更新则可以通过导入代码->执行->删除代码的次序来进行。第2个方案则是直接建立一个程序文件，用户只要打开该文件并运行宏就可以完成安装或升级。两种方法各有优劣，这里不一一评说，也欢迎大家给意见。 三、安全性初探 最前面提到，VBA的安全性不高。对于老手来说，VBA就像穿了半遮半掩薄纱的美女一样，轻轻一拉就可以看到一切。这里提到的安全性，窃以为只不过在这件薄纱上系了个腰带，不能说完全安全，只是让人解衣的时候多费一些手脚。 1、数据加密。前面略提了一些在数据交换的过程中，基本数据是裸露在外的，这时候需要进行加密输出，解密导入。具体采用何种加密算法，大家自己在论坛上找吧； 2、库加密。Access数据库的加密很简单，不多说。不过可以尝试将扩展名改为xml或其他的形式，掩盖一些真相； 3、权限控制。将系统功能划分为各种组合，并将用户账号和权限绑定，在触发系统功能性操作之前，在系统中校验权限，这个还是比较简单的。另外一个方法在程序加载的时候，隐藏没有权限的功能触发点。最后一个方法是最复杂的，我也没有完全实现，就是通过用户权限来设置不同的数据库视图，以控制用户阅读及修改的权限。这种方法往往应用于企业数据库中，access要不要搞这么复杂，值得商榷。 4、备份管理。这里不展开，只提一点：版本多了如何管理？建议建立一个索引表在库文件里，每次备份的时候加入说明性文字，管理员就可以根据这些索引文字来恢复指定版本了。 四、一些技巧 其实前面在讲方案的时候，提过一些技巧性的东西。这里再提一些： 1、 工作界面的保护。每次操作之前对工作表或工作簿进行解保护，操作完之后加上保护，或者定义好工作界面的可操作区域，然后在worksheet.protect方法里，加入userinterfaceonly:=true即可在保护状态对工作表可编辑区域进行编辑； 2、 智能控件及组合。通过类似配置数据的存储方法，可将用户最后或常用的一些控件输入内容储存起来，下次用户打开这个窗体的时候，无需每次录入这些常用的信息。 3、 窗体的美化。这个方法很多，有用API的，也有不用的。我比较常用的3种自定义类，一个是上面提到的让控件具有记忆功能的SmartCtrl，二是和XTimer结合的XStatusbar，三是通过Label来实现command button效果且带事件功能的IconBar。 4、 标准化SQL。通常用自定义类的方法实现，定义好Select的内容，Table的Join方法以及Where的表述，基本上可以实现SQL语句的输出。 5、 善用XSD。通过编辑界面批量录入数据的时候，很容易发生数据类型不符等问题，这个要么在Save之前进行校验，要么通过XSD和XML进行匹配。由于维护的表格或参数表不同，将校验特征写入程序不太现实，最好就是用xsd进行匹配校验了。 啰啰嗦嗦讲了4千多字，好像意犹未尽。我不是IT科班出身，VBA纯属自学，在论坛上也得益颇多，一些想法是直接采用前辈高手的做法，另外一些也是自己摸索总结得出。希望大家不吝指教，共同提高。 | 
/1 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-1-12 14:28
楼主
