Excel VBA 逆引き集 | 品質保証（テスト）

Excel VBA
スポンサーリンク

ねらい：Excelで「品質保証（テスト）」を仕組みにして、拡張しても壊れないようにする

手作業の確認は限界があります。Excel VBAでも「テストの型」を最初に入れておくと、機能追加や修正のたびに自動検証でき、安心して前進できます。ここでは、初心者でも貼って動かせるテスト枠（ランナー、アサーション、フィクスチャ、ゴールデンマスター、依存の差し替え）を示し、検証・集計・ソートの例題まで通します。重要なのは、配列I/Oでテストを速く回し、同じ入力に同じ出力が出る「再現性」を担保することです。

テストの基本設計：小さいユニットテストを積み重ねる

テストは「ユニット→統合→エンドツーエンド」の順で小さいものを積み重ねます。まずは単機能（検証、集計、ソートなど）を、入力と期待値のペアで確かめるユニットテストから始めます。外部I/O（ファイル、ネット、時刻）は結果が揺れやすいので、関数化してテスト中だけ差し替えます。これにより「同じテストは毎回同じ結果」になり、信頼性が跳ね上がります。

重要ポイントの深掘り

ユニットテストは「速く・頻繁に」回せるのが価値です。配列を渡して配列で返す関数にしておくと、シート往復がなく高速で、期待値比較も容易です。揺らぎ要因（現在時刻、乱数、環境差）はすべて関数経由にし、テスト中は固定値に切り替えます。

アサーションとテストランナーのテンプレート

テストで使う「判定（アサーション）」と「結果をまとめて出すランナー」を用意します。プロシージャ名を登録し、順に実行して合否とメッセージをシートへ出力します。

アサーションの最小セット

' ModAssert.bas
Option Explicit
Public Passed As Long, Failed As Long, CurrentCase As String

Public Sub StartCase(ByVal name As String)
    CurrentCase = name
End Sub

Public Sub EndCase()
    CurrentCase = ""
End Sub

Public Sub AssertTrue(ByVal cond As Boolean, ByVal msg As String)
    If cond Then
        Passed = Passed + 1
    Else
        Failed = Failed + 1
        Debug.Print "FAIL: "; CurrentCase; " - "; msg
    End If
End Sub

Public Sub AssertEquals(ByVal expected As Variant, ByVal actual As Variant, ByVal msg As String)
    Dim ok As Boolean
    ok = (CStr(expected) = CStr(actual))
    AssertTrue ok, msg & " (expected=" & CStr(expected) & ", actual=" & CStr(actual) & ")"
End Sub

Public Sub AssertArray2DEquals(ByRef exp As Variant, ByRef act As Variant, ByVal msg As String)
    Dim ok As Boolean: ok = True
    If UBound(exp, 1) <> UBound(act, 1) Or UBound(exp, 2) <> UBound(act, 2) Then ok = False
    Dim r As Long, c As Long
    If ok Then
        For r = 1 To UBound(exp, 1)
            For c = 1 To UBound(exp, 2)
                If CStr(exp(r, c)) <> CStr(act(r, c)) Then
                    ok = False
                    Exit For
                End If
            Next
            If Not ok Then Exit For
        Next
    End If
    AssertTrue ok, msg
End Sub
VB

テストランナーと結果出力

' ModTestRunner.bas
Option Explicit
Private tests As Collection

Public Sub Register(ByVal procName As String)
    If tests Is Nothing Then Set tests = New Collection
    tests.Add procName
End Sub

Public Sub RunAllTests()
    Passed = 0: Failed = 0
    If tests Is Nothing Or tests.Count = 0 Then
        MsgBox "テストが登録されていません。", vbExclamation
        Exit Sub
    End If

    Dim ws As Worksheet: Set ws = PrepareOut("Tests")
    ws.Range("A1:D1").Value = Array("Case", "Result", "Message", "Time")

    Dim i As Long, rowOut As Long: rowOut = 2
    For i = 1 To tests.Count
        Dim name As String: name = CStr(tests(i))
        StartCase name
        Dim t0 As Double: t0 = Timer
        On Error Resume Next
        Application.Run tests(i)
        Dim errDesc As String: errDesc = Err.Description
        On Error GoTo 0
        EndCase

        Dim res As String: res = IIf(Len(errDesc) = 0, "OK", "ERROR")
        ws.Cells(rowOut, "A").Value = name
        ws.Cells(rowOut, "B").Value = res
        ws.Cells(rowOut, "C").Value = errDesc
        ws.Cells(rowOut, "D").Value = Format(Timer - t0, "0.000")
        rowOut = rowOut + 1
    Next
    ws.Cells(rowOut, "A").Value = "Passed": ws.Cells(rowOut, "B").Value = Passed
    ws.Cells(rowOut + 1, "A").Value = "Failed": ws.Cells(rowOut + 1, "B").Value = Failed
    ws.Columns.AutoFit
    MsgBox "テスト完了: Passed=" & Passed & " Failed=" & Failed, vbInformation
End Sub

Private Function PrepareOut(ByVal name As String) As Worksheet
    Dim ws As Worksheet
    On Error Resume Next: Set ws = Worksheets(name): On Error GoTo 0
    If ws Is Nothing Then Set ws = Worksheets.Add: ws.Name = name
    ws.Cells.Clear
    Set PrepareOut = ws
End Function
VB

重要ポイントの深掘り

VBAは関数ポインタを直接渡せないため、プロシージャ名を文字列で登録し Application.Run で呼ぶのがシンプルです。アサーションは「失敗時だけ詳しく」出すと、ログが読みやすくなります。シートに「Case/Result/Message/Time」を残すことで、後から原因分析が容易です。

フィクスチャ（固定データ）と再現性の確保

テストごとに「小さく・明確な」入力配列を作るフィクスチャを用意します。乱数や時刻などの揺らぎはコントロールし、毎回同じ結果になるようにします。

フィクスチャ生成と揺らぎ制御

' ModFixture.bas
Option Explicit

Public Function SmallTable() As Variant
    Dim a() As Variant: ReDim a(1 To 6, 1 To 3)
    a(1, 1) = "Key": a(1, 2) = "Name": a(1, 3) = "Amount"
    a(2, 1) = "b": a(2, 2) = "Baker": a(2, 3) = "10"
    a(3, 1) = "a": a(3, 2) = "Able": a(3, 3) = "20"
    a(4, 1) = "a": a(4, 2) = "Able": a(4, 3) = "20"
    a(5, 1) = "c": a(5, 2) = "Charlie": a(5, 3) = "5"
    a(6, 1) = "b": a(6, 2) = "Baker": a(6, 3) = "7"
    SmallTable = a
End Function

Public Sub SeedRandom(Optional ByVal seed As Long = 42)
    Randomize seed
End Sub

Public Function NowClock() As Date
    NowClock = #1/1/2025 12:00:00 AM#  ' テスト時は固定時刻
End Function
VB

重要ポイントの深掘り

テストで乱数や時刻を使う場合は、必ずテスト用のラッパー関数に寄せて固定値にします。外部ファイルを読むテストは、テンポラリフォルダを作って小さなCSVを用意し、実行後に必ず削除します。これで「他の環境でも同じように通る」テストになります。

例題テスト：検証・集計・ソートのユニットテスト

単機能を確かめる three-in-one の例を示します。検証は必須・型チェック、集計はキーごとに合計、ソートは安定ソートで元順序保持を確認します。

検証のテスト

' Test_Validate.bas
Option Explicit

Public Sub Test_Validate_RequiredAndType()
    Dim a As Variant: a = SmallTable()
    Dim errs As New Collection
    Dim r As Long
    For r = 2 To UBound(a, 1)
        If Len(Trim$(CStr(a(r, 1)))) = 0 Then errs.Add "Key empty at " & r
        If Not IsNumeric(a(r, 3)) Then errs.Add "Amount not numeric at " & r
    Next
    StartCase "Validate Required/Type"
    AssertEquals 0, errs.Count, "エラーは0件のはず"
    EndCase
End Sub
VB

集計のテスト

' Test_GroupSum.bas
Option Explicit

Public Sub Test_GroupSum_ByKey()
    Dim a As Variant: a = SmallTable()
    Dim d As Object: Set d = CreateObject("Scripting.Dictionary")
    Dim r As Long
    For r = 2 To UBound(a, 1)
        Dim k As String: k = LCase$(Trim$(CStr(a(r, 1))))
        Dim v As Double: v = Val(CStr(a(r, 3)))
        d(k) = IIf(d.Exists(k), d(k) + v, v)
    Next
    StartCase "GroupSum by Key"
    AssertEquals 17, d("b"), "bの合計は10+7=17"
    AssertEquals 40, d("a"), "aの合計は20+20=40"
    AssertEquals 5, d("c"), "cの合計は5"
    EndCase
End Sub
VB

ソートのテスト（安定性確認）

' Test_SortStable.bas
Option Explicit

Public Sub Test_SortStable_ByName()
    Dim a As Variant: a = SmallTable()
    Call StableSort2D(a, 2, True) ' 2列目Nameで昇順（安定）
    StartCase "StableSort by Name"
    AssertEquals "Able", a(2, 2), "先頭データはAble"
    AssertEquals "Able", a(3, 2), "次もAble（重複保持）"
    AssertEquals "Baker", a(4, 2), "その次はBaker"
    EndCase
End Sub
VB

重要ポイントの深掘り

期待値は「なぜその値になるか」が説明できる小さなデータで作ります。安定ソートは「同値の元順序が保たれているか」を確認すると、現場で起きやすいタイブレーク事故を防げます。集計は文字と数値が混在しがちなので、ValやCDblで数値化して比較します。

ゴールデンマスター（期待出力の保存）と差分テスト

「複数ステップの処理を通した最終出力」が大きい場合、期待出力（ゴールデン）をCSVやシートに保存しておき、毎回の出力とハッシュや行差分で比較します。

ゴールデンの保存と比較

' ModGolden.bas
Option Explicit

Public Sub SaveGolden(ByVal wsName As String, ByVal goldenSheet As String)
    Dim ws As Worksheet: Set ws = Worksheets(wsName)
    Dim a As Variant: a = ws.Range("A1").CurrentRegion.Value
    Dim g As Worksheet: Set g = PrepareOut(goldenSheet)
    g.Range("A1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Public Sub CompareWithGolden(ByVal wsName As String, ByVal goldenSheet As String)
    Dim ws As Worksheet: Set ws = Worksheets(wsName)
    Dim g As Worksheet: Set g = Worksheets(goldenSheet)
    Dim a As Variant: a = ws.Range("A1").CurrentRegion.Value
    Dim b As Variant: b = g.Range("A1").CurrentRegion.Value

    StartCase "GoldenCompare " & wsName
    AssertArray2DEquals b, a, "現在出力はGoldenと一致するべき"
    EndCase
End Sub

Private Function PrepareOut(ByVal name As String) As Worksheet
    Dim s As Worksheet
    On Error Resume Next: Set s = Worksheets(name): On Error GoTo 0
    If s Is Nothing Then Set s = Worksheets.Add: s.Name = name
    s.Cells.Clear
    Set PrepareOut = s
End Function
VB

重要ポイントの深掘り

ゴールデンは「仕様合意済みの期待出力」です。仕様変更の際はゴールデンを更新し、その変更理由を残します。行ごとの厳密一致で比較し、違いが出たらテストで赤く表示するなど、発見しやすくします。

依存の切り離しとモック（差し替え）

外部依存を関数経由にしておくと、テスト時は「モック（偽物）」に差し替え、安定した検証ができます。以下は時計とファイル読みを差し替える例です。

差し替え可能な依存とモック

' ModDeps.bas
Option Explicit
Public Function ClockNow() As Date
    ClockNow = Now
End Function

Public Function ReadCsv(ByVal path As String) As Variant
    ' 実運用のCSV読込（省略）
End Function

' テスト用モック
Public Function ClockNow_Mock() As Date
    ClockNow_Mock = #1/1/2025#
End Function

Public Function ReadCsv_Mock(ByVal path As String) As Variant
    ReadCsv_Mock = SmallTable()
End Function
VB
' Test_Deps.bas
Option Explicit

Public Sub Test_UsesMocks()
    Dim t As Date: t = ClockNow_Mock()
    StartCase "Clock Mock"
    AssertEquals CDate("2025-01-01"), t, "固定時刻が返るべき"
    EndCase

    Dim a As Variant: a = ReadCsv_Mock("dummy")
    StartCase "CSV Mock"
    AssertEquals "Key", a(1, 1), "ヘッダがKeyであるべき"
    EndCase
End Sub
VB

重要ポイントの深掘り

依存を「関数名で差し替えるだけ」にすると、VBAでも十分モックが扱えます。実稼働コードは本依存、テスト中はモックに切り替えることで、ネットワークやファイルの遅延・不安定に影響されず、確実な検証が可能になります。

自動実行と合格基準の運用

ブックを開いたらテストを走らせ、合格しなければ分かる仕組みにします。毎回の結果はシートとメッセージで可視化し、失敗件数が残っている状態で配布しないルールにします。

自動起動のフック

' ThisWorkbook
Option Explicit

Private Sub Workbook_Open()
    Register "Test_Validate_RequiredAndType"
    Register "Test_GroupSum_ByKey"
    Register "Test_SortStable_ByName"
    Register "Test_UsesMocks"
    RunAllTests
End Sub
VB

重要ポイントの深掘り

毎回テストが走るだけで「壊れたまま出荷」が減ります。失敗を見逃さないために、「Failed>0なら警告表示」など、簡単でも良い合格基準を入れておきます。定時自動テストはWindowsタスクスケジューラでブックを開くだけでも実現できます。

まとめ：テストの“型”を先に入れて、配列中心で速く回す

アサーションとランナー、フィクスチャ、依存差し替え、ゴールデン比較を用意し、検証・集計・ソートのユニットテストから始めると、Excelでも品質保証は十分回ります。揺らぎを抑え、配列I/Oで高速化し、毎回自動で走る仕組みを整える。これが「拡張しても壊れない」ための最短ルートです。

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