繰返し計算の高速化の方法をご指南願います

質問

いつも大変お世話になり有難うございます。
VBA初心者です。
早速ですがお尋ねします。
VBAで毎回条件変えて繰返すプログラムを作りましたが終了まで数時間かかり困ってます。
プログラムの全体の流れは、
約25000個のデータをあるパラメータで計算→計算結果とその時のパラメータをセル（i、1～9）に記入→パラメータを変更→約25000個のデータをあるパラメータで計算→計算結果とその時のパラメータをセル（i+1、1～9）に記入→・・・
というものです。繰り返し回数は1万回前後です。パラメータをもとに計算する式は簡単な加減則で、文字数の都合もあり割愛いたします。
試行錯誤の結果、
1.繰り返し計算の途中にある以下の多くのセル記入文の部分
2.1列目のセルに(IF(C77=100,"測定終了",IF(C77-J77>0,"下降","上昇")))、(IF(C78=100,"測定終了",IF(C78-J78>0,"下降","上昇")))・・・の式が約25000行に渡りは入っている事
3.2～4列目に時間、測定データ1、測定データ2が約25000個ずつセルに入っている事
がネックと分かりました（ファイル容量約3MB）。

Ｑ1、以下のセル記入方法を改善して高速化できる方法があればご指南いただけないでしょうか？
なおセルへの記入はWorksheets(1)にあるグラフに使用するのと、記入されたデータを後でソートして傾向を見るためのものです。また以下Ｒangeの値は一回の計算毎に変化するものです。

Range("J74") = Application.WorksheetFunction.Sum(Range("J76:J30000"))
Range("K74") = Application.WorksheetFunction.Sum(Range("K76:K30000"))
Range("L74") = Application.WorksheetFunction.Count(Range("J76:J30000"))
Range("M74") = Application.WorksheetFunction.Count(Range("K76:K30000"))
Worksheets(2).Cells(i, 1).Value = i - 1
Worksheets(2).Cells(i, 2).Value = Range("J74")
Worksheets(2).Cells(i, 3).Value = Range("K74")
Worksheets(2).Cells(i, 4).Value = Range("L74")
Worksheets(2).Cells(i, 5).Value = Range("M74")
Worksheets(2).Cells(i, 6).Value = Range("B32")
Worksheets(2).Cells(i, 7).Value = Range("B33")
Worksheets(2).Cells(i, 8).Value = Range("B31")
Worksheets(2).Cells(i, 9).Value = Range("B34")

Q2、(IF(C77=100,"測定終了",IF(C77-J77>0,"下降","上昇")))・・・、の式によりプログラムが遅くならない方法は考えられるでしょうか？（一回の計算毎に”下降””上昇”の数をcountしているため計算無効にすると困ります）

Ｑ3、時間と測定データ１，２は繰返し計算のプログラムで使用する元データのため消してはまずいのですが、データの多さがプログラム速度に悪影響与えない方法はあるでしょうか？

画面更新無効などは設定しています。
winXP、excel2002、cpu2.4GHz、RAM1GB、です。
字数の都合で前後式は省略してます。すみません。
ネットや書籍で調べ、当初より多少は速度が改善されたのですが、上記問題を残し力尽きてしまいました。
質問のうち一つでも結構ですので、ご指南よろしくお願いします。

回答No.2

pulsa · ベストアンサー

初心者と言ってますが、そうでもないですよね
処理の高速化にあたり、画面の更新が処理の足を引っ張る事に行き着くのは、そうたやすい事では無いと思います
相当努力されたのでしょう

さて本題
画面の更新を止める事により処理が高速化するのは、つまり画面の描画には時間が掛かる事を意味しています
逆に画面に変化を起こさせ無ければ処理を高速化させる事ができます
セルへの入力は文字を表示させる以外にフォントや書式、罫線など計算処理に不要な項目が多く含まれる為、ただ文字を表示させただけでも、エクセル上では複雑な処理が発生しています
画面の更新を止める事で、再描画に必要なリソースを節約できるのですが、逆に言うと表示していないだけで、画面の再描画が命令されたときすぐに表示できるように、画面に現れないだけで、裏ではセルに値を書き込みに行っています
この書き込み処理自体を減らす事で、更なる高速化が可能です

こちらをお試し下さい
　　Dim iCnt As Integer
　　For iCnt = 1 To 32000
　　　　Range("A1") = Range("A1") + 1
　　Next iCnt

次にこちらをお試し下さい
　　Dim iCnt As Integer
　　Dim jCnt As Integer
　　For iCnt = 1 To 32000
　　　　jCnt = jCnt + 1
　　Next iCnt
　　Range("A2") = jCnt

同じ内容を実行しているのに、その処理時間の違いに驚くハズです
画面の更新を停止しても、上の処理が下の処理と遜色無いレベルには到底なりません
このように、一旦値を変数に格納して処理し、最後書き出すようにするだけで、劇的に高速化します

次に、配列はご存知ですか？
配列自体はここで説明できるほど浅くないので、説明はネットを色々見て体得していただくとして、今回の処理に利用することで言うと、2次配列はそのままエクセルシートと対応させる事ができるので、上の例と同様、毎回行っているセルへ書き出す処理を配列を使用して計算させ、最後一気にシートに出力させる事で、処理の高速化が望めます

こちらを試してください
　　Dim iCnt As Integer
　　Dim jCnt As Integer

　　For iCnt = 1 To 100
　　　　For jCnt = 1 To 100
　　　　　　Cells(iCnt, jCnt) = iCnt + jCnt
　　　　Next jCnt
　　Next iCnt

次にこちらを試して下さい
　　Dim iCnt As Integer
　　Dim jCnt As Integer

　　Dim MyCellR(1 To 100, 1 To 100)

　　For iCnt = 1 To 100
　　　　For jCnt = 1 To 100
　　　　　　MyCellR(iCnt, jCnt) = iCnt + jCnt
　　　　Next jCnt
　　Next iCnt
　　Range(Cells(1, 1), Cells(100, 100)) = MyCellR

ポイントは配列を (1 to 100)と1から作成している所で、通常は配列のインデックスは0から始まるのですが、それを1から始める事でセルの変わりに利用する利便性を上げています

最後に各セルに入れてある関数を解決します
Evaluateメソッドを利用します
これは、平たく言うとセルに入力するような値を文字列として入力して使う事が出来るメソッドです
使い方はこんな感じ

先ほどの実験ロジック実行後の値が入っているシートを表示した状態で試して下さい
MsgBox Application.Evaluate("=SUM(A1:A100)")
かっこの中は文字列で、上記の体裁を取っていればOKですので、今関数が入っているセルを直接指定して関数的に利用する事が出来ます
(Range("J74")に関数が入っていると仮定して)
MsgBox Application.Evaluate(Range("J74"))

これら、配列+Evaluate　でロジックを組みなおせば、かなり高速化できると思います
示されたコードでは、ロジックを組むには情報が足りませんので、具体的なコードは示せませんが、手法としてはこんな感じで高速化が可能です
配列は、逆に言うと処理状況の確認が難しくなるので、最初戸惑うかもしれませんが、ようは画面に出さないシートと思って使えばイメージしやすいと思います（Cells(1,1)=MyCellR(1,1)と脳内補完する）

画面の更新やシートをSelectせずに利用する方法に行き着けたあなたならきっと出来ます
がんばって！！

お礼

返答遅くなりすみません。
良質な実用書のようなご回答有難うございます。
最後に激励までして頂き感動です。VBAは興味持ち出し1年未満で、且つ我流なため自信なかったのですが、とても励みになりました。
今まで悶々としていた問題が解決できると思うと、ご回答内容を見ているだけでワクワクしてきます。

本題ですが、
　画面更新を止めても裏では表示準備しているため結構負荷は残っているのですね。なるほど理解が深まりました。早速サンプルプログラムで検証させて頂きます。
　配列に関しては、データ数が多いとき高速化に有効ということをネットで見た程度で「ところで、配列はどうやって使用するの・・・？」という印象でした。なので、この配列のサンプルプログラムも大変参考になります。これを機に何とか使えるようにしたいと思います。
　ここまでのイメージとして、Range("**")に替わるプログラム方法を用いることでPCは計算のみに集中、最後にまとめて表示で高速化、という様なかんじでしょうか。
　evaluateメソッド・・。手元のVBA参考書には載ってないです。本当に勉強になります。ちょっとまだ私の力量不足で、evaluateのイメージが沸いてきませんが、ご回答文章を呼び水にして調べてみたいと思います。

私の質問文が内容不足にも関わらず、非常に的確なご回答有難うございます。（まるで私の後ろからプログラムを実際に見ながらアドバイスもらっているかのようで、不思議な感覚でした）
多少時間を要しそうですが検証結果は追って報告させて頂きます。では失礼致します。

補足

どうやら質問を締め切ると結果報告はできなさそうです・・。すみません。今回はどうも有難うございました。

回答No.3

Harusir

ロジカル的な解決方法は他の回答にまかせるとして、処理中はエクセルを最小化しておき、処理が終了後に元に戻す事で、速度向上に繋がります。一度試して見てください。

'ワークブックを最小化
ActiveWindow.WindowState = xlMinimized

'～処理を記述

'ワークブックを最大化
ActiveWindow.WindowState = xlMaximized

参考URL：

http://www.happy2-island.com/excelsmile/smile03/capter00306.shtml

お礼

ご回答有難うございます。
この方法は知りませんでした。
これだけで簡単に高速化できるのですね。
是非試してみたいと思います。
どうもありがとうございました。

回答No.1

okormazd

1
関数の入ったセルが多数あるため、その再計算で時間がかかっていると思います。質問のデータだけでは関数の入力されたセルを減らしたり、再計算しなくてすむのかどうかを判断できません。これを減らすことが決定的と思います。
2.
セルへの書き込み回数が多いかもしれません。セルへの逐次書き込みを減らせれば速度は向上します。

お礼

返答遅くなり申し訳ありません。
ご回答ありがとうございます。
詳細までプログラムを書いてなくて申し訳ございません。
仰るとおりセルに入力された関数と、逐次書き込みが高速化のネックになっていることは確かです（正しい計算結果は出力されなくなりますが、試しにセル内の関数や逐次書き込み文を消去してプログラム走らせると、3割程度計算時間が短縮されます）。正しい計算結果をだしつつこれらの処理を減らす方法も考えていきたいと思います。どうも有難うございました。