VBA配列による高速化 時間割ソフトを使った実験
今回は実際に使っていた時間割編成ソフトを紹介します。使いますのはVector にフリーソフトとして登録している「エクセルVBAシンプル時間割」です。学校の先生でなければあまり興味はないと思いますが、ここでは速度差を見るためにだけ使いますのでご了承ください。
ダウンロードして立ち上げたらALT＋F11でエディターを開きます。標準モジュールのmodule1に次のコードが書かれています。
Sub 転記()
'使用する変数を宣言する（定義付け）
Dim i As Integer, j As Integer, k As Integer, m As Integer
Dim c As Variant, d As Variant, e As Variant, f As Variant
'教員時間割と教室時間割のデータを消去（初期化）
Worksheets("教員時間割").Range("c5", "aj150") = ""
Worksheets("教室時間割").Range("c5", "an150") = ""
'セルの値を変数として読み込み
c = Worksheets("クラス時間割").Range("a1", "an150")
d = Worksheets("編成表").Range("a1", "an500")
e = Worksheets("教員時間割").Range("a1", "an150")
f = Worksheets("教室時間割").Range("a1", "an150")
'計算範囲を指定(繰り返し）
For i = 1 To 50
For j = 3 To 37
For m = 1 To 500
'空欄や該当しないデータはとばしなさい。
'(「クラス時間割と編成表」間で「クラス番号と科目名」が一致する時間だけを拾う）
If c(i, j) = "" Then GoTo 10
If d(m, 6) = "" Then GoTo 20
If c(i, j) <> d(m, 6) Then GoTo 20
If c(i, 1) <> d(m, 1) Then GoTo 20
' 編成表と教員時間割の教員番号が一致したら、教員時間割のその時間に「クラス番号と科目名」を記入しなさい。
For k = 1 To 60
If e(k, 1) = "" Then GoTo 100 '教員番号がない行はとばしなさい
If e(k, 1) = d(m, 7) Then e(k, j) = e(k, j) & d(m, 1) & d(m, 6)
100 Next k
' 編成表と教室時間割の教室番号が一致したら、教室時間割のその時間に「クラス番号と科目名」を記入しなさい。
For l = 1 To 60
If f(l, 1) = "" Then GoTo 200 '教室番号がない行はとばしなさい
If f(l, 1) = d(m, 9) Then f(l, j) = f(l, j) & d(m, 1) & d(m, 6)
200 Next l
20 Next m
10 Next j
Next i
'計算結果を表に書き戻しなさい
Worksheets("教員時間割").Range("a1", "aj150") = e
Worksheets("教室時間割").Range("a1", "an150") = f
End Sub「'セルの値を変数として読み込み」でワークシートを配列に取り込んでいます。これ以後はワークシートは無関係にメモリーの中でデータ処理が実行されます。終了後は「'計算結果を表に書き戻しなさい」で配列の中にある結果をワークシートに書き込んでいます。配列はメモリーの中にあり見えないため、頭の中で想像しながら使わなければいけないのですが、ワークシートを読み込ませるとそのワークシートをみながらコードを書けます。cellsとまったく同じ感覚です。
さて配列を使わずにcellsでコードを書くと次のようになります。まったく同じ動作をするのですが、cellsは1つずつワークシートからデータを取り寄せ、1つずつ結果を書き込むということを繰り返します。その動作の違いを実験してみましょう。次のコードをコピーしてVBEに貼り付けてください。位置は配列を使ったコードの下でOKです。教員時間割または教室時間割のワークシートを開き、最初の（）の横にカーソルを合わせたら、緑色の三角の実行ボタンを押しましょう。F5を押しても実行します。処理速度はPCの性能によって異なります。私の場合は下のようになりました。
Sub セル仕様GoTo文()
'使用する変数を宣言する（定義付け）
Dim i As Integer, j As Integer, k As Integer, m As Integer
Dim c As Variant, d As Variant, e As Variant, f As Variant
'教員時間割と教室時間割のデータを消去（初期化）
Worksheets("教員時間割").Range("c5", "aj150") = ""
Worksheets("教室時間割").Range("c5", "an150") = ""
'計算範囲を指定(繰り返し）
For i = 1 To 50
For j = 3 To 37
For m = 1 To 380
'空欄や該当しないデータはとばしなさい。
'(「クラス時間割と編成表」間で「クラス番号と科目名」が一致する時間だけを拾う）
If Worksheets("クラス時間割").Cells(i, j) = "" Then GoTo 10
If Worksheets("編成表").Cells(m, 6) = "" Then GoTo 20
If Worksheets("クラス時間割").Cells(i, j) <> Worksheets("編成表").Cells(m, 6) Then GoTo 20
If Worksheets("クラス時間割").Cells(i, 1) <> Worksheets("編成表").Cells(m, 1) Then GoTo 20
' 編成表と教員時間割の教員番号が一致したら、教員時間割のその時間に「クラス番号と科目名」を記入しなさい。
For k = 1 To 60
If Worksheets("教員時間割").Cells(k, 1) = "" Then GoTo 100 '教員番号がない行はとばしなさい
If Worksheets("教員時間割").Cells(k, 1) = Worksheets("編成表").Cells(m, 7) Then Worksheets("教員時間割").Cells(k, j) = Worksheets("教員時間割").Cells(k, j) & Worksheets("編成表").Cells(m, 1) & Worksheets("編成表").Cells(m, 6)
100 Next k
' 編成表と教室時間割の教室番号が一致したら、教室時間割のその時間に「クラス番号と科目名」を記入しなさい。
For l = 1 To 60
If Worksheets("教室時間割").Cells(l, 1) = "" Then GoTo 200 '教室番号がない行はとばしなさい
If Worksheets("教室時間割").Cells(l, 1) = Worksheets("編成表").Cells(m, 9) Then Worksheets("教室時間割").Cells(l, j) = Worksheets("教室時間割").Cells(l, j) & Worksheets("編成表").Cells(m, 1) & Worksheets("編成表").Cells(m, 6)
200 Next l
20 Next m
10 Next j
Next i
End Sub