Excel VBA 逆引き集 | テスト自動化

Excel VBA
スポンサーリンク
  1. ねらい：Excelで「テスト自動化」を仕組みにして、毎回同じ品質を素早く確認する
  2. テスト基盤：アサーションとランナーで“自動化の土台”を作る
    1. 判定（アサーション）の最小セット
    2. テストランナー（登録→一括実行→結果シート出力）
    3. 重要ポイントの深掘り
  3. 自動起動とスケジュール：開いた瞬間にテストが走る
    1. ブックイベントでテスト自動実行
    2. 重要ポイントの深掘り
  4. フィクスチャ（固定入力）とモック（依存の差し替え）
    1. 小さな固定データを返すフィクスチャ
    2. 外部依存のモック差し替え
    3. 重要ポイントの深掘り
  5. 実例テスト群：検証・集計・ソート・ゴールデン比較
    1. 必須・型チェックのユニットテスト
    2. キー別合計のユニットテスト
    3. 安定ソートのユニットテスト
    4. ゴールデン比較（出力が大きい処理の自動照合）
    5. 重要ポイントの深掘り
  6. レポート自動出力：CSV保存・色分け・履歴化
    1. テスト結果のCSV出力
    2. 重要ポイントの深掘り
  7. エンドツーエンド自動化：ETL処理をテストから起動し、完了を確認
    1. テストから処理を起動して期待結果を検証
    2. 重要ポイントの深掘り
  8. 落とし穴と対策（深掘り）
    1. シート往復でテストが遅い
    2. 外部依存で結果が揺れる
    3. 合格基準が曖昧
    4. 大きな出力の目視比較
  9. まとめ：テスト枠を“最初に”入れて、ブックを開くだけで品質を確認できるようにする

ねらい：Excelで「テスト自動化」を仕組みにして、毎回同じ品質を素早く確認する

人手のチェックは抜けやすく、時間もかかります。VBAでもテスト枠を最初に入れておけば、ブックを開くだけで自動テストが走り、結果が見える化されます。ここでは初心者でも貼って動く最小テンプレを「テストランナー」「アサーション」「フィクスチャ」「自動起動」「レポート出力」「モック差し替え」「ゴールデン比較」までまとめて提供し、重要ポイントを深掘りします。

テスト基盤：アサーションとランナーで“自動化の土台”を作る

判定（アサーション）の最小セット

' 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
            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
    Dim ws As Worksheet: Set ws = PrepareOut("Tests")
    ws.Range("A1:E1").Value = Array("Case", "Result", "Message", "Time(sec)", "When")

    Dim i As Long, rowOut As Long: rowOut = 2
    For i = 1 To IIf(tests Is Nothing, 0, 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")
        ws.Cells(rowOut, "E").Value = Now
        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

    If Failed > 0 Then
        MsgBox "テスト失敗あり: Passed=" & Passed & " Failed=" & Failed, vbExclamation
    Else
        MsgBox "テスト成功: Passed=" & Passed, vbInformation
    End If
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

重要ポイントの深掘り

テスト名を文字列で登録し、Application.Runで呼び出す設計はVBAでは最も安定します。結果はシートに時刻付きで出力し、失敗時だけ詳細メッセージを残すと、後から解析しやすくなります。合格基準（Failed=0）をメッセージで即表示して、配布前の見落としを防ぎます。

自動起動とスケジュール：開いた瞬間にテストが走る

ブックイベントでテスト自動実行

' ThisWorkbook
Option Explicit

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

重要ポイントの深掘り

ブックを開くだけでテストが毎回走るようにしておくと「壊れたまま出荷」がなくなります。Windowsタスクスケジューラで毎朝このブックを開く設定にすれば、定時自動テストもすぐ実現できます。

フィクスチャ（固定入力）とモック（依存の差し替え）

小さな固定データを返すフィクスチャ

' 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
VB

外部依存のモック差し替え

' ModDeps.bas
Option Explicit

Public Function ReadCsv(ByVal path As String) As Variant
    ' 実運用版（省略）
End Function

Public Function ReadCsv_Mock(ByVal path As String) As Variant
    ReadCsv_Mock = SmallTable()
End Function
VB

重要ポイントの深掘り

テストは「同じ入力ならいつも同じ出力」を保証するのが大事です。外部ファイルやネットなど揺らぎがある依存は、関数に切り出してテスト時だけモックに入れ替えます。これで失敗の原因が“コードの変更のみ”に限定され、解析が楽になります。

実例テスト群：検証・集計・ソート・ゴールデン比較

必須・型チェックのユニットテスト

' 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, "エラー０件であるべき"
    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"
    AssertEquals 40, d("a"), "a=20+20"
    AssertEquals 5, d("c"), "c=5"
    EndCase
End Sub
VB

安定ソートのユニットテスト

' UTIL_SortStable.bas（安定ソート本体は前提として用意）
' Test_SortStable.bas
Option Explicit

Public Sub Test_SortStable_ByName()
    Dim a As Variant: a = SmallTable()
    StableSort2D a, 2, True
    StartCase "StableSort by Name"
    AssertEquals "Able", a(2, 2), "先頭はAble"
    AssertEquals "Able", a(3, 2), "同値は順序保持"
    AssertEquals "Baker", a(4, 2), "その次がBaker"
    EndCase
End Sub
VB

ゴールデン比較（出力が大きい処理の自動照合）

' 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
' Test_Golden.bas
Option Explicit

Public Sub Test_GoldenReport()
    ' 事前に Report シートへ期待どおりのアウトプットを作成済みとする
    SaveGolden "Report", "Golden_Report"
    ' 仕様変更後に再実行した出力と比較
    CompareWithGolden "Report", "Golden_Report"
End Sub
VB

重要ポイントの深掘り

ユニットテストは「小さな期待」を積み上げます。出力が大きい統合処理は、期待結果をゴールデンとして保存し、毎回自動比較するのが効率的です。差が出たらテストに失敗として残り、原因調査へ直行できます。

レポート自動出力：CSV保存・色分け・履歴化

テスト結果のCSV出力

' ModReport.bas
Option Explicit

Public Sub ExportTestResultsCsv()
    Dim ws As Worksheet: Set ws = Worksheets("Tests")
    Dim path As String: path = ThisWorkbook.Path & "\test_results_" & Format(Now, "yyyymmdd_HhNnSs") & ".csv"
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    Dim a As Variant: a = rg.Value

    Dim h As Integer: h = FreeFile
    Open path For Output As #h
    Dim r As Long, c As Long, line As String
    For r = 1 To UBound(a, 1)
        line = ""
        For c = 1 To UBound(a, 2)
            line = line & IIf(c > 1, ",", "") & Replace(CStr(a(r, c)), ",", " ")
        Next
        Print #h, line
    Next
    Close #h
    MsgBox "CSV出力: " & path, vbInformation
End Sub
VB

重要ポイントの深掘り

テスト履歴は資産です。時刻入りファイル名でCSV保存すれば、時系列で成功率・所要時間の推移が追えます。シート側で条件付き書式（Result=ERRORを赤）を入れておくと、失敗が一目で分かります。

エンドツーエンド自動化：ETL処理をテストから起動し、完了を確認

テストから処理を起動して期待結果を検証

' Test_E2E.bas
Option Explicit

Public Sub Test_E2E_ImportTransformExport()
    StartCase "E2E ETL"
    ' 入力準備（テンポラリCSV作成）
    Dim tmp As String: tmp = ThisWorkbook.Path & "\tmp_input.csv"
    Dim h As Integer: h = FreeFile
    Open tmp For Output As #h
    Print #h, "Key,Name,Amount"
    Print #h, "a,Able,10"
    Print #h, "b,Baker,20"
    Close #h

    ' 処理本体を呼ぶ（例：RunPipelineOnce）
    RunPipelineOnce tmp

    ' 出力検証（Reportシートの先頭行）
    Dim ws As Worksheet: Set ws = Worksheets("Report")
    Dim a As Variant: a = ws.Range("A1").CurrentRegion.Value
    AssertEquals "Key", a(1, 1), "ヘッダ一致"
    AssertEquals "a", a(2, 1), "1行目キー"
    AssertEquals "b", a(3, 1), "2行目キー"

    ' 後片付け
    On Error Resume Next: Kill tmp: On Error GoTo 0
    EndCase
End Sub
VB
' ModPipelineOnce.bas（ETLの単回実行をテストしやすい形へ切り出す）
Option Explicit

Public Sub RunPipelineOnce(ByVal csvPath As String)
    Dim data As Variant: data = LoadCsv(csvPath)
    Dim ws As Worksheet: Set ws = PrepareOut("Report")
    ws.Range("A1").Resize(UBound(data, 1), UBound(data, 2)).Value = data
End Sub

Private Function LoadCsv(ByVal path As String) As Variant
    Dim st As Object: Set st = CreateObject("ADODB.Stream")
    st.Type = 2: st.Charset = "UTF-8": st.Open: st.LoadFromFile path
    Dim text As String: text = st.ReadText: st.Close
    Dim lines() As String: lines = Split(Replace(text, vbCrLf, vbLf), vbLf)
    Dim cols As Long: cols = UBound(Split(lines(0), ",")) + 1
    Dim a() As Variant: ReDim a(1 To UBound(lines) + 1, 1 To cols)
    Dim r As Long, c As Long
    Dim head() As String: head = Split(lines(0), ",")
    For c = 1 To cols: a(1, c) = head(c - 1): Next
    For r = 2 To UBound(a, 1)
        If r - 1 > UBound(lines) Then Exit For
        Dim rec() As String: rec = Split(lines(r - 1), ",")
        For c = 1 To cols: a(r, c) = IIf(c - 1 <= UBound(rec), rec(c - 1), ""): Next
    Next
    LoadCsv = a
End Function

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

重要ポイントの深掘り

エンドツーエンドは「入→処理→出」をテストから一括で起動し、最後に期待結果を検証します。テンポラリファイルを使い、終了時に必ず削除。処理本体は“テストしやすい単回関数”に切り出しておくと、自動化が簡単になります。

落とし穴と対策（深掘り）

シート往復でテストが遅い

配列I/Oを標準に。入力配列→ロジック→出力配列→最後に一括書き戻しが最速です。

外部依存で結果が揺れる

ファイル・ネット・時刻・乱数は関数に切り出し、テスト時はモック／固定値に切り替える。

合格基準が曖昧

Failed=0を明示。失敗時は即メッセージと赤行表示。履歴をCSVへ自動保存して、傾向も追う。

大きな出力の目視比較

ゴールデン比較に切り替え。行ごと厳密一致のアサーションで自動判定し、差異があれば原因特定へすぐ進む。

まとめ：テスト枠を“最初に”入れて、ブックを開くだけで品質を確認できるようにする

アサーションとランナー、フィクスチャ、モック、ゴールデン比較、CSVレポートまで揃えれば、Excelでもテストは十分自動化できます。毎回同じタイミングで同じテストが走り、失敗は一目で分かる。これが“壊れない拡張”への最短ルートです。

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