在Excel中遍历大范围Range的最快方法（微软Excel博客文章）

   问题描述
你有一个很大的Range的Excel数据。就说是10万行，每行50列（当然是用Excel2007）。这样一共有5百万个单元格的数据。A到F列有你需要分析的包含文字和字母的数据，根据每一行数据的组合，你需要使用G到H列的数字做一些计算并保存在I和J列。你也可以在I和J列放入20万个公式，但你肯定知道有这么大数量的公式的表格会运行很慢并且占用很大的内存空间。

你决定尝试用VBA代码来解决这个难题。问题是怎样以最有效的方法来完成这样的任务？

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

使用Range对象

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

 Dim DataRange as Range ' Could also be 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 ' Change the value 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 ' Change the value ActiveCell.Value = MyVar End If ActiveCell.Offset(0, 1).Select ' Move one column to the right Next Icol ActiveCell.Offset(1, -MaxCols).Select ' Move one rows down and back to first column 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 ' Not using 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 ' Change the value DataRange(Irow, Icol) = MyVar End If Next Icol Next Irow Range("A1").CurrentRegion = DataRange ' writes back the result to the range

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

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

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

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