Excel VBAでコーディングを行う際、1つのプロシージャ（サブや関数）のサイズが大きくなりすぎると、可読性や保守性が低下します。
適切なサイズに分割し、効率的なコード設計をすることで、エラーの削減やデバッグのしやすさが向上します。
この記事では、プロシージャの正しいサイズ、長くなりすぎた場合の対策、具体的なリファクタリング（整理）方法について解説します。
目次
プロシージャの正しいサイズとは？
VBAには、プロシージャの長さに厳密な制限はありませんが、「1つのプロシージャは50～100行以内」に収まるのが推奨されます。
| プロシージャの行数 | 可読性・メンテナンス性 |
|---|---|
| ～50行 | 最適（短くて分かりやすい） |
| 50～100行 | 範囲外（コメントがあればOK） |
| 100行以上 | 分割推奨（長すぎて保守性低下） |
✔ 「1つのプロシージャで1つの目的を達成する」ことを意識すると、適切なサイズになりやすくなります。
プロシージャが長くなる原因と問題点
長いプロシージャは、以下のような問題があります。
・ 可読性の低下
Sub LongProcedure()
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer
' シートの設定
Set ws = ThisWorkbook.Sheets("Sheet1")
' セルの書式設定
ws.Range("A1:A10").Font.Bold = True
ws.Range("B1:B10").Interior.Color = RGB(255, 255, 0)
ws.Range("C1:C10").NumberFormat = "0.00"
' データ入力
For i = 1 To 10
ws.Cells(i, 1).Value = "データ " & i
ws.Cells(i, 2).Value = i * 10
Next i
' 最終行を取得し、合計を計算
Set rng = ws.Range("B1").End(xlDown)
ws.Cells(rng.Row + 1, 2).Formula = "=SUM(B1:B" & rng.Row & ")"
' メッセージ表示
MsgBox "処理が完了しました！", vbInformation
End Sub
📌問題点
- 1つのプロシージャに複数の処理（フォーマット設定・データ入力・合計計算）が完了
- コードの長さが増えると、修正やデバッグが困難になる
・変更や修正が大変
長いプロシージャは、一部の処理を変更したいときに影響範囲が広がり、バグが発生する可能性が高くなります。
✔ 「1つのプロシージャは1つの機能を持つべき」という原則に従うと、変更時の影響範囲を最小限にすることができます。
プロシージャを分割する方法
長いプロシージャを「正しい単位で分割」することで、可読性・メンテナンス性を向上できます。
・関連する処理ごとにサブに分割
' メインのプロシージャ
Sub MainProcedure()
FormatCells
InputData
CalculateTotal
MsgBox "処理が完了しました！", vbInformation
End Sub
' セルの書式設定
Sub FormatCells()
With ThisWorkbook.Sheets("Sheet1")
.Range("A1:A10").Font.Bold = True
.Range("B1:B10").Interior.Color = RGB(255, 255, 0)
.Range("C1:C10").NumberFormat = "0.00"
End With
End Sub
' データ入力
Sub InputData()
Dim ws As Worksheet
Dim i As Integer
Set ws = ThisWorkbook.Sheets("Sheet1")
For i = 1 To 10
ws.Cells(i, 1).Value = "データ " & i
ws.Cells(i, 2).Value = i * 10
Next i
End Sub
' 合計計算
Sub CalculateTotal()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("B1").End(xlDown)
ws.Cells(rng.Row + 1, 2).Formula = "=SUM(B1:B" & rng.Row & ")"
End Sub
✅ 1つの処理ごとにサブを選択することで、各機能の独立性が判断、管理が容易に！
✅MainProcedureは処理の流れを管理するだけで、どこで何をしているのかが一目でわかる！
・ 共通処理を関数（Function）化する
複数のプロシージャで使う共通の処理を、関数として定義すると、さらに効率的になります。
' 最終行を取得する関数
Function GetLastRow(ws As Worksheet, columnNumber As Integer) As Long
GetLastRow = ws.Cells(Rows.Count, columnNumber).End(xlUp).Row
End Function
' 合計を計算する Sub
Sub CalculateTotalUsingFunction()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim lastRow As Long
lastRow = GetLastRow(ws, 2) ' 列Bの最終行を取得
ws.Cells(lastRow + 1, 2).Formula = "=SUM(B1:B" & lastRow & ")"
End Sub
✅GetLastRow関数を作成することで、他のプロシージャでも簡単に最終実行を取得可能！
✅共通処理を関数化することで、コードの重複を削減できる。
プロシージャを分割する際のルール
- 1つのプロシージャは1つの目的に沿って
- 例: 「データ入力」「書式設定」「一括」など、それぞれ独立した処理を選択
- 50～100行以内を目安にする
- 長くなる場合はサブや関数に分割
- 引数を使って柔軟に処理する
- 例:
SetCellValue(sheetName As String, cellAddress As String, value As Variant)
- 例:
- 共通処理は関数を考える
- 例:
GetLastRow(ws As Worksheet, columnNumber As Integer) As Long
- 例:
まとめ
| 方法 | ポイント |
|---|---|
| 長いプロシージャを気にする | 1つのプロシージャは50～100行以内が理想 |
| 処理ごとに Sub に分割 | FormatCells、、InputDataなどCalculateTotalに分ける |
| 共通処理は関数を考える | GetLastRow、CalculateTotalなど汎用的な処理を関数化 |
| メインプロシージャは流れを管理するだけにする | MainProcedureのように処理の流れを管理 |
✔正しくプロシージャを分割することで、VBAのコードが整理され、メンテナンスが容易になります！