Excel VBA 逆引き集 | VBAでリファクタリング

Excel VBA
スポンサーリンク
  1. ねらい：動作は変えずに「読みやすく・直しやすく・速く」する
    1. 重要ポイントの深掘り
  2. リファクタリングの安全な流れ（7ステップ）
    1. 現状の棚卸しで「直す対象」を見える化する
    2. 開始・終了・例外の共通枠を先に導入する
    3. 入口名を Run_XXXX に統一して操作点を一本化する
    4. 責務分離で「直す場所」を明確化する
    5. セル往復を撤廃し、配列I/Oへ置換する
    6. 重複処理を共通モジュールへ集約する
    7. テスト・計測・ログで「動作不変」と「効果」を確認する
  3. よくある悪い癖をその場で直す
    1. Select/Activateの多用を「直接参照」に置き換える
    2. 1セルずつの読み書きを「配列I/O」に置き換える
    3. ロジックからセル参照を排除して「純粋関数」にする
    4. 定数直書きをやめて「Configから読む」ようにする
    5. DoEventsの呼びすぎを「1〜5%刻み」に間引く
  4. 分離テンプレートで「読めて直せる」形へ
    1. 業務フローと純粋ロジックを分けたサンプル
  5. 例題で練習しながら確かめる
    1. 例題1：共通枠の導入で「落ちても戻る」ことを確認する
    2. 例題2：セル往復を配列I/Oへ置換し、処理時間を計測する
    3. 例題3：ロジックを純粋関数に切り出し、単体テストを回す
  6. 実務の落とし穴を先回りで避ける
    1. 一気に直して壊すことを避けるための小さな刻み方
    2. 入口が複数で制御不能になる前に統一する
    3. 定数直書きによる本番差異を防ぐための設定外出し
  7. スターター計画（今日から着手できる最短ルート）
    1. ステップ1：棚卸しと共通枠の導入
    2. ステップ2：配列I/Oへの置換と計測
    3. ステップ3：責務分離と命名統一
    4. ステップ4：共通化とテストの整備
  8. 直感に頼らず「数値と枠」で進めるのがリファクタリングの近道

ねらい：動作は変えずに「読みやすく・直しやすく・速く」する

VBAのリファクタリングは、機能を増やすことではありません。今の動作を維持したまま、構造を整えて保守性と速度を上げます。安全に進める鍵は、小さく区切って直すこと、毎回の動作確認、開始と終了の共通枠、責務分離、配列I/Oです。初心者でも貼って使えるテンプレートと、直し方の勘所を具体的なコードと例題で解説します。

重要ポイントの深掘り

「動作不変」を最優先にします。見た目や結果が同じであることを確認しながら、内部の形だけを良くします。最初に開始・終了の共通枠を入れて“落ちても戻る”状態を作ってから、セル往復の撤廃や責務分離に進むと安全です。

リファクタリングの安全な流れ（7ステップ）

現状の棚卸しで「直す対象」を見える化する

どのモジュールに何のプロシージャがあり、Public/Private、Option Explicitの有無を一覧化します。可視化すると、巨大Subや重複、命名の揺れが具体的に分かります。

Sub AuditModules()
    Dim vbComp As Object, ws As Worksheet, r As Long: r = 2
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("Audit")
    If ws Is Nothing Then Set ws = ThisWorkbook.Worksheets.Add: ws.Name = "Audit"
    On Error GoTo 0
    ws.Range("A1:D1").Value = Array("Module", "Proc", "Scope", "Has Option Explicit")

    For Each vbComp In ThisWorkbook.VBProject.VBComponents
        If vbComp.Type = 1 Then
            Dim hasExplicit As Boolean
            hasExplicit = InStr(1, vbComp.CodeModule.Lines(1, WorksheetFunction.Min(30, vbComp.CodeModule.CountOfLines)), "Option Explicit", vbTextCompare) > 0
            Dim i As Long
            For i = 1 To vbComp.CodeModule.CountOfLines
                Dim ln As String: ln = vbComp.CodeModule.Lines(i, 1)
                If ln Like "Public * Sub *" Or ln Like "Public * Function *" Or ln Like "Private * Sub *" Or ln Like "Private * Function *" Then
                    ws.Cells(r, 1).Value = vbComp.Name
                    ws.Cells(r, 2).Value = Split(Replace(Trim$(ln), "(", " ("), " ")(UBound(Split(Replace(Trim$(ln), "(", " ("), " ")))
                    ws.Cells(r, 3).Value = IIf(Left$(ln, 6) = "Public", "Public", "Private")
                    ws.Cells(r, 4).Value = IIf(hasExplicit, "Yes", "No")
                    r = r + 1
                End If
            Next
        End If
    Next
    ws.Columns.AutoFit
    MsgBox "棚卸し完了（Auditシート）"
End Sub
VB

開始・終了・例外の共通枠を先に導入する

どこで失敗しても環境を復帰できるように、全ての入口を共通枠に通します。描画・イベント・再計算を止め、最後に必ず戻します。

' ModApp
Option Explicit

Public Sub AppEnter(Optional ByVal status As String = "")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    If Len(status) > 0 Then Application.StatusBar = status
End Sub

Public Sub AppLeave()
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB

入口名を Run_XXXX に統一して操作点を一本化する

実行の起点を揃えると、UIボタンやメニューからの紐付けが簡単になります。入口で共通枠と例外処理を徹底します。

' ModService
Option Explicit

Public Sub Run_EmployeeImport()
    On Error GoTo EH
    AppEnter "社員取込"
    ' …処理本体…
    AppLeave
    MsgBox "社員取込が完了しました。"
    Exit Sub
EH:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB

責務分離で「直す場所」を明確化する

UI（ボタン・フォーム）、Service（業務フロー）、Logic（計算・判定の純粋関数）、IO（読み書き）、Config（設定読み取り）に分けます。セル操作はIO、ルールはLogic、入口はServiceのみにします。

セル往復を撤廃し、配列I/Oへ置換する

Rangeから二次元配列に一括で読み、配列上で処理し、まとめて書き戻します。最も効く改善です。

' ModIO
Option Explicit

Public Function ReadRegion(ByVal topLeft As Range) As Variant
    ReadRegion = topLeft.CurrentRegion.Value
End Function

Public Sub WriteArray(ByVal ws As Worksheet, ByVal topLeft As String, ByVal arr As Variant)
    ws.Range(topLeft).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub
VB

重複処理を共通モジュールへ集約する

最終行・タイムスタンプ・安全ファイル名・進捗など、よく使う処理は一か所にまとめ、Public関数の契約（名前・引数・戻り型）は固定します。

テスト・計測・ログで「動作不変」と「効果」を確認する

小さい関数にテストSubを用意して期待値を確認します。Timerで区間計測し、ログへ記録して変更前後を比較します。

Sub Measure(ByVal label As String, ByVal runner As String)
    Dim t As Double: t = Timer
    Application.Run runner
    Debug.Print label & ": " & Format(Timer - t, "0.000") & "s"
End Sub
VB

よくある悪い癖をその場で直す

Select/Activateの多用を「直接参照」に置き換える

選択は描画やイベントの余計なコストになります。Withやオブジェクト変数で直接指定します。

With Worksheets("Input")
    .Range("A1").Value = "OK"
End With
VB

1セルずつの読み書きを「配列I/O」に置き換える

数万行で固まる主因です。CurrentRegionで一括読みし、結果をまとめて書き戻します。

Dim arr As Variant: arr = Worksheets("Input").Range("A1").CurrentRegion.Value
Dim rows As Long: rows = UBound(arr, 1)
Dim result() As Variant: ReDim result(1 To rows, 1 To 1)

Dim r As Long
For r = 2 To rows
    result(r, 1) = CStr(arr(r, 1)) & CStr(arr(r, 2))
Next

Worksheets("Output").Range("E1").Resize(rows, 1).Value = result
VB

ロジックからセル参照を排除して「純粋関数」にする

テスト可能になり、列順変更にも強くなります。入力配列としきい値などの値だけを渡し、配列を返す形にします。

' ModLogic
Option Explicit

Public Function AddPassFlag(ByVal data As Variant, ByVal th As Double) As Variant
    Dim rows As Long: rows = UBound(data, 1)
    Dim cols As Long: cols = UBound(data, 2)
    Dim out() As Variant: ReDim out(1 To rows, 1 To cols + 1)

    Dim c As Long
    For c = 1 To cols: out(1, c) = data(1, c): Next
    out(1, cols + 1) = "合格"

    Dim r As Long
    For r = 2 To rows
        For c = 1 To cols: out(r, c) = data(r, c): Next
        out(r, cols + 1) = IIf(Val(data(r, 5)) >= th, "○", "×")
    Next

    AddPassFlag = out
End Function
VB

定数直書きをやめて「Configから読む」ようにする

本番差異に強くなります。名前定義やConfigシートから読み取り、型を検証して返します。

' ModConfig
Option Explicit

Private Function ConfigSheet() As Worksheet
    Set ConfigSheet = ThisWorkbook.Worksheets("Config")
End Function

Public Function GetConfigString(ByVal key As String) As String
    Dim ws As Worksheet: Set ws = ConfigSheet()
    Dim last As Long: last = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim r As Long
    For r = 2 To last
        If StrComp(CStr(ws.Cells(r, "A").Value), key, vbTextCompare) = 0 Then
            GetConfigString = Trim$(CStr(ws.Cells(r, "B").Value))
            Exit Function
        End If
    Next
    Err.Raise 900, , "Configキーが見つかりません: " & key
End Function
VB

DoEventsの呼びすぎを「1〜5%刻み」に間引く

UI応答を保ちつつ速度を維持します。共通の進捗関数に集約します。

Sub ProgressThrottled(ByVal cur As Long, ByVal total As Long, Optional ByVal label As String = "進捗")
    If total <= 0 Then Exit Sub
    Dim stepN As Long: stepN = Application.WorksheetFunction.Max(1, total \ 100)
    If cur Mod stepN = 0 Then
        Application.StatusBar = label & " " & Format(cur / total, "0%") & " (" & cur & "/" & total & ")"
        DoEvents
    End If
End Sub
VB

分離テンプレートで「読めて直せる」形へ

業務フローと純粋ロジックを分けたサンプル

入口はRun_XXXXで揃え、Config→IO→Logic→IOの流れにします。例外時もAppLeaveで復帰します。

' ModService
Option Explicit

Public Sub Run_AddPassFlag()
    On Error GoTo EH
    AppEnter "合格判定"
    Dim inputSheet As String: inputSheet = GetConfigString("INPUT_SHEET")
    Dim outputSheet As String: outputSheet = GetConfigString("OUTPUT_SHEET")
    Dim th As Double: th = CDbl(GetConfigString("THRESHOLD"))

    Dim data As Variant: data = ReadRegion(Worksheets(inputSheet).Range("A1"))
    Dim out As Variant: out = AddPassFlag(data, th)
    WriteArray Worksheets(outputSheet), "A1", out

    AppLeave
    MsgBox "完了"
    Exit Sub
EH:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB

例題で練習しながら確かめる

例題1：共通枠の導入で「落ちても戻る」ことを確認する

既存の処理をAppEnter/AppLeaveで包み、意図的にエラーを出しても描画・イベント・計算が復帰するかを確かめます。これができれば以降の変更が怖くなくなります。

例題2：セル往復を配列I/Oへ置換し、処理時間を計測する

Timerで前後の時間を計測し、数万行の連結や判定が桁違いに速くなることを確認します。計測結果をImmediateに出すか、PerfLogシートへ記録すると効果が共有できます。

例題3：ロジックを純粋関数に切り出し、単体テストを回す

小さな配列を渡して期待値をImmediateで確認します。配列だけで動くなら、シート構成に依存せず安全に変更可能です。

Sub Test_AddPassFlag()
    Dim data(1 To 3, 1 To 5) As Variant
    data(1, 5) = "Score": data(2, 5) = 80: data(3, 5) = 60
    Dim out As Variant: out = AddPassFlag(data, 70)
    Debug.Print out(2, 6)  ' ○
    Debug.Print out(3, 6)  ' ×
End Sub
VB

実務の落とし穴を先回りで避ける

一気に直して壊すことを避けるための小さな刻み方

巨大Subをそのまま書き換えず、まず入口の枠を入れ、次に配列I/O化、次に責務分離……と段階を踏みます。各段階で動作確認と計測を行い、効果と安全を同時に担保します。

入口が複数で制御不能になる前に統一する

Run_XXXXへ揃えて、UIはそこだけ呼ぶ形にします。入口が散らばると進捗・エラー処理・環境復帰が揺れて事故につながります。

定数直書きによる本番差異を防ぐための設定外出し

しきい値やパスはConfigから読み、型を検証します。誤設定はFail Fastで止め、直すべき場所を具体的に表示します。

スターター計画（今日から着手できる最短ルート）

ステップ1：棚卸しと共通枠の導入

AuditModulesで状況を見える化し、すべての入口にAppEnter/AppLeaveを導入します。これで安全網が張れます。

ステップ2：配列I/Oへの置換と計測

最も重いループを選び、Range→配列→一括書き戻しへ置換します。Timerで前後の秒数を出し、効果を確認します。

ステップ3：責務分離と命名統一

UI/Service/Logic/IO/Configに分け、入口はRun_XXXX、ヘルパーは動詞＋目的語で命名します。ロジックは配列・値のみを扱う純粋関数にします。

ステップ4：共通化とテストの整備

最終行・進捗・タイムスタンプなどの重複を共通モジュールへ移し、テストSubを各ロジックに用意します。変更時に即確認できる体制が整います。

直感に頼らず「数値と枠」で進めるのがリファクタリングの近道

動作不変の確認を小さく繰り返し、効果はTimerで測って可視化します。開始・終了の枠が支えになり、配列I/Oと責務分離が構造を強くします。どこから着手するか迷うなら、いちばん遅いループの配列化から始めましょう。最短で“読める・速い・壊れない”に近づけます。

スポンサーリンク
タイトルとURLをコピーしました