マクロの処理速度を上げたい

質問

例えば"A1"から"Z100"までランダムに数字が入っている場合に、
次の処置をしたいのです。
　処理Ａ：１０以下の数値を０に置き換える。
"A1"から始まるのはほぼ決まっているのですが、
最後のセルが決まっていません。
いろいろなときに使えるように下記マクロを作りましたが、
データ量が多いのか、処理に時間がかかってしまいます。
(本番データは200×3000件くらいあり、処理も例より複雑です)

処理は行えているみたいなので、速度を上げるのにいい方法はないか
相談させてください。

プログラムの知識は基本情報処理試験のC言語を勉強していた
くらいです。マクロは本を見ながら作っているので、調べきれて
いない部分もあるかもしれませんが、よろしくお願いいたします。

参考にしている本は、C&R研究所のEXCEL VBAハンドブックです。

＝＝＝＝ここから＝＝＝＝
sub テスト()
'使用する変数
　dim LastColNum as integer
　dim i as integer

　'Ａ１をアクティブにします
　range("A1").select
　'最後の列の行番号を調べます
　LastColNum=range("A1").end(xlToRight).column

　'アクティブなセルが空白になるまで処理を続けます
　Do Until activecell.value=""

　　'行数分処理を続けます
　　for i=0 to LastColNum-1
　　　'10未満だったら処理をします
　　　if activecell.offset(0,i).value<10 then
　　　　'１０未満のセルに０を入力します。
　　　　activecell.offset(0.i).value=0
　　　end if
　　next
　　'次の行をアクティブにします
　　activecell.offset(1,0).activate
　Loop
end

回答No.2

KenKen_SP

こんにちは。KenKen_SP です。

Excel VBA はコードのなかに Select が多くあると処理に時間がかかります。
例えば、マクロの記録で生成されるコードでは、次のように無駄な Select
が多くみられます。

Range("A1").Select
Selection.Copy

これは、 Range("A1").Copy の1行で書くことができ、こうすることで、処理
速度の遅い Select をしないで済みますし、また、コードが2行から1行になっ
たことで、Excel が処理する命令数が減ったことで処理速度が向上します。

よく、ループ処理のなかで Select を多用するコードを見受けますが、これを
見直すだけで随分と処理速度が変わります。

これが、VBA の処理速度を向上させるための基本テクニックです。

以下のサンプルは、セルへ最初のデータ取得時と書き出し時の2回しかアクセス
しません。ループでセルのデータをひとつひとつ拾うのではなく、配列で一気に
データを取得します。

そして、時間のかかるセル上で10未満か比較するのではなく、配列で（メモリ上）
で比較処理しています。

Sub Sample()

　　Dim Buf　　　As Variant
　　Dim varTmp　 As Variant
　　Dim lRowCnt　As Long
　　Dim lColCnt　As Long
　　Dim DataArea As Range
　　Dim i As Long, j As Long

　　'データ範囲取得
　　Set DataArea = ActiveSheet.UsedRange

　　'範囲を明示したいなら次のように変更
　　'もちろん最終行/最終列を取得してRange("A1:Z100")
　　'の部分を指定してもOK
　　'Set DataArea = ActiveSheet.Range("A1:Z100")

　　'配列にバッファ
　　Buf = DataArea.Value
　　'行数/列数取得
　　lRowCnt = UBound(Buf)
　　lColCnt = UBound(Buf, 2)
　　'ループ
　　For i = 1 To lRowCnt
　　　　For j = 1 To lColCnt
　　　　　　'Emptyでなく、数値で10未満なら0に
　　　　　　varTmp = Buf(i, j)
　　　　　　If Not IsEmpty(varTmp) Then
　　　　　　　　If IsNumeric(vatTmp) And varTmp < 10 Then
　　　　　　　　　　Buf(i, j) = 0
　　　　　　　　End If
　　　　　　End If
　　　　Next j
　　Next i
　　'データを配列からセルに戻す
　　DataArea.Value = Buf
　　'オブジェクト変数クリア
　　Set DataArea = Nothing

End Sub

お礼

ありがとうございます。

私もマクロの記録などで作ることが多いため、
いらないところが多いみたいですね。

配列とかはちょっと難しいので、もう少し成長したら
勉強してみたいです。