同じマクロのプログラムなのに処理速度が違う

エクセルのマクロについての質問です。
あるシートの一番左列に1~300の値を順に入れるプログラムをfor文で作成しました。
しかし、ファイルによっては処理速度が異なります。（別のプログラムでも同様）
ファイルの違いはデータ量で、シートにたくさんのデータがあるファイルほど
遅くなります。
　これは仕方ないことなのでしょうか？コードを改善しても無駄でしょうか？
よろしくお願いいたします。

No.5 ベストアンサー 回答者： ママチャリ 回答日時： 2016/10/08 14:35

計算方法を自動計算にしているとA列に値を入れる都度、再計算が行われるので負担になる場合があります。

そこで、計算方法を手動計算にしてから目的の処理を行い、終了時に自動計算に戻すようにすると場合によっては早くなるかもしれませんので、お試しください。
自動、手動の切り替えはこんな感じです。

Application.Calculation = xlManual '手動にする
　・
　・
Application.Calculation = xlAutomatic '自動に戻す

この回答へのお礼

ありがとうございます。試してみたいと思います。

お礼日時：2016/10/08 14:40

No.6 回答者： baet 回答日時： 2016/10/08 23:07

' 配列を作成し、範囲に一度で投入します

Dim a(1 To 300) As Variant
Dim n As Long
For n = LBound(a) To UBound(a)
　a(n) = "=VLOOKUP(B" & CStr(n) & ",C:C,1,FALSE)"
Next n
Me.Range("A1:A300").Formula = a
' 範囲の FormulaR1C1 に一度で投入します
Me.Range("A1:A300").FormulaR1C1 = "=VLOOKUP(RC[1],C[2],1,FALSE)"

この回答へのお礼

ありがとうございます。当方は単純なものしか記述できないため、ご指摘のコードは大変難しいです。variant型もlong型も使ったことがありません。
いろいろ調べましたが、添え字の初めの整数と最後の整数を取得してfor文でaに格納し、一度に書き出す…ということだと思います。
大変勉強になります。

お礼日時：2016/10/10 00:44

No.4 回答者： t_fumiaki 回答日時： 2016/10/08 12:20

no.3

可能性はもう１個。
オートシェイプが増殖してませんか？
セルやシートのコピペを行なったり、更新するとどんどん増えます。
一度シェイプ全削除して見て下さい。
（画像も消えるのが難点）

For Each oShape In ActiveSheet.Shapes
oShape.Delete
Next

この回答へのお礼

たびたびありがとうございます。幸いオートシェイプは使っておらず、単純にセルの値を参照して書き出しをするだけの処理になります。ただ、そのセルはVLOOKUP関数など、すべてのセルに関数が入っております。しかもifで分岐するなど、かなり複雑になっております。これが遅くなる原因になるでしょうか。

お礼日時：2016/10/08 12:34

No.3 回答者： t_fumiaki 回答日時： 2016/10/08 12:13

エクセルに値を入れると、入れた値が見えます。

2と入れると、セルに２が表示されます。

これはエクセルがセル内容の表示を更新表示しているからです。
データ量が多くなればなるほど、いちいちセル内容を表示更新するのですから、スピードがガクンと遅くなります。

セルの値を更新したり入力している時には、表示更新をstopしておけばスピードが速くなります。

Application.ScreenUpdating = False　：　表示更新停止
Application.ScreenUpdating = true　：　表示更新再開

この回答へのお礼

ありがとうございます。そういう手があるとは知りませんでした。
試したいと思います。

お礼日時：2016/10/08 12:19

No.2 回答者： daaa- 回答日時： 2016/10/08 11:48

エクセルのマクロは知りませんが、そのデータファイルをメモリー上に読み込み、一気に検索させるようにはできないのですか？　それがこういう場合、普通の高速化手段だと思います。

マクロではありませんが、RAMDISK上にコピーするとかもありかな？

この回答へのお礼

ありがとうございます。
配列変数はせいぜい1~2で、その配列は多くても15程度です。ですのでメモリの読み込みは多くはないかと思います。
特にfor文で、他シートを参照して書き込む　を繰り返し、条件に合わなかったらfor文を抜け出す、といった処理が遅く、困っております。

お礼日時：2016/10/08 12:08

No.1 回答者： chiaki0001 回答日時： 2016/10/08 11:25

仕方ないと思います

参照データが多いために起こる処理でしょうね

薄い辞書ならすぐ引き出せるけど分厚い辞書なら引くのも見つけるまでに時間がかかるのと一緒ですね

この回答へのお礼

ありがとうございます。やはりそうですよね。特に他シートから参照するプログラムで処理速度が顕著に遅くなります。

お礼日時：2016/10/08 11:35