値貼り付けは遅い

---

セルに数式が入力されていて。その数式をセルごとコピーするのではなく、数式の計算結果だけを別のセルに代入することがよくあります。もう、実務では超"あるある"の話です。これ、もし手動でやるのなら、おそらく日本国民全員が「形式を選択して貼り付け」の、いわゆる"値貼り付け"を実行します。ああ、もちろん、[貼り付けオプション]の「値」を実行するのも、やってることは同じですよ。

さて、この"値貼り付け"が遅いってご存じでしたか？実務で何回も値貼り付けを繰り返していたら、そりゃマクロも遅くなりますよ。やってみましょうか。

セル範囲A1:A500に適当な数値を入れました。隣のセル範囲B1:B500に「=A1*2」的な数式を入れました。このセル範囲B1:B500の計算結果(値)を、1つずつ隣のC列に"値貼り付け"していきます。本当は、このように連続した範囲でしたら、複数のセルを一気にやるんですけど、今回はそういう話ではありません。"値貼り付け"を500回繰り返したら、という検証です。まずは、10回試した結果をご覧ください。

Sub Macro1()
    Dim i As Long
    For i = 1 To 500
        Cells(i, 2).Copy
        Cells(i, 3).PasteSpecial xlPasteValues
    Next i
End Sub

回数	値貼り付け
1	63.547
2	69.281
3	68.781
4	68.094
5	67.188
6	65.844
7	69.671
8	68.703
9	69.562
10	71.031
平均	68.170

"値貼り付け"を実行すると、どうしてもアクティブセルが移動します。つまり、画面が変化します。こんなときは、画面の更新を抑止するとマクロの実行速度が上がります。本来であれば、アクティブセルが移動するようなコードそのものが「それって、ど～よ」って感じなんですけど、一応画面更新を抑止したコードも試してみました。

Sub Macro2()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To 500
        Cells(i, 2).Copy
        Cells(i, 3).PasteSpecial xlPasteValues
    Next i
    Application.ScreenUpdating = True
End Sub

回数	値貼り付け	値貼り付け (画面抑止)
1	63.547	19.812
2	69.281	17.656
3	68.781	21.625
4	68.094	18.422
5	67.188	20.766
6	65.844	17.375
7	69.671	20.328
8	68.703	18.125
9	69.562	18.844
10	71.031	18.437
平均	68.170	19.139

この結果を見て「おお！やっぱ画面を止めるとマクロは速くなるんだぁ！」なんて喜ばないでくださいね。本題はここからです。

計算式の結果を別のセルに"値貼り付け"するって、要するにどういうことですか？計算式の結果を、別のセルに"代入"するってことでしょ。セルに何かを代入するのは「セル.Value = 何か」と書けば済みます。さらに、セルに入力した計算式の結果って、それってValueプロパティですよね。ですから、みなさんが手動でやる"値貼り付け"ってのは、VBA的には値(Value)の代入なんです。上記と同じ、500個の数式が入力されているセルの、値(Value)を別のセルに代入すると、次のような結果になります。

Sub Macro3()
    Dim i As Long
    For i = 1 To 500
        Cells(i, 3).Value = Cells(i, 2).Value
    Next i
End Sub

回数	値貼り付け	値貼り付け (画面抑止)	Valueの代入
1	63.547	19.812	0.562
2	69.281	17.656	0.547
3	68.781	21.625	0.688
4	68.094	18.422	0.703
5	67.188	20.766	0.719
6	65.844	17.375	0.750
7	69.671	20.328	0.688
8	68.703	18.125	0.593
9	69.562	18.844	0.547
10	71.031	18.437	0.515
平均	68.170	19.139	0.631

あなたは、これでもまだ"値貼り付け"を繰り返しますか？

私のセミナーで毎回言っていますが、マクロとは「手動操作の高速化」じゃないんです。Excelに対して手動操作でやることを、そのまま速く実行することがマクロではありません。マクロとは、みなさんがExcelに指示・命令をすることです。そのためにVBAというマクロ言語が実装されているんです。くれぐれも「手動操作の高速化」をしないようにしてくださいね。