Excel VBA:実行高速化

画面更新を止める

Application.ScreenUpdating = False

再計算を止める

Application.Calculation = False

イベント処理を止める

Application.EnableEvents = False

警告を止める

Application.DisplayAlerts = False

カーソルを待ち表示にする

Application.Cursor = xlWait

code:MacroUtilClass.cls

Private vScreenUpdatingBak As Boolean

Private vCalculationBak As Long

Private vEnableEventsBak As Boolean

Private vDisplayAlertsBak As Boolean

Private vCursorBak As Long

Public Sub MacroStart()

vScreenUpdatingBak = Application.ScreenUpdating

vCalculationBak = Application.Calculation

vEnableEventsBak = Application.EnableEvents

vDisplayAlertsBak = Application.DisplayAlerts

vCursorBak = Application.Cursor

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

Application.DisplayAlerts = False

Application.Cursor = xlWait

End Sub

Public Sub MacroEnd()

Application.Cursor = vCursorBak

Application.DisplayAlerts = vDisplayAlertsBak

Application.EnableEvents = vEnableEventsBak

Application.Calculation = vCalculationBak

Application.ScreenUpdating = vScreenUpdatingBak

End Sub

VBA はセル1つ1つにアクセスするととても遅い。(再描画と再計算を抜いていても遅い)

このため、行全体や列全体、表全体の Variant の配列を作ってから、それをセルに貼り付けるのが早い。

行全体や列全体、表全体の Variant の配列を作るには、Worksheet.Range オブジェクトを使うのが良い。

code:vb

' Range に対して一気に書くと早い

Sub TestRangeValue(ivSheet As Worksheet)

Dim v As Variant

v = ivSheet.Range(ivSheet.Cells(10, 10), ivSheet.Cells(110, 210))

For i = LBound(v, 1) To UBound(v, 1) '相対位置になる点に注意

For j = LBound(v, 2) To UBound(v, 2) '相対位置になる点に注意

v(i, j) = i * 1000 + j

Next

Next

ivSheet.Range(ivSheet.Cells(10, 10), ivSheet.Cells(110, 210)) = v

End Sub

Sub TestRangeValueActiveSheet()

TestRangeValue ActiveSheet

End Sub

' メチャクチャ遅い

Sub TestCellValue(ivSheet As Worksheet)

v = ivSheet.Range(ivSheet.Cells(10, 10), ivSheet.Cells(110, 210))

For i = 1 To 100

For j = 1 To 200

ivSheet.Cells(i + 10, j + 10) = i * 1000 + j

Next

Next

End Sub

Sub TestCellValueActiveSheet()

TestCellValue ActiveSheet

End Sub

参考

CreateObject の呼び出しはとても遅い。

可能な限り使いまわす。

キャッシュ化する。

VBA のエディタ画面の「ツール」→「参照」で該当のオブジェクトを参照状態にする。

例

code:vb

Public FSO As New Scripting.FileSystemObject

ただし、この場合は関数呼び出しごとに New されるのでまだパフォーマンスが低い。

2. Static を使って関数内に保持する

code:vb

Function GetFSO() As Object

Static FSO As Object

If FSO Is Nothing Then

Set FSO = CreateObject("Scripting.FileSystemObject")

End If

Set GetFSO = FSO

End Function

デメリットはインスタンスが常時メモリ上に存在してしまう点

(必要であれば何らかの解放手段を用意する)