問題描述
你有一個很大的Range的Excel數(shù)據(jù)。例如說10萬行，每行50列（當(dāng)然是用Excel2007），這樣一共有5百萬個單元格的數(shù)據(jù)。A到F列有你需要分析的文字?jǐn)?shù)據(jù)，根據(jù)每一行數(shù)據(jù)的組合，你需要使用G到H列的數(shù)字做一些計算并保存在I和J列。也可以在I和J列放入20萬個公式，但你肯定知道有這么巨大數(shù)量的公式的表格會運行很慢并且占用很大的內(nèi)存空間。
你決定嘗試用VBA代碼來解決這個難題。問題是怎樣以最有效的方法來完成這樣的任務(wù)？
什么是你的選擇
你怎樣才能掃描Excel中的一個區(qū)域，從某些單元格中讀取數(shù)據(jù)，然后更改其他某些單元格的內(nèi)容？
使用Range對象
假設(shè)你想讀取的區(qū)域是從A1開始的。代碼就看起來應(yīng)該這樣：
Dim DataRange as Range ' 也可以使用 Dim DataRange as ObjectDim Irow as LongDim MaxRows as LongDim Icol as IntegerDim MaxCols as LongDim MyVar as DoubleSet DataRange=Range("A1").CurrentRegionMaxRows= Range("A1").CurrentRegion.Rows.CountMaxCols= Range("A1").CurrentRegion.Columns.CountFor Irow=1 to MaxRowsFor icol=1 to MaxColsMyVar=DataRange(Irow,Icol)If MyVar > 0 thenMyVar=MyVar*Myvar ' 更改數(shù)值 DataRange(Irow,Icol)=MyVarEnd IfNext IcolNext Irow
使用Selection，并使用Offset移動
許多VBA開發(fā)者是從記錄宏來學(xué)習(xí)VBA技巧。
當(dāng)使用相對引用時，生成的VBA代碼如下：
ActiveCell.Offset(0, -1).Range("A1").Select結(jié)果，很多開發(fā)者采用這種方法，在代碼中使用ActiveCell或Selection區(qū)域來在單元格之間移動、讀取或?qū)懭霐?shù)據(jù)。代碼應(yīng)該就像這樣的：
Dim Irow As LongDim MaxRows As LongDim Icol As IntegerDim MaxCols As LongDim MyVar As DoubleRange("A1").SelectMaxRows = Range("A1").CurrentRegion.Rows.CountMaxCols = Range("A1").CurrentRegion.Columns.CountFor Irow = 1 To MaxRowsFor Icol = 1 To MaxColsMyVar = ActiveCell.ValueIf MyVar > 0 ThenMyVar=MyVar*Myvar ' 更改數(shù)值 ActiveCell.Value = MyVarEnd IfActiveCell.Offset(0, 1).Select ' 向右移動一列Next IcolActiveCell.Offset(1, -MaxCols).Select ' 向下移動一行并回到第一列Next Irow
使用Variant類型變量
這個方法復(fù)制區(qū)域中所有單元格的值到內(nèi)存中的一個變量，然后在變量中處理數(shù)值，如果需要的話，處理完后還可以將數(shù)字移回到該區(qū)域。
下面是這次的代碼：
Dim DataRange As VariantDim Irow As LongDim MaxRows As LongDim Icol As IntegerDim MaxCols As LongDim MyVar As DoubleDataRange = Range("A1").CurrentRegion.Value ' 不使用Set MaxRows = Range("A1").CurrentRegion.Rows.CountMaxCols = Range("A1").CurrentRegion.Columns.CountFor Irow = 1 To MaxRowsFor Icol = 1 To MaxColsMyVar = DataRange(Irow, Icol)If MyVar > 0 ThenMyVar=MyVar*Myvar ' 更改數(shù)值 DataRange(Irow, Icol) = MyVarEnd IfNext IcolNext IrowRange("A1").CurrentRegion = DataRange ' 將結(jié)果寫回到區(qū)域中
比較前兩個方法，這個方法的一個區(qū)別是速度非常之快。
性能總結(jié)
我使用相對較大的區(qū)域來比較這三個方法，下面是結(jié)果：
| 方法 | 操作 | 單元格/秒 |
| Variant | 讀 | 1,225,490 |
| 寫 | 714,286 | |
| 讀/寫 | 263,158 | |
| Range | 讀 | 250,000 |
| 寫 | 1818(?) | |
| 讀/寫 | 1,852 | |
| Offset | 讀 | 206 |
| 寫 | 200 | |
| 讀/寫 | 203 |
你可以看到使用Variant變量能夠獲得快很多的速度，尤其是改變單元格的時候。即使可以通過Excel公式來完成計算，在某些情況下這個方法是唯一可以接受的，因為使用非常大量的公式會導(dǎo)致Excel非常緩慢。 明顯需要避免的方法是使用Offset來移動ActiveCell。
