エクセルVBA 高速化②　- 値だけの処理にしたら496倍速くなった

データをコピーするとき、copy、pasteメソッドを使うと時間がかかります。セルの色や罫線など色々な情報を全てコピペしているからです。
値だけが必要ならコピーペーストをやめることで高速化ができます。

マクロ記録機能で作ったものが遅いのは、これが原因なことが多いです。

VBA高速化のベンチマークに使う処理
COPY, PASTEをやめるとVBAは高速化できる
1. 高速化したVBAのソースコード
2. 値だけ処理したら231倍速くなった
100倍の10000行で比較してみる　
1. 高速化前の処理時間
2. 値だけ処理したら496倍速くなった
まとめ

VBA高速化のベンチマークに使う処理

処理前の速度は下記記事の高速化前の状態「11.55秒」をベンチマークにします。
今回は罫線を除いて値だけをコピーすることで、どのくらいの処理速度差が出るのか計測します。

そして今回の方法以外にも高速化の方法はあります。
組み合わせることで相乗効果が得られます。

エクセルVBA 高速化③　- 配列化したら8477倍速くなった

セルを一つ一つ処理するよりも配列としてメモリ上に展開すると高速になります。慣れるまで少々難しいかもしれませんが劇的に効果があります。

COPY, PASTEをやめるとVBAは高速化できる

コピー&ペーストではなく、セルのvalueプロパティのみ書き換えます。
コピー&ペーストは罫線や色、フォントなどのすべてのプロパティ情報を変更するのに対し、対象を限定できるので高速に処理できます。
具体的には下記のような書き方にします。

Cells(i, j + 5).Value = Cells(i, j).Value

全体像は次の通りです。

高速化したVBAのソースコード

Option Explicit

Sub test_proc()
    Dim startTime As Double
    Dim endTime As Double
    Dim responceTime As Double
    
    startTime = Timer
    
    'メイン処理
    Dim i, j As Long
    For i = 1 To 100
        For j = 1 To 4
            Cells(i, j + 5).Value = Cells(i, j).Value
        Next j
    Next i
    
    endTime = Timer
    responceTime = endTime - startTime
    
    MsgBox (responceTime & "秒")
End Sub

値だけ処理したら231倍速くなった

測定結果は0.046秒でした。
11.55秒→0.05秒なので、処理速度は231倍です。

100倍の10000行で比較してみる　

高速化前の処理時間

100倍の処理量でも実行してみました。10000行です。
下記記事で計測した通り、高速化前のコードでは1101.99秒でした。
処理量が100倍になって処理時間も100倍です。

値だけ処理したら496倍速くなった

今回高速化した方では2.22秒でした。
処理量100倍なら処理時間も100倍で0.046秒→4.6秒かと思いきや、処理時間の増加は50倍ほど。
つまり処理量が増えるほど効果も大きくなっています。

高速化前が1101.99秒なので、1101.99÷2.22で処理速度496倍の効果。

まとめ

COPY, PASTEではなくValueを書き換える処理にすると高速化できる
今回の処理での効果は231～496倍の効果
※この処理での数値であって、常にこの効果が保証される訳ではありません
処理データ量が多いほど効果も期待できる

このやり方はVBAエキスパートで深めることができます。

リンク