セルのコピー
[A1]の値が条件に合致したら[B1]にコピーする。これを[Ax]まで繰り返す。
VBAプログラムでは、ごく一般的なロジックです。
一般的なロジックだからこそ、性能を見直すべき大きなポイントでもあります。
今回はセルの値をコピーする性能について、検証します。
- For文で、Valueを代入
- アドレス指定して、Valueを代入
- アドレス指定して、Copyメソッド
- アドレス指定して、PasteSpecialメソッド
[A:A]の値を[B:B]にコピーするロジックを、以上4つのパターンに分けて検証します。
For文で、Valueを代入 サンプルプログラム
範囲[A:A]までをループさせ、Offset(, 1)で隣のセルに値を代入しています。
代入の前に、条件式を入れると、どこにでもある一般的なロジックですね。
アドレス指定して、Valueを代入 サンプルプログラム
ごく単純な値の代入です。
特に条件がなくコピー元セル範囲とコピー先セル範囲が決まっている場合や、条件で絞った後に一括でコピーする場合等で、この様な書き方になると思われます。
アドレス指定して、Copyメソッド サンプルプログラム
値の代入ではなく、セルのコピー&ペーストをする方法です。
RangeオブジェクトのCopyメソッドを使用し、引数にコピー先セルを指定しています。
EXCELで操作する際は、セルをコピーして、ペースト先セルを選択してEnterキーを押したときと同じ動きをします。
アドレス指定して、PasteSpecialメソッド サンプルプログラム
形式を選択して貼り付けをする方法です。
コピー元のセルのRangeオブジェクトのCopyメソッドを実行した後に、コピー先のセルのRangeオブジェクトのPasteSpecialメソッドを実行する流れとなります。
また、PasteSpecialメソッドは、セルをコピーして、ペースト先セルを選択してCtrl + Vキーを押したときと同じ動きをしますので、Application.CutCopyMode = Falseにてコピー状態を解除する必要があります。
ここでは、よく使用するであろう、値の貼り付けと書式の貼り付けの両方を実行し、検証をします。
検証
上記4つのパターンについての実行結果は以下の通りです。
回数 | For | Value | Copy | PasteSpecial |
---|---|---|---|---|
1回目 | 2.063 | 0.047 | 0.016 | 0.016 |
2回目 | 2.813 | 0.047 | 0.016 | 0.031 |
3回目 | 2.781 | 0.032 | 0.016 | 0.032 |
4回目 | 2.828 | 0.031 | 0.000 | 0.032 |
5回目 | 2.797 | 0.046 | 0.000 | 0.063 |
6回目 | 2.813 | 0.047 | 0.016 | 0.032 |
7回目 | 2.828 | 0.032 | 0.016 | 0.016 |
8回目 | 2.797 | 0.094 | 0.016 | 0.047 |
9回目 | 2.813 | 0.031 | 0.000 | 0.063 |
10回目 | 2.906 | 0.047 | 0.016 | 0.016 |
平均 | 2.744 | 0.045 | 0.011 | 0.035 |
※単位は全て『秒』
今回の検証によりわかることは、ループさせて一つ一つコピーするより、一括でコピーするほうが速いということです。
最速はCopyメソッドで、For文と比べて0.4%も速くなっています。
5分かかっていたマクロが、1秒で終わります。この差は脅威ですね。
絶対に対応すべき点の一つでもあります。