さて、今回はちょっとむつかしいかもしれないが・・。
------
VBAでシステムを作る際の考慮点 その６
次の壁。
・結果が出るまで３０秒かかるが、そんなにお客さんを待たせられない。
つまりは、処理が遅い、という事象である。これは「VBAだから」と諦めている方も多いのではないだろうか。実際、処理の高速化についての資料は、私が探していないだけかもしれないが、ほとんど見かけないように思う。
参考まで、この記事で紹介した、Microsoft Excel / Visual Basic プログラマーズガイド for Windows 95 （アスキー出版局）は入手できるなら持っていた方がいいかと思う。ただ、まことに僭越ながら、今後はこれを超える内容を紹介していこうと思っているので、と大風呂敷を広げてみるが、のちのちビックマウスといわれないようにしなくては。
話がそれたが、よほど効率の悪いコードを組んでいない限りは、経験上次のところで特に遅くなっているケースが多い。
・シートへのデータのライト
・シートからのデータのリード
・データベースアクセス
まずは、シートのライトから。これにはプロパティの変更、つまりフォントの大きさや色、セルの色変更なども含む。
まずは画面の更新を止めてみる、というのは効果が高い。
Application.ScreenUpdateing = False
とするとExcel画面が更新されなくなる。処理の終わりや、エラー発生時の処理に
Application.ScreenUpdateing = True
と書かないと、いつまでも更新されないで固まった画面になるので注意が必要だ。Excel 5 のときは処理が終了すると自動的に画面更新がされるようになったのだが・・。
画面の更新が激しい場合はこれが有効である。
また、プロパティの変更は処理をまとめると高速化が図れる。
セル個別に変更するより、複数セルまとめたり、列単位にしたり、複数列をまとめたり・・。
次にシートのライトとリードに共通のものがある。
これはデータベースや外部ファイルから読み込んだ時に有効なものであるが、ひとつひとつのセルにアクセスしていくのではなく、仲介する Variant 型の変数を介するようにするとよい。
ちょっと例文が怪しい（資料を穿り返して正しく書き直すのでしばしお待ちを・・）
Dim vVal as Variant
vVal = Cells
こんなようにすれば、vValにセルの値がごっそり入る。領域を制限してもいいだろう。そうして読み込んだセルにアクセスようにvVal にアクセスする。vValを変更してシートに貼れば、それでシートの内容が変更できる。
このとき、Option Base を 1 にしておくこと。そうしないとセルの(1,1)がvValの(0,0)になるので、混乱のものになるためだ。
データベースアクセスは使っていない方も多いかもしれない。速度が遅いときのデータベーステクニックとして簡単に二つだけ述べておく。
一つは、データベースとしてAccessを使うな、ということ。導入の敷居は高くなるかもしれないが、無償版の SQL Server を入れた方がよっぽど高速である。経験上、Accessを呼び出しての処理で２～３時間かかるものを、DBを変更するだけで最低３倍速にはなった（記憶では、２時間半が15分だったかと思うので、10倍ということになるか・・）。３０秒かかっているうち、Accessのデータベースアクセスに２０秒取られているとしたら、その２０秒を２～７秒程度まで短縮できる可能性がある。
もう一つは Excel 2003 を使っていた時の経験則であるが、大量のデータを読み込む場合は、xlsブック自体を大きくしておくこと。小さくしたら 500KBくらいになるとすれば、あえてファイルサイズを 3,000KBとかになるようにして保存しておく。ここでの詳細は省き、いずれ解説するつもりだが、初期はブックサイズ分のメモリを確保するようで、データを読み込んでメモリが不足するとその場で使うメモリを確保しようとするようなのだ。その確保時間に時間がかかる、というのがあるらしい。
高速化の技法のいくつかを紹介したが紹介しなかった分はいずれまた。
------
VBAでシステムを作る際の考慮点 その６
次の壁。
・結果が出るまで３０秒かかるが、そんなにお客さんを待たせられない。
つまりは、処理が遅い、という事象である。これは「VBAだから」と諦めている方も多いのではないだろうか。実際、処理の高速化についての資料は、私が探していないだけかもしれないが、ほとんど見かけないように思う。
参考まで、この記事で紹介した、Microsoft Excel / Visual Basic プログラマーズガイド for Windows 95 （アスキー出版局）は入手できるなら持っていた方がいいかと思う。ただ、まことに僭越ながら、今後はこれを超える内容を紹介していこうと思っているので、と大風呂敷を広げてみるが、のちのちビックマウスといわれないようにしなくては。
話がそれたが、よほど効率の悪いコードを組んでいない限りは、経験上次のところで特に遅くなっているケースが多い。
・シートへのデータのライト
・シートからのデータのリード
・データベースアクセス
まずは、シートのライトから。これにはプロパティの変更、つまりフォントの大きさや色、セルの色変更なども含む。
まずは画面の更新を止めてみる、というのは効果が高い。
Application.ScreenUpdateing = False
とするとExcel画面が更新されなくなる。処理の終わりや、エラー発生時の処理に
Application.ScreenUpdateing = True
と書かないと、いつまでも更新されないで固まった画面になるので注意が必要だ。Excel 5 のときは処理が終了すると自動的に画面更新がされるようになったのだが・・。
画面の更新が激しい場合はこれが有効である。
また、プロパティの変更は処理をまとめると高速化が図れる。
セル個別に変更するより、複数セルまとめたり、列単位にしたり、複数列をまとめたり・・。
次にシートのライトとリードに共通のものがある。
これはデータベースや外部ファイルから読み込んだ時に有効なものであるが、ひとつひとつのセルにアクセスしていくのではなく、仲介する Variant 型の変数を介するようにするとよい。
ちょっと例文が怪しい（資料を穿り返して正しく書き直すのでしばしお待ちを・・）
Dim vVal as Variant
vVal = Cells
こんなようにすれば、vValにセルの値がごっそり入る。領域を制限してもいいだろう。そうして読み込んだセルにアクセスようにvVal にアクセスする。vValを変更してシートに貼れば、それでシートの内容が変更できる。
このとき、Option Base を 1 にしておくこと。そうしないとセルの(1,1)がvValの(0,0)になるので、混乱のものになるためだ。
データベースアクセスは使っていない方も多いかもしれない。速度が遅いときのデータベーステクニックとして簡単に二つだけ述べておく。
一つは、データベースとしてAccessを使うな、ということ。導入の敷居は高くなるかもしれないが、無償版の SQL Server を入れた方がよっぽど高速である。経験上、Accessを呼び出しての処理で２～３時間かかるものを、DBを変更するだけで最低３倍速にはなった（記憶では、２時間半が15分だったかと思うので、10倍ということになるか・・）。３０秒かかっているうち、Accessのデータベースアクセスに２０秒取られているとしたら、その２０秒を２～７秒程度まで短縮できる可能性がある。
もう一つは Excel 2003 を使っていた時の経験則であるが、大量のデータを読み込む場合は、xlsブック自体を大きくしておくこと。小さくしたら 500KBくらいになるとすれば、あえてファイルサイズを 3,000KBとかになるようにして保存しておく。ここでの詳細は省き、いずれ解説するつもりだが、初期はブックサイズ分のメモリを確保するようで、データを読み込んでメモリが不足するとその場で使うメモリを確保しようとするようなのだ。その確保時間に時間がかかる、というのがあるらしい。
高速化の技法のいくつかを紹介したが紹介しなかった分はいずれまた。