VBAのデータが増えて処理が遅くなる件につきまして

質問

VBA初心者です。

現在勤怠表を制作しております。

csvにてデータを読み込み、計算して表にしています。

出勤時間より遅かったら「　遅刻　」と表示させていますが、

「　午前給　」や「　遅延　」などで出勤時間や勤務時間を再計算したいため、

「　Worksheet_Change　」でセルの値が変わった場合の処理を書いています。

これにより「　遅刻　」から「　午前給　」に変えると出勤時間が「　９時　」から「　１３時　」に

自動で変換されひも付いたほかのデータも変わるようにしました。

ここまではいろいろありましたが試行錯誤しできたのですが、

やたらと処理が重くなってきました。

当初は「　１５０ＫＢ　」ほどのエクセルデータが、現在は「　３５００ＫＢ　」ほどになり、

２秒で終わっていた読み込みも２分以上かかるようになりました。

そこで、自分なりに調べた結果、「　メモリーの解放　」と言うキーワードにたどり着いたのですが、

いまいちよくわかりません。

もともとのやり方が問題なのか、cavの読み込みが問題なのか、Changeイベントが問題なのか。。。

配列などはほとんど使っておりません。
for文とif文で構成されております。

長々とわかりにくい質問で申し訳ございません。

なにかヒントでもいいので教えて頂けたら幸いです。

よろしくお願い致します。

回答No.1

MARU4812

デバッグの基本中の基本テクニック１。
処理ブロック毎に処理時間を計測してボトルネックを明確にする。

Private tm As Single '処理時間計測用タイマー
Debug.Print Format$(Now, "hh:nn:ss") & " Start"
tm = Timer
計測したい処理
Debug.Print Format$(Now, "hh:nn:ss") & " End :" & Timer - tm

デバッグの基本中の基本テクニック２。
>「　Worksheet_Change　」でセルの値が変わった場合の処理を書いています。
一度この処理を全てコメントアウトして処理が速くなったらここがボトルネック。

> 当初は「　１５０ＫＢ　」ほどのエクセルデータが、現在は「　３５００ＫＢ　」ほどになり、
> ２秒で終わっていた読み込みも２分以上かかるようになりました。
単純計算で３５００÷１５０＝２３．３３
２×２３＝４６
もともと２秒で終わっていたのも既に遅いのかもしれないし。

> 配列などはほとんど使っておりません。
ボトルネックになっているなら1箇所で使っているだけで十分でしょう？
「ほとんど」とかいう言葉に意味が無いです。
配列がボトルネックだと判明した場合、Redim してたら遅くなる可能性が非常に高いです。
経験上、関数を抜けるまで配列で使用したメモリは解放されません。メモリを解放する
手段はありませんので、そもそものメモリ使用を最小限に抑えるコーディングが必要です。

お礼

ご回答ありがとうございます！

まずはどこが原因かを教えて頂いた方法で調べてみたいと思います。

初心者なもので、コーディング方法ももっとシンプルにできるのでは？と疑問を持ちながら日々努力しています。

ありがとうございました！！