VBA 配列とワークシートどっちがいい？

質問

数値計算するマクロを作っていて思うことがあり質問しました。
ワークシートに3万セルのデータがあり、それを元に計算し、結果を別シートに記入します。
(1)計算をすべてワークシート上で行う方法
(2)データを全て配列に読み込み、配列上で計算し、結果のみ配列に書き出す方法
どちらがいいの？

考えや情報が色々と頭の中でぐるぐる回ってます。
・せっかくの「表計算」ソフトなのだから当然(1)？
・シート上の方が色々な関数を使える
・配列上の方が計算速いと聞く
・３万セルごときどちらも同じ？
・１０万セルの配列読み込み11秒（For Next）2秒（Range型変数使用）の書籍記事
・１００万セルの配列読み込みコンマ数秒（For Next）コンマゼロ数秒（Range型）のweb上記事

今まで私自身が体感したのは、例え３万個といえど
・行削除が多いと遅い
・コピペが多いと遅い（５千回で１０秒）
・ファイル数が多いと遅い（open/close１個１秒弱）
・シート上だと関数はやっぱり豊富
・セルへのアクセスは不明？？

どっちがいいですか？100万セルでも同じですか？
どっちでやってますか？

回答No.1

keithin · ベストアンサー

基本のその１は「セルを読み書きする回数」が少ないほど早い、です。
基本のその２は、エクセルのワークシート関数で計算する方が、ＶＢＡで同じ計算をするよりも遥かに速い、です。

たとえば、ＶＢＡ初心者さんが非常に多く陥りがちな例として
sub macro1()
　dim r as long
　for r = 1 to 9999
　　cells(r, "B") = cells(r, "A") * 2
　next r
end sub
みたいな具合に、対象のセル「全部を一つずつ舐めまわすように」一個一個丁寧に、合計9999回の出し入れをします。

これを
sub macro2()
range("B1:B9999").formula = "=A1*2"
end sub
みたいに合計9999個のセル範囲に「一回で」処理を行うことで、各段の高速化が図れます。

これに加えて、セルを触る（行削除・行挿入等含む）ことで、シート上に既存の関数セルの内で「再計算を余儀なくされる数式」の多寡が問題となるケースも考えられます。

たとえば
=SUM(C:C)
のような関数がシート上のどこかに（しかも沢山）記入されていたとすると、マクロが行の挿入削除を行えば、関数はマクロの実行に「割り込んで」再計算を走らせます。
その関数が一個二個なら実用上問題ありませんが、リストの上から下まで漏れなくこういった関数が記入されていたり、それぞれの関数式がまた効率が悪くて計算が遅かったりすると（こういう状況は非常にしばしば発生します）、目も当てられない反応の遅さに直結します。

ご質問の直接の回答として
＞(1)計算をすべてワークシート上で行う方法
＞(2)データを全て配列に読み込み、配列上で計算し、結果のみ配列に書き出す方法
＞どちらがいいの？

これはワークシートで行う計算式の、効率次第です。
数式が単純なら、ワークシート上で計算すべきです。
しかし何某か非常に複雑な計算式を頑張って作成していた場合などでは、マクロによって配列で計算を行い、「結果を一発で転記する」ことで高速化する事も十分あり得る話となります。

＞・せっかくの「表計算」ソフトなのだから当然(1)？

単純な計算ならその通りです。

＞・シート上の方が色々な関数を使える

シート上で使える関数のほとんどは、ＶＢＡでも利用できます。しかしその関数単独であれば、シート上で計算したほうが高速です。

＞・配列上の方が計算速いと聞く
＞・３万セルごときどちらも同じ？

計算の内容次第です。
たとえば
Sub 比較()
　Dim a As Variant, res As Variant, x As Variant
'準備
　Range("A1:A50000").Formula = "=ROW()"
　Range("A1:A50000").Value = Range("A1:A50000").Value

'ワークシート計算
　Range("C1") = Timer
　Range("B1:B50000").Formula = "=A1*2"
　Range("D1") = Timer

'配列に一括取得
　Range("C2") = Timer
　a = Application.Transpose(Range("A1:A50000").Value)
　res = a
　For x = 1 To UBound(a)
　　res(x) = a(x) * 2
　Next x
　Range("B1:B50000") = Application.Transpose(res)
　Range("D2") = Timer

End Sub
といった具合に比較してみると、ワークシートで計算したほうが３倍高速であるといった結果になります。

＞・１０万セルの配列読み込み11秒（For Next）2秒（Range型変数使用）の書籍記事
＞・１００万セルの配列読み込みコンマ数秒（For Next）コンマゼロ数秒（Range型）のweb上記事

前述した通り、１０万セルを「一個ずつちんたら読み込み」してたのでは、ＶＢＡだの配列を使う以前の問題です。

お礼

ありがとうございます。
配列計算ではループで回してるので、セル計算でもループで回してみたら、40倍遅くなりました。
セルへのアクセスは一気にやった方がいいようですね。

ただ、回答いただいたセル上で一気に書く方法はFormulaで"=A1*2"のように単純なので表現出来ましたが、長い式になると可読性の問題が出そうです。総合的には配列に読み込み計算し一気にセルに記述するのがよさそうです。