VBAのマクロを組んで実行していると、大量のデータ処理にかなりの時間が掛かる事があります。
数百行処理するレベルではあまり気にならない処理も数万件の処理になると処理性能が非常に気になります。
今回は、EXCEL VBAマクロの性能向上方法について記載しようと思います。
性能劣化の原因
EXCEL VBAマクロを使用してプログラムを実行する際には、大きく分けて２種類の処理が存在します。
①内部でのループ、判断、計算
これは通常のプログラミング言語と同様に内部のワークエリアへの参照、代入、計算、ループ、判断などに当たります。
②EXCELワークシート関連の操作
EXCELワークシートのセルの内容の参照、セルへの当たりの代入、表の書式変更など、EXCELワークシートを直接操作する処理です。
この中で、①処理自体は性能が良い為、特に性能改善を行う必要はありません。
もちろん、処理方式の見直しなど、改善は可能ですが、そこまでの効果は期待できません。
これに対して、②のEXCELワークシート関連の操作は実は非常に重いのです。
つまり、②のEXCELワークシート関連の操作を見直す事により、性能向上を図る事が可能です。
最も簡単でかなり効果がある性能向上策
まず試すべき性能向上策としては、EXCELアプリケーションの画面更新をしない命令を入れる事です。
これは処理の開始に１ステップ、処理の終了に１ステップの合計２ステップ入れるだけで済みます。
具体的なコーディングは以下
画面更新を止める命令
Application.ScreenUpdating = False
画面更新を再開する命令
Application.ScreenUpdating = True
実際の処理性能を見る為にサンプルプログラミングを組んでみました。
処理の流れは、入力ファイルを読み込んで、”，”で項目を区切り、７つの項目を１行に出力するものです。
Sub FileIO_I2() Dim Infile As String Dim linecnt As Long Dim strstm As String Dim wkstm() As String Application.ScreenUpdating = False Workbooks(1).ActiveSheet.Cells(2, 7) = Time Infile = Workbooks(1).ActiveSheet.Cells(2, 3) Open Infile For Input As #1 linecnt = 1 Do While (EOF(1) = False) Line Input #1, strstm wkstm = Split(strstm, ",") Workbooks(1).ActiveSheet.Cells(linecnt + 5, 2) = linecnt Workbooks(1).ActiveSheet.Cells(linecnt + 5, 3) = wkstm(0) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 4) = wkstm(1) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 5) = wkstm(2) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 6) = wkstm(3) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 7) = wkstm(4) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 8) = wkstm(5) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 9) = wkstm(6) linecnt = linecnt + 1 Loop Close #1 Workbooks(1).ActiveSheet.Cells(3, 7) = Time Application.ScreenUpdating = True MsgBox ("処理終了") End Sub
入力データファイルは５万行のデータを準備しました。
まずは、性能向上策を入れないバージョンでの走行。
性能向上部分をコメントアウトして実行。
実行結果として、１０：２５秒かかりました。
つぎに性能向上部分のコメントアウトを外して実行。
実行結果が、４：１０秒に短縮しました。
たった２ステップ追加するだけで、処理時間が半分以下まで短縮。
と言うように、簡単な割りに効果がかなりあります。
この性能向上策に対しての注意点ですが、処理が途中でエラーになった際に、画面の更新が行われないようになる為、エラー処理に必ず画面更新を再開する処理を入れる事です。
少し手間がかかるが、それなりの性能向上が期待できる性能向上策
さらに性能向上を行う際の対策について記載します。
対策の概要として、EXCELワークシートへの参照、更新命令を極力少なく（命令数を少なく）する事です。
今回のプログラムでは、入力データを”，”で分解し、１項目ずつセルに代入しています。
↓この部分
Workbooks(1).ActiveSheet.Cells(linecnt + 5, 2) = linecnt Workbooks(1).ActiveSheet.Cells(linecnt + 5, 3) = wkstm(0) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 4) = wkstm(1) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 5) = wkstm(2) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 6) = wkstm(3) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 7) = wkstm(4) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 8) = wkstm(5) Workbooks(1).ActiveSheet.Cells(linecnt + 5, 9) = wkstm(6)
この処理を改善し、EXCELワークシートへの操作を１命令にしてみます。
処理的には、セルに直接代入していた値をwkVALに一時的に格納し、EXCELワークシートへの代入をRange命令に変更し、一括処理しています。
Renge命令のところは、コーディングが長くなるので、Withステートメントを使ってWorkbooks(1).ActiveSheet部分を省略。
Dim wkVAL(8) As String
wkVAL(0) = linecnt
wkVAL(1) = wkstm(0)
wkVAL(2) = wkstm(1)
wkVAL(3) = wkstm(2)
wkVAL(4) = wkstm(3)
wkVAL(5) = wkstm(4)
wkVAL(6) = wkstm(5)
wkVAL(7) = wkstm(6)
With Workbooks(1).ActiveSheet
.Range(.Cells(linecnt + 5, 2), .Cells(linecnt + 5, 9)) = wkVAL
End With
この状態で実行
実行結果は、３：０２秒になりました。
処理時間が１分８秒短縮
トータルで性能向上を何もしていない時の１／３になりました。
こちらの性能対策は、細かいコーディングの見直しが要求される為、難易度は高いですが、やればやるだけ性能が向上していきます。
ちなみにRange命令の範囲の書き方も、文字列にするとさらに性能向上します。
strRange = "B" & CStr(linecnt + 5) & ":" & "I" & CStr(linecnt + 5)
.Range(strRange) = wkVAL上記の指定に変えた所、さらに３０秒ほど短縮しました。
兎に角、肝としては、セルへの参照を極力減らす事。
同じセルを何度も参照するのであれば、一度ワークエリアに格納して参照するなどの対策を心掛けると性能は向上します。
以上、EXCEL VBAマクロの性能向上策でした。
コメント