こんにちは！高速化ネタが大好きなtknriaです！
繰り返し処理をしたい場合にちょー頼りになるVBA。
仕事では120%お世話になりまくりです。
そんなVBAですが、たま～に「処理遅いな。。」と感じてしまうこともあります。
原因はいろいろありますが、解決策の一つとして考えられ、すぐにでも導入できるものを紹介します。
それが「With ～ End With」構文。
■ Withを使ってみる（コード例）
まず、Excelのsheet上に、2つボタンを用意してください。
これらのボタンに以下を割り当てます。
'----- 以下、ソース -----
Const YOKO_MAX = 100
Const TATE_MAX = 5000
Const heikin = 10
Sub ベタ更新_Click()
Dim start As Double
Dim finish As Double
Dim time_total As Double
Dim i As Integer
Dim j As Integer
Dim kaisuu As Integer
time_total = 0
For kaisuu = 1 To heikin
Worksheets("Sheet1").Range("A1:KN10000").Clear
start = Timer
For i = 1 To TATE_MAX
For j = 1 To YOKO_MAX
Worksheets("Sheet1").Cells(j, i) = 1
Next j
Next i
finish = Timer
' MsgBox ("ベタ更新：" & finish - start & "sec")
time_total = time_total + (finish - start)
Next kaisuu
Worksheets("Sheet2").Range("B10") = time_total / heikin
End Sub
Sub with更新_Click()
Dim start As Double
Dim finish As Double
Dim time_total As Double
Dim i As Integer
Dim j As Integer
Dim kaisuu As Integer
time_total = 0
With Worksheets("sheet1")
For kaisuu = 1 To heikin
.Range("A1:AI100").Clear
start = Timer
For i = 1 To TATE_MAX
For j = 1 To YOKO_MAX
.Cells(j, i) = 1
Next j
Next i
finish = Timer
' MsgBox ("with更新：" & finish - start & "sec")
time_total = time_total + (finish - start)
Next kaisuu
End With
Worksheets("Sheet2").Range("B10") = time_total / heikin
End Sub
'----- 以上、ソース -----
Const YOKO_MAX = 100
Const TATE_MAX = 5000
Const heikin = 10
Sub ベタ更新_Click()
Dim start As Double
Dim finish As Double
Dim time_total As Double
Dim i As Integer
Dim j As Integer
Dim kaisuu As Integer
time_total = 0
For kaisuu = 1 To heikin
Worksheets("Sheet1").Range("A1:KN10000").Clear
start = Timer
For i = 1 To TATE_MAX
For j = 1 To YOKO_MAX
Worksheets("Sheet1").Cells(j, i) = 1
Next j
Next i
finish = Timer
' MsgBox ("ベタ更新：" & finish - start & "sec")
time_total = time_total + (finish - start)
Next kaisuu
Worksheets("Sheet2").Range("B10") = time_total / heikin
End Sub
Sub with更新_Click()
Dim start As Double
Dim finish As Double
Dim time_total As Double
Dim i As Integer
Dim j As Integer
Dim kaisuu As Integer
time_total = 0
With Worksheets("sheet1")
For kaisuu = 1 To heikin
.Range("A1:AI100").Clear
start = Timer
For i = 1 To TATE_MAX
For j = 1 To YOKO_MAX
.Cells(j, i) = 1
Next j
Next i
finish = Timer
' MsgBox ("with更新：" & finish - start & "sec")
time_total = time_total + (finish - start)
Next kaisuu
End With
Worksheets("Sheet2").Range("B10") = time_total / heikin
End Sub
'----- 以上、ソース -----
n行、100列の幅に1を代入するというマクロですが、代入にかかった時間を計測しています。
また、今回は10回の試行の平均時間で比較します。
■ Withの効果！
横軸にn行、縦軸に平均時間secをとったグラフが以下の通り。
データ数10万個（1000行、100列）ともなると、約10%の時間削減効果が出てきています。
10%の時間削減は、その作業にかかる人件費の10%削減に繋がります！
■ 何で差が出るの？
この差はどうして生じるかというところですが、
For i = 1 To TATE_MAX
For j = 1 To YOKO_MAX
Worksheets("Sheet1").Cells(j, i) = 1
Next j
Next i
だと、Worksheets("Sheet1")→Cells(j,i)→i,jをインクリ→
Worksheets("Sheet1")→Cells(j,i)→i,jをインクリ→…
と処理されていくところが、
With Worksheets("Sheet1")
For i = 1 To TATE_MAX
For j = 1 To YOKO_MAX
.Cells(j, i) = 1
Next j
Next i
End With
だと、Worksheets("Sheet1")→Cells(j,i)→i,jをインクリ→Cells(j,i)→インクリ→Cells(j,i)→…
と処理されるためです。
家族の紹介をするときに、「○○家」ということを最初に伝えて、名前だけ紹介するのと同じ感覚でしょうか。
中学校の理科でいうと、信号を脳まで送らずに脊髄で処理する「反射」と同じ？
■ 億劫なあとがき
実験や評価の自動化ツールとしてExcel VBAを用いる人も多いと思うので、遅いと感じる人は見直してみてはいかがでしょうか。
