如何最快速的大范围扫描EXCEL

如何最快速的大范围扫描EXCEL

问题描述
你有一个大范围的数据在Excel中。比方说，它包含100000列和50列的每一行（是您使用的是Excel 2007年的课程） 。所以你完全有5,000,000细胞。 A至F列有一些字母数字数据，您需要分析的基础上的组合价值的每一行，您需要使用的数值在G至H做一些计算和存储的结果列第一和学者您可以将200000公式中的I和J但是你看到这样的电子表格量的计算公式得到非常母猪和消耗大量的内存。

您决定尝试解决这个问题在一块VBA代码。问题是如何执行这一任务的最有效的方式？

什么是您的选择
你怎么能扫描范围在Excel中，阅读的价值在一些细胞，改变另一些？

使用Range对象
假设范围要阅读始于格A1

代码看起来像这样：

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


使用的选择和使用移动抵消
许多的VBA开发的经验教训的VBA技术从宏观录音。

当使用相对参照生成的VBA代码创建声明如下：

ActiveCell.Offset （ 0 ， -1 ） 。范围（ “格A1 ” ） 。选择

因此许多开发商采用这一技术，并使用ActiveCell或选择范围，从细胞到细胞的代码和读或写的细胞价值观。代码如下所示：

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

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


另一个区别是，这种方法是速度极快，而另外两人。

效能摘要
俺比较了三种方法的比较大的范围，这里的结果是：
  

Method
Operation
Cells/Sec

Variant
Read
1,225,490

  Write
714,286

  Read/Write
263,158

Range
Read
250,000

  Write
1818

  Read/Write
1,852

Offset
Read
206

  Write
200

  Read/Write
203
  


正如你所看到的一个变种变量使用更快特别是在不断变化的细胞。即使可以做到的计算与Excel的公式，在某些情况下这种方法是唯一可以接受的，因为使用了大量的计算公式可以成为非常缓慢。

显然，一个方法，以避免正在使用的ActiveCell抵销。