【VBA高速化】VBA高速化テクニック②【事務のおばちゃんでも簡単】
今回は、Excel VBAのプログラムを高速化するために使えるテクニックを1つ紹介します。
これは既存のExcel VBAのプログラムを少し修正して改善できるテクニックではありません。
しかし、「Excel VBA でプログラムを書けます」と言えるためには必須のテクニックですので、
是非身につけて頂ければと思います。
テクニックの概要
今回は、ソースコードを紹介する前に、今回シェアするテクニックの概念について説明します。
（概念といっても大それたものではありません。）
Excel VBAにおいて主となる処理の1つに、「セルへのアクセス」があります。
特にセルの値を読む操作は頻出処理だと思います。
Excel VBA においては、「セルにアクセスする回数」分、処理時間がかかります。
例えば、行挿入処理を例にします。
行挿入処理は、Ctrl Shift + といった3つのキーを同時押しすることでできます。
- Excelにて、上記ショートカットキーを10回押して行挿入した場合
- 10行選択して1回ショートカットキーを押した場合
この2つの処理の処理時間はどう変わるでしょうか。
10行選択して1回ショートカットキーを押した場合の方が圧倒的に速いと思います。
セルへアクセスする処理は全て上記の法則が適用されます。
今回は、「セルにアクセスする回数」が多くなりがちな処理である、
セルの値の読み込みについて、具体的なプログラムを共有します。
ソースコード
それではソースコードを以下に記載します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
Option Explicit ' 実行速度が速いセルの値読み込み Public Sub fastCellReader() ' 定数宣言 Const MAX_ROW_NUMBER As Long = 50000 Const MAX_COLUMN_NUMBER As Long = 100 ' 変数宣言 Dim sheet As Worksheet Dim r As Range Dim startTime As Single Dim rowNumber As Long Dim columnNumber As Long Dim cellText As String Dim cells As Variant ' 処理開始日時を取得 startTime = Timer() ' アクティブシートを取得 Set sheet = ActiveSheet ' A1セルから最大行目/最大列目までの値を一気に取得 With sheet cells = .Range(.cells(1, 1), .cells(MAX_ROW_NUMBER, MAX_COLUMN_NUMBER)) End With ' 1行目から最大行目まで走査 For rowNumber = 1 To MAX_ROW_NUMBER ' 1列目から最大列目まで走査 For columnNumber = 1 To MAX_COLUMN_NUMBER ' セルの値を読み込み cellText = cells(rowNumber, columnNumber) Next Next ' 処理時間を出力 Debug.Print "処理時間 : " & Format((Timer() - startTime), "0.00") & " 秒" End Sub ' 実行速度が遅いセルの値読み込み Public Sub SlowCellReader() ' 定数宣言 Const MAX_ROW_NUMBER As Long = 50000 Const MAX_COLUMN_NUMBER As Long = 100 ' 変数宣言 Dim sheet As Worksheet Dim r As Range Dim startTime As Single Dim rowNumber As Long Dim columnNumber As Long Dim cellText As String ' 処理開始日時を取得 startTime = Timer() ' アクティブシートを取得 Set sheet = ActiveSheet ' 1行目から最大行目まで走査 For rowNumber = 1 To MAX_ROW_NUMBER ' 1列目から最大列目まで走査 For columnNumber = 1 To MAX_COLUMN_NUMBER ' セルの値を読み込み cellText = sheet.cells(rowNumber, columnNumber).Value Next Next ' 処理時間を出力 Debug.Print "処理時間 : " & Format((Timer() - startTime), "0.00") & " 秒" End Sub |
上記プログラムの実行速度は以下の通りです。
| 実行回数 | FastCellReader | SlowCellReader |
| 1回目 | 1.08秒 | 11.69秒 |
| 2回目 | 1.10秒 | 12.57秒 |
| 3回目 | 0.96秒 | 12.06秒 |
上記の通り、SlowCellReaderは、FastCellReader の10倍程度処理時間を要しています。
[補足]
セルの値の読み込み以外のセル操作についても上記概念は通用します。
例えば、罫線を設定したい場合においても、広範囲を選択して1度に罫線を設定することで、
処理時間を大幅に短縮できます。
まとめ
今回は、Excel VBAの処理速度向上のためのテクニックを紹介しました。
大事なポイントは以下2点。
- Excel VBA においては、「セルにアクセスする回数」分、処理時間がかかる。
- セルから値を取得する処理は、一度に全て値を取得することで値取得処理については10倍性能が向上する。
「Excel VBAを使える」と言えるようになるためには今回紹介したテクニックは必須ですので、
是非今回のテクニックを身につけましょう。