在Excel中遍历大范围Range的最快方法

微软Excel官方博客上10月3日发表的文章

问题描述
你有一个很大的Range的Excel数据。例如说10万行，每行50列（当然是用Excel2007），这样一共有5百万个单元格的数据。A到F列有你需要分析的文字数据，根据每一行数据的组合，你需要使用G到H列的数字做一些计算并保存在I和J列。也可以在I和J列放入20万个公式，但你肯定知道有这么巨大数量的公式的表格会运行很慢并且占用很大的内存空间。
你决定尝试用VBA代码来解决这个难题。问题是怎样以最有效的方法来完成这样的任务？

什么是你的选择
你怎样才能扫描Excel中的一个区域，从某些单元格中读取数据，然后更改其他某些单元格的内容？

使用Range对象
假设你想读取的区域是从A1开始的。代码就看起来应该这样：

Dim DataRange as Range    ' 也可以使用 Dim DataRange as Object
Dim Irow as Long
Dim MaxRows as Long
Dim Icol as Integer
Dim MaxCols as Long
Dim MyVar as Double
Set DataRange=Range("A1").CurrentRegion
MaxRows= Range("A1").CurrentRegion.Rows.Count
MaxCols= Range("A1").CurrentRegion.Columns.Count
For Irow=1 to MaxRows
For icol=1 to MaxCols
MyVar=DataRange(Irow,Icol)
If MyVar > 0 then
MyVar=MyVar*Myvar ' 更改数值
      DataRange(Irow,Icol)=MyVar
End If
Next Icol
Next Irow

使用Selection，并使用Offset移动
许多VBA开发者是从记录宏来学习VBA技巧。
当使用相对引用时，生成的VBA代码如下：

      ActiveCell.Offset(0, -1).Range("A1").Select

结果，很多开发者采用这种方法，在代码中使用ActiveCell或Selection区域来在单元格之间移动、读取或写入数据。代码应该就像这样的：

Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
Range("A1").Select
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
For Icol = 1 To MaxCols
MyVar = ActiveCell.Value
If MyVar > 0 Then
MyVar=MyVar*Myvar ' 更改数值
      ActiveCell.Value = MyVar
End If
ActiveCell.Offset(0, 1).Select ' 向右移动一列
Next Icol
ActiveCell.Offset(1, -MaxCols).Select ' 向下移动一行并回到第一列
Next Irow

使用Variant类型变量
这个方法复制区域中所有单元格的值到内存中的一个变量，然后在变量中处理数值，如果需要的话，处理完后还可以将数字移回到该区域。
下面是这次的代码：

        Dim DataRange As Variant
Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
DataRange = Range("A1").CurrentRegion.Value ' 不使用Set
    MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
For Icol = 1 To MaxCols
MyVar = DataRange(Irow, Icol)
If MyVar > 0 Then
MyVar=MyVar*Myvar ' 更改数值
        DataRange(Irow, Icol) = MyVar
End If
Next Icol
Next Irow
Range("A1").CurrentRegion = DataRange ' 将结果写回到区域中

比较前两个方法，这个方法的一个区别是速度非常之快。

性能总结
我使用相对较大的区域来比较这三个方法，下面是结果：

方法	操作	单元格/秒
Variant	读	1,225,490
	写	714,286
	读/写	263,158
Range	读	250,000
	写	1818(?)
	读/写	1,852
Offset	读	206
	写	200
	读/写	203

你可以看到使用Variant变量能够获得快很多的速度，尤其是改变单元格的时候。即使可以通过Excel公式来完成计算，在某些情况下这个方法是唯一可以接受的，因为使用非常大量的公式会导致Excel非常缓慢。明显需要避免的方法是使用Offset来移动ActiveCell。