Excelスキルアップコレクション
           〜 Something U Want 〜


Google  

WWW を検索
Excelスキルアップコレクション内 を検索
 @     Top  >     Excel VBA スキルアップコレクション Topics  >     (04) シート上へ高速にデータを書出す

 
(04) シート上へ高速にデータを書出す

 シートに何も書出されていない状態から

 行番号+列番号を書出す処理を考えます。行方向に1,000、列方向に100の範囲で、VBAからこの処理を行う場合、どのようなプログラムが高速でしょうか?

 次の3つのプログラムを作成しパフォーマンス比較します。
[ プログラム1 ]

 1行目:  Public Sub test1()
 2行目:      Dim lp1 As Long, lp2 As Long
 3行目:      ' 行数分ループする。
 4行目:      For lp1 = 1 To 1000
 5行目:          ' 列数分ループする。
 6行目:          For lp2 = 1 To 100
 7行目:              ' "行数+列数"の値をシートに書出す。
 8行目:              Range("A1").Offset(lp1 - 1, lp2 - 1).Value = lp1 + lp2
 9行目:          Next
10行目:      Next
11行目:  End Sub
[ プログラム2 ]

 1行目:  Public Sub test2()
 2行目:      Application.ScreenUpdating = False ' 画面更新の抑止
 3行目:      Call test1
 4行目:      Application.ScreenUpdating = True  ' 画面更新の再開
 5行目:  End Sub
[ プログラム3 ]

 1行目:  Public Sub test3()
 2行目:      Application.ScreenUpdating = False ' 画面更新の抑止
 3行目:      
 4行目:      Dim lp1 As Long, lp2 As Long
 5行目:      Dim dataArr(1000 - 1, 100 - 1) As Variant
 6行目:      
 7行目:      ' 行数分ループする。
 8行目:      For lp1 = 1 To 1000
 9行目:          ' 列数分ループする。
10行目:          For lp2 = 1 To 100
11行目:              ' "行数+列数"の値を二次元配列に格納する。
12行目:              dataArr(lp1 - 1, lp2 - 1) = lp1 + lp2
13行目:          Next
14行目:      Next
15行目:      
16行目:     ' 作成した2次元配列をシートに一括書出しする。
17行目:     Range("A1:CV1000") = dataArr
18行目:  
19行目:      Application.ScreenUpdating = True  ' 画面更新の再開
20行目:  End Sub
簡単にプログラムについて説明します。
[ プログラム1 ]は、仕様を満たす最もスタンダードなプログラムです。二重ループ内で、行番号+列番号を計算しながらセルにデータを書出しています。
[ プログラム2 ]は、[ プログラム1 ]を呼び出しているだけですが、VBAからExcelシートにデータを書出す際の画面更新を一時的に抑止し、画面のちらつきを抑える効果を働かせています。Application.ScreenUpdatingについては、かなりメジャーなオプションなので、ご存知の方も多いでしょう。[ プログラム1 ]を実行させる前に、このオプションをFalseに設定することで、セルに書出す様子がリアルタイムに反映されなくなり、その分パフォーマンスアップが期待できます。ただし、画面更新を抑止して処理を行った場合、処理が終わったタイミングで画面更新を再開させる為にTrueを設定し直して下さい。
Application.ScreenUpdating ← True/False
[ プログラム3 ]は、少し回りくどい処理を実施していますが、Variant型の配列変数に、12行目にて、行番号+列番号の計算結果を一時的に格納しています。その後、17行目でVariant型の配列データをExcelシートに一括出力する方法を使っています。
実行計測結果を以下に示します。
プログラム測定結果[秒]
1100
251
31以下

 [ プログラム1 ][ プログラム2 ]の測定結果を見る限り、画面更新を抑止してExcelシートにデータを書出した方が、大幅にパフォーマンス改善されることがわかります。また、[ プログラム3 ]は少し回りくどい処理ではあるものの、爆発的に高速であることが実証されました。実行環境により測定結果には違いが生まれますが、これら3つのプログラムの速度比率はほぼ同程度なものが得られるでしょう。
ページの先頭へ