ExcelVBAを高速化する7つの方法
富士通さんがExcelで分散環境にアクセスするソリューション()を開発してくれたけれども、それに対してVBA()みたいな反応が多かったので作成してみました。Excel上からVBAを扱おうとするとシングルスレッドではあるものの、十分な速度が出せると思っています。速度が出ない場合は殆どが不適切なコーディングによるものです。以下に高速化するための7つのTIPSを載せます。
セルにできる限りアクセスしない
ExcelはなぜかCellオブジェクトにアクセスするのが非常に遅いです。そのため、Cellオブジェクトには出来る限りアクセスしないほうが良いです。Excelの最後のシートに設定を書き込んだものを用意するとかやる場合がありますが、値が数千回読み込まれるならば一度VBAで全部読み込んでしまって使いまわしたほうが高速化出来る可能性があります。
とりあえずお勧めなのがDictionaryクラス(いわゆるHashTableみたいなの)を使う方法です。メニューから参照設定を追加します。
アドイン→参照設定→Microsoft Scripting Runtimeにチェック
あとはこんな感じ。
Dim dic As New Dictionary
Dim value As String
dic.Add "key", "item"
value = dic.Item("key")
最初に設定内容をDictionary内に読み込んであげてください。
CreateObjectを使用する方法もありますが、存在するメソッドを確認できないのでお勧めしません。*1
コンパイルを行う
あまり意識している人は少ないと思いますが、VBAはコンパイルが必須です。コンパイルができない場合殆どの場合にVBAの実行ができないのですが、たまに実行ができてしまいます。ただし、その場合の実行速度は体感で10倍以上も遅いです。いつの間にか遅くなっていた場合はコンパイルができるかどうか確かめてみたほうがいいかもしれません。
メニューから、
デバッグ→VBAProjectのコンパイル
でコンパイルすることができます。
コンパイルエラーが出ていたら適切に修正してあげてください。何が適切かについては知りません。
区切り位置指定ウィザードを使わない
マクロの記録を使用してVBAが組まれている場合にたまにあるのですが、区切り位置指定ウィザードの動作は非常に遅いです。自前でパーサーを作ってください。ちなみにVBAでも正規表現は使えます。
アドイン→参照設定→Microsoft VBScript Regular Expressionx X.X
あたりを追加してください。
そうしたらRegExpオブジェクトが使えるはずなので、適当に使用できます。
ScreenUpdating=falseを入れて画面の描写を抑える
Cellオブジェクトに書き込みを行うと、画面への描写も行われるのですが、画面への描写は輪をかけて遅いです。画面への描写を抑えることで高速化出来る余地があります。
VBAの起動直後に以下のような記載を入れてあげてください。
ScreenUpdating = false
ただし、必ず最後にtrueに戻してあげる必要があります。
ScreenUpdating = true
ON ERROR RESUME NEXTとかON ERROR GOTO XXXのようなエラーハンドリングを知らないと死ぬので注意してください。エラーハンドリングについて自信がない場合はあまりお勧めできません。
Cellへのアクセスを最小限にしていればオプショナルでいいと思います。*2
Variantは使用しない
Variantは他の型に比べて不要な情報を多く保持していてメモリもたくさん使います。極力使わないほうがいいです。Stringなどの適切な型を使用してあげてください。
Dim str as String
みたいな形で。
オブジェクトの生成は最小限に抑える
オブジェクトの生成を抑えることでメモリの消費を抑え高速化出来る場合があります。
例えば、関数内に以下のような記述があったとします。
Dim obj as Object set obj = new Object
以下のように書き換えることでこのオブジェクトはその関数内では1回しか生成されません。
Dim obj as new Object
for文の中でたくさん作っている場合には効果があるかもしれません。知らないでこれ使っている人は逆にバグ出したりしますが・・・・・
文字列型関数を使う
最後に$が付いている関数はVariantではなく文字列を扱います。そのため高速です。5の亜流ですね。7コメがどうしても思いつかなかったので分離しました。
さて、急ぎ足で7つ紹介してみましたが、いかがでしたでしょうか。ここまで守っていれば十分に速い処理ができているはずです。
ということで、これを全部守っている者だけがVBAに対して石を投げてください。
