问题描述
你有一个很大的Range的Excel数据。就说是10万行,每行50列(当然是用Excel2007)。这样一共有5百万个单元格的数据。A到F列有你需要分析的包含文字和字母的数据,根据每一行数据的组合,你需要使用G到H列的数字做一些计算并保存在I和J列。你也可以在I和J列放入20万个公式,但你肯定知道有这么大数量的公式的表格会运行很慢并且占用很大的内存空间。
你决定尝试用VBA代码来解决这个难题。问题是怎样以最有效的方法来完成这样的任务?
你的选择
你怎样才能扫描Excel中的一个区域,从某些单元格中读取数据,然后更改其他某些单元格的内容?
使用Range对象
假设你想读取的区域是从A1开始的。代码就看起来应该这样:
使用Selection,并使用Offset移动
许多VBA开发者是从记录宏来学习VBA技巧。
当时有相对引用时,生成的VBA代码如下:
ActiveCell.Offset(0, -1).Range("A1").Select
结果,很多开发者采用这种方法,在代码中使用ActiveCell或Selection区域来在单元格之间移动,读取或写入数据。代码应该就像这样的:
使用Variant类型变量
这个方法复制区域中所有单元格的值到内存中的一个变量,然后在变量中处理数值,如果需要的话,处理完后也可以将数字移回到该区域。
下面是这次的代码:
比较前两个方法,这个方法的一个区别是速度非常之快。
性能总结
我使用相对较大的区域来比较这三个方法,下面是结果:
方法 | 操作 | 单元格/秒 |
Variant | 读 | 1,225,490 |
写 | 714,286 | |
读/写 | 263,158 | |
Range | 读 | 250,000 |
写 | 1818 | |
读/写 | 1,852 | |
Offset | 读 | 206 |
写 | 200 | |
读/写 | 203 |
你可以看到使用Variant变量能够获得快很多的速度,尤其是改变单元格的时候。即使可以通过Excel公式来完成计算,在某些情况下这个方法是唯一可以接受的,因为使用非常大量的公式会导致Excel非常缓慢。 明显需要避免的方法是使用Offset来移动ActiveCell。