VBA高速化にはセルにアクセスせず配列を使え？

質問

VBAを高速化するにはセルにアクセスせず配列を使えといいますが、
シート中に沢山の計算式が複雑に絡み合っている（沢山のセルを参照した計算式が多数ある）ものを扱う場合には、セルに入っている値を変更しなくては、なにも始まらないです。シート自体を2次元配列のような仮想シートみたいなものにごっそり入れて、まるで本当のシートのように、セル中に格納された計算式も機能し、かつ高速処理になる方法ってあるのでしょうか？

回答No.1

keithin · ベストアンサー

＞セルにアクセスせず配列を使え

セルを一個一個巡回しては値を記入し、また一個ずつセルを巡っては計算結果を取り出すみたいな事をしていると、確かにとても遅いです。

＞シート中に沢山の計算式が複雑に絡み合っている

計算式を含むワークシートを仮想化するみたいな、そんな機能は残念ながらありません。

現実的には
○入力しなきゃならないセル（＝計算式のインプットデータになるセル）に、一度にまとめて一斉に必要な値を投入する（できるようにセルの配置やインプットデータを工夫する）
○計算結果のセルをまとめて取得する（出来るように計算セルの配置を考える）
といった工夫をします。

＞沢山のセルを参照した計算式が多数ある

ＶＢＡの中で数式をエミュレートして計算させる事も、やろうと思えばできます。が、ワークシートに計算させた方が早いです。
工夫代の中には、マクロでやろうとしている事を数式で実現できないか工夫するといったアプローチも含まれます。

お礼

なるほどです。ありがとうございました。

回答No.3

Gotthold

配列や変数を使うというのは、
・同じセルを複数回参照する
とか
・マクロ処理の途中経過をいったんセルに入れる
とかいうことを避けるようにという意味合いです。

マクロ処理の最終結果をセルに入れるところはセルへのアクセスはどうしても必要ですから
配列を使ってセルへのアクセス回避はできません。
（No.1の回答のように、そもそもセルへのアクセス数が少なくなるように構成自体を見直すことができれば良いのですが）

> (Application.ScreenUpdating =False)はすでに使用していますが、残念ながら24時間以上かかってしまいます。
これは画面更新は止まりますが、セルを更新するたびに再計算は発生してしまいます。
セル更新の回数が多いのであれば、

処理の前に
Application.Calculation = xlCalculationManual
で自動計算を停止し、
処理が終わってから
Application.Calculate
で再計算、
Application.Calculation = xlCalculationAutomatic
で自動計算再開
すれば良いと思います。

【参考】
自動計算を止める
http://www.smilecolor.net/tips.php?id=ex&num=95

お礼

なるほどです。ですが残念ながら、そもそも”セルを更新するたびに再計算を行った結果を評価する”　というプロセスを何度も行わなければいけない仕様となっておりますので、今回のプログラムに適用はむずかしいです。ありがとうございました。

回答No.2

WindFaller

>VBAを高速化するにはセルにアクセスせず配列を使えといいますが、
この場合は、セルはオブジェクトであり、値が変化すれば、画面が変化するので、一般的には速度が遅くなるわけで、Rangeオブジェクトの値を、配列変数に代入できれば速くはなります。しかし、思ったほどではありません。私の実験では、せいぜい、1万個～3万個ぐらいのセルに対しては有効だったという記憶があります。(現在はどうかは知りません）

>シート中に沢山の計算式が複雑に絡み合っている（沢山のセルを参照した計算式が多数ある）

ワークシートの反応が遅ければ、VBAで速くなることがある、という程度です。実際、ワークシートの方が速いかどうかは、数式次第ですね。ワークシートで配列を使う場合などは、VBAの方が速いです。何れにしても、画面の変化を止める(Application.ScreenUpdating =False)としていれば、そんなに大きく違わないはずです。

お礼

そんなにかわらないのですね　なるほど。
(Application.ScreenUpdating =False)はすでに使用していますが、残念ながら24時間以上かかってしまいます。ありがとうございました。