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

hhh5460

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

今天的作者Dany Hoter（Excel组的产品计划员）将要讨论他最近发现的使用VBA操作大范围Range的各种方法之间的性能特性。

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

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

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

1.使用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

2.使用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

3.使用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。


========================================================
其实应该还有别的选择，如for each:
Dim MyVar as Double
For Each C In Range("A1").CurrentRegion
    MyVar=C.Value
    If MyVar > 0 then
         MyVar=MyVar*Myvar        ' Change the value
         C.Value=MyVar
      End If
Next C
Set C=Nothing
(其实这种方法本质还是Range)

[ 本帖最后由 hhh5460 于 2010-11-7 19:26 编辑 ]

杏花雨V翟

楼主辛苦 这个需要慢慢消化

李子然

进来学习下

gjj136138139

进来看看，学习学习

ldy

DataRange = Range("A1").CurrentRegion.Value  
DataRang 其实就是论坛中无数帖子中都有提及的、传说中的-----数组。

wangfengcpa

我晕啊，你第三个是数组啊。

刘志文

学习下

NewbyExcel

vba程序优化要求少用变体类型变量，看来事情没有绝对的。学习。谢谢！

secowu

03上不知能否这样用。。
另外楼主提供下博客的链接地址

zhao__feng

谢谢,学习下