确保 Excel 文件可迁移性的注意事项

作者: , 共 1724 字

在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。

1. 声明每一个 VBA 变量

预先声明每个 VBA 的变量。对方的 Excel 选项也许勾选了「要求变量申明」,你没声明变量在你这里运行没问题,到对方那边就会出错。另外,预先声明变量,是一种良好的编程习惯,可防止失误。

为防止忘记声明变量,可主动勾选 VBA 选项中的「要求变量申明」,或者在每个 VBA 模块和类模块前面都写上Option Explicit

2. 注意数据库的连接字符串

在 Excel 表格中使用外部数据源或者 VBA 中操作数据库时,连接字符串有多个写法。通过 ODBC 连接数据库相对比较简单,但损害了 Excel 文件的可迁移性,因为对方电脑上很可能没有事先定义 ODBC 数据源。 所以,尽量使用下面第二种方法。

' 使用DSN(ODBC)连接数据库的连接字符串,需对方机器同时配置ODBC
Provider=MSDASQL;DSN=odbc_name;UID=username;PWD=password;

' 使用IP直接连接数据库的连接字符串,无需对方机器配置ODBC
driver={SQL Server};server=ip;uid=username;pwd=password;

3. 避免引用 dll

比如写一段调用 Outlook 发送邮件的 VBA 代码,我可以先引用「Microsoft Office 12.0 Object Library」,然后在代码中声明对象:

dim xOutlook as New Outlook.Application

但这样做有一个缺陷。对方的 Office 版本可能与你的不一致,从而不存在你所引用的这个问题,从而导致一连串错误。 解决问题的办法是,尽量不直接引用 dll 文件,变量声明为Object对象,并通过CreateObjectGetObject来生成或获取对象。

dim xOutlook as Object

Set xOutlook = GetObject(, "Outlook.Application")

这种方法相对于直接声明Outlook.Application对象有一个稍微麻烦的地方, xOutlook 的方法和成员不会自动完成。所以,我编写该类程序时,一般都是先申明为实际对象Outlook.Application,写完程序后再把Outlook.Appilcation改成Object

上面Outlook只是一个例子。其它还有Word、数据裤ADODB.Connection等对象都应注意使用该法则。

4. 避免引用外部文件

Excel 可以引用其它 Excel 文件里的数据。但它有一个缺陷, Excel 在引用外部文件时使用绝对路径。这样假设 A 文件引用相同目录下的文件 B ,那么即使你把 A、B 两个文件都发给对方,对方在打开 A 文件时也可能出错。

避免该问题的方法就是避免引用外部文件,比如特殊复制粘贴去掉公式,或者把外部数据表复制到主文件直接引用等等。如果迫不得已使用外部文件,那只能让对方到「数据->编辑链接」一栏重设链接文件的位置。

5. 注意 Office 版本差异

Office 各个版本有一些差异,功能也不完全一样。 尤其要注意的是各版本的函数不一样。比如 Office 2003 里就没有 Office 2007 的IfError函数。如果已知对方的 Office 版本有差异,一定要注意这些函数上的差异。

比如AVERAGEIF, AVERAGEIFS, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE, COUNTIFS, IFERRORSUMIFS这些函数都是从 Office 2007 才引入的。

当然这点也可以无视。Office 2003 是应该扔到垃圾堆了。

Q. E. D.

类似文章:
2014-03-25 更新:我已经将该类修改成函数形式,并增加新功能,参见更新 Excel 的数据库查询函数库
更新一下之前写的Excel 的数据库类,将其改成函数的形式,调用更简单(省却了生成类实例的步骤)。现在这个代码在工作中用了一年多,已经比较健壮。若有问题,请留言指出或与我联系。
Excel、Matlab 在与数据库交互数据时,通常需要事先配置好 ODBC 数据源,这导致一个文件换到另一台机器还需要重新配置 ODBC。手工配置太麻烦,从国外一个网站看到一种解决方案:
相似度: 0.145
编程 » Excel, VBA
Excel VBA 出错时给出的错误信息极少,需要充分利用各种工具来进行调试。
Excel 有一个很有用的功能是直接导入外部数据库或者使用外部数据源建立数据透视表和数据透视图。但比较可惜的是,这个数据源的查询语句是静态的,它无法根据日期自动修改(比如在应用中,我们希望每天获取的外部数据都是当天最新的数据),下面两个函数是修改外部数据源的 VBA 代码,调用它们就可以建立动态的数据源。
Matlab 的 database 工具箱只支持 ODBC 数据库连接,在使用之前需要先设置 ODBC 数据源。之前在 Excel 里用的是 ADODB ,使用 ADODB 的好处是无需设置 ODBC 源,似乎效率也要高一些(但我一直没弄懂 ADODB 是什么东西,所以效率这东西我也说不清)。
编程 » Excel, 数据库
在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式
编程 » Excel, VBA
某些时候需要打开 Excel 文件来获取或者写入数据,但又不希望跳出打开的 Excel 文件窗口,可以用下面的代码:
Excel 多表合并和查询是一个应用很广泛的问题。下面是一个简单的例子,我们需要从两张数据表里,得出每个行业的股票波动率平均值。第一个数据表保存了股票和行业的对应关系,有两列,第一列为股票名,第二列为每只股票对应的行业。第二张表保存了各个股票在各个交易日的收盘价和前收盘价,有四列,第一列是股票名,第二列为交易日,第三列和第四列分别为股票在这个交易日的前收盘价和收盘价。
在工作中定期或不定期会收到一些数据文件,然后要将它们的附件保存到自己的电脑上,下面演示如何让 Outlook 自动做这件事情。
IT » 比特币, 数字货币
最近看到一篇文章Satoshi』s Genius: Unexpected Ways in which Bitcoin Dodged Some Cryptographic Bullets,国内有人翻译过(中本聪的天才:比特币以意想不到的方式躲开了一些密码学子弹)。里面说的第一个就是天才的中本聪并不是将公钥而是将公钥两次 HASH 之后作为比特币账户的地址,这可以让比特币系统抵抗量子计算机的攻击。
几天前,中行转债( 113001 )跌到 97 块钱左右(最新价格是 97.23 ),当时转股溢价率和纯债溢价率都特别低,我发了一条微博: