VBAで奇数or偶数をチェックするモック作る


## VBA：副作用を分離し、ロジックを強靭にテストする一手法 ― 偶奇判定の例

VBAでExcelのセルを直接操作するようなコードはテストが厄介だ。ここでは、その副作用をインターフェイスで分離し、モックを用いてロジック本体を確実にテストする骨太なアプローチを示す。例として「数値の偶奇を判定し結果をセルに記す」処理を用いる。

### 構造 ― モジュール一覧

プロジェクトは以下のモジュールで構成される。

* **標準モジュール**
    * `G`
    * `MainModule`
    * `MockUtil`
    * `Test_AnswerWriter`
* **クラスモジュール**
    * `AnswerWriter`
    * `Effect`
    * `EffectMock`
    * `IEffect`

### コード ― 各モジュールの全容

#### `IEffect` (クラスモジュール)
副作用操作の契約だ。今回は`WriteMatrix`を利用する。

```vba
Option Explicit

'指定された列の最終行を取得
Public Function GetEndRow(ByVal Sheet As Worksheet, ByVal Col As Long) As Long
End Function

'指定された範囲の値を2次元配列で取得
Public Function ReadMatrix( _
        ByVal Sheet As Worksheet, _
        ByVal Row1 As Long, _
        ByVal Col1 As Long, _
        ByVal Row2 As Long, _
        ByVal Col2 As Long) As Variant
End Function

'指定された範囲に2次元配列の値を書き込む
Public Sub WriteMatrix( _
        ByVal Sheet As Worksheet, _
        ByVal Row1 As Long, _
        ByVal Col1 As Long, _
        ByVal Row2 As Long, _
        ByVal Col2 As Long, _
        ByVal Matrix As Variant)
End Sub
```

#### `Effect` (クラスモジュール)
`IEffect`の具現化。実際のセル書き込みを担う。

```vba
Option Explicit
Implements IEffect

Public Function IEffect_GetEndRow(ByVal Sheet As Worksheet, ByVal Col As Long) As Long
    IEffect_GetEndRow = Sheet.Cells(Sheet.Rows.Count, Col).End(xlUp).Row
    End Function

Public Function IEffect_ReadMatrix( _
        ByVal Sheet As Worksheet, _
        ByVal Row1 As Long, _
        ByVal Col1 As Long, _
        ByVal Row2 As Long, _
        ByVal Col2 As Long) As Variant
    IEffect_ReadMatrix = Sheet.Range(Sheet.Cells(Row1, Col1), Sheet.Cells(Row2, Col2)).Value
    End Function

Public Sub IEffect_WriteMatrix( _
        ByVal Sheet As Worksheet, _
        ByVal Row1 As Long, _
        ByVal Col1 As Long, _
        ByVal Row2 As Long, _
        ByVal Col2 As Long, _
        ByVal Matrix As Variant)
    Sheet.Range(Sheet.Cells(Row1, Col1), Sheet.Cells(Row2, Col2)).Value = Matrix
    End Sub
```

#### `EffectMock` (クラスモジュール)
テスト時の影武者。副作用を起こさず、呼び出しを記録する。

```vba
Option Explicit
Implements IEffect

Public GetEndRow_Values As Dictionary
Public ReadMatrix_Values As Dictionary
Public WriteMatrix_Results As Dictionary

Private Sub Class_Initialize()
    Set GetEndRow_Values = New Dictionary
    Set ReadMatrix_Values = New Dictionary
    Set WriteMatrix_Results = New Dictionary
    End Sub

Public Function IEffect_GetEndRow(ByVal Sheet As Worksheet, ByVal Col As Long) As Long
    IEffect_GetEndRow = MockUtil.GetValue(GetEndRow_Values, Col)
    End Function

Public Function IEffect_ReadMatrix( _
        ByVal Sheet As Worksheet, _
        ByVal Row1 As Long, _
        ByVal Col1 As Long, _
        ByVal Row2 As Long, _
        ByVal Col2 As Long) As Variant
    IEffect_ReadMatrix = MockUtil.GetValue(ReadMatrix_Values, Row1, Col1, Row2, Col2)
    End Function

Public Sub IEffect_WriteMatrix( _
        ByVal Sheet As Worksheet, _
        ByVal Row1 As Long, _
        ByVal Col1 As Long, _
        ByVal Row2 As Long, _
        ByVal Col2 As Long, _
        ByVal Matrix As Variant)
    Call MockUtil.SetValue(WriteMatrix_Results, Matrix, Row1, Col1, Row2, Col2)
    End Sub
```

#### `AnswerWriter` (クラスモジュール)
本処理の核心。数値の偶奇を判定し、結果の書き出しを指示する。

```vba
Option Explicit

Public Sub EvaluateAndWriteOddEven(ByVal targetSheet As Worksheet, ByVal targetRow As Long, ByVal targetCol As Long, ByVal numberToEvaluate As Long)
    Dim resultString As String
    
    If numberToEvaluate Mod 2 = 0 Then
        resultString = "まる"
    Else
        resultString = "ばつ"
    End If
    
    Dim outputMatrix(1 To 1, 1 To 1) As Variant
    outputMatrix(1, 1) = resultString
    
    Call G.Effect.WriteMatrix(targetSheet, targetRow, targetCol, targetRow, targetCol, outputMatrix)
End Sub
```

#### `G` (標準モジュール)
グローバル変数の格納庫。`IEffect`型変数を保持し、依存性の注入を可能にする。

```vba
Option Explicit

Public Effect As IEffect
```

#### `MockUtil` (標準モジュール)
モックオブジェクトが利用する補助関数群。

```vba
Option Explicit

Public Function GetValue(ByVal Values As Dictionary, ParamArray Args() As Variant) As Variant
    If IsObject(Values(GetKey(Args))) Then
        Set GetValue = Values(GetKey(Args))
    Else
        GetValue = Values(GetKey(Args))
    End If
    End Function

Public Sub SetValue(ByVal Values As Dictionary, ByRef Value As Variant, ParamArray Args() As Variant)
    If IsObject(Value) Then
        Set Values.Item(GetKey(Args)) = Value
    Else
        Values.Item(GetKey(Args)) = Value
    End If
    End Sub

Private Function GetKey(ByVal Args As Variant) As String
    GetKey = Join(Args, "|")
    End Function
```

#### `Test_AnswerWriter` (標準モジュール)
`AnswerWriter`のロジックを検証するテストコード。副作用なしに真価を問う。

```vba
Option Explicit

Public Sub RunOddEvenTests()
    Test_EvaluateOddEven_EvenNumber
    Test_EvaluateOddEven_OddNumber
    Debug.Print "RunOddEvenTests: 全テスト完了！"
End Sub

Private Sub Test_EvaluateOddEven_EvenNumber()
    Dim writer As AnswerWriter: Set writer = New AnswerWriter
    Dim mockEffect As EffectMock: Set mockEffect = New EffectMock
    Set G.Effect = mockEffect
    
    Dim testRow As Long: testRow = 1
    Dim testCol As Long: testCol = 1
    Dim evenNumber As Long: evenNumber = 10
    
    Call writer.EvaluateAndWriteOddEven(Nothing, testRow, testCol, evenNumber)
    
    Dim expectedOutput(1 To 1, 1 To 1) As Variant
    expectedOutput(1, 1) = "まる"
    
    Dim actualRecordedMatrix As Variant
    actualRecordedMatrix = MockUtil.GetValue(mockEffect.WriteMatrix_Results, testRow, testCol, testRow, testCol)
    
    Debug.Assert actualRecordedMatrix(1, 1) = expectedOutput(1, 1)
    Debug.Print "Test_EvaluateOddEven_EvenNumber: パス (数値: " & evenNumber & " -> 結果: " & actualRecordedMatrix(1, 1) & ")"
    
    mockEffect.WriteMatrix_Results.RemoveAll
End Sub

Private Sub Test_EvaluateOddEven_OddNumber()
    Dim writer As AnswerWriter: Set writer = New AnswerWriter
    Dim mockEffect As EffectMock: Set mockEffect = New EffectMock
    Set G.Effect = mockEffect
    
    Dim testRow As Long: testRow = 5
    Dim testCol As Long: testCol = 3
    Dim oddNumber As Long: oddNumber = 7
    
    Call writer.EvaluateAndWriteOddEven(Nothing, testRow, testCol, oddNumber)
    
    Dim expectedOutput(1 To 1, 1 To 1) As Variant
    expectedOutput(1, 1) = "ばつ"
    
    Dim actualRecordedMatrix As Variant
    actualRecordedMatrix = MockUtil.GetValue(mockEffect.WriteMatrix_Results, testRow, testCol, testRow, testCol)
    
    Debug.Assert actualRecordedMatrix(1, 1) = expectedOutput(1, 1)
    Debug.Print "Test_EvaluateOddEven_OddNumber: パス (数値: " & oddNumber & " -> 結果: " & actualRecordedMatrix(1, 1) & ")"
    
    mockEffect.WriteMatrix_Results.RemoveAll
End Sub
```

#### `MainModule` (標準モジュール)
プログラム実行の起点。

```vba
Option Explicit

Public Sub Main_OddEvenCheck()
    Set G.Effect = New Effect
    
    Dim writer As AnswerWriter: Set writer = New AnswerWriter
    
    Call writer.EvaluateAndWriteOddEven(Sheet1, 1, 1, 5)
    Call writer.EvaluateAndWriteOddEven(Sheet1, 2, 1, 8)
    
    Debug.Print "Main_OddEvenCheck: 処理完了！"
    
    ' テスト実行時は以下を呼び出す
    ' Call Test_AnswerWriter.RunOddEvenTests
End Sub
```

### 結論

この構造により、ロジック (`AnswerWriter`) と副作用 (`Effect`) を明確に分離。テスト (`Test_AnswerWriter` と `EffectMock`) はロジックの正しさのみに集中でき、コードの信頼性と保守性を格段に向上させる。臆することなく、この力強い手法を試されたい。

---
本記事は、Qiitaの「【改訂版】VBAだってユニットテストがしたい！」を参考に、VBAで副作用をクラスに隔離し、モックを用いてユニットテストを行う手法を自分なりに再現・応用したものです。