LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@KENKEN_WORCKS

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

Posted at

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

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

構造 ― モジュール一覧

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

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

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

IEffect (クラスモジュール)

副作用操作の契約だ。今回はWriteMatrixを利用する。

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の具現化。実際のセル書き込みを担う。

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 (クラスモジュール)

テスト時の影武者。副作用を起こさず、呼び出しを記録する。

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 (クラスモジュール)

本処理の核心。数値の偶奇を判定し、結果の書き出しを指示する。

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型変数を保持し、依存性の注入を可能にする。

Option Explicit

Public Effect As IEffect

MockUtil (標準モジュール)

モックオブジェクトが利用する補助関数群。

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のロジックを検証するテストコード。副作用なしに真価を問う。

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 (標準モジュール)

プログラム実行の起点。

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) はロジックの正しさのみに集中でき、コードの信頼性と保守性を格段に向上させる。臆することなく、この力強い手法を試されたい。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?