Excel VBA_変更に強い表を作る(ソフトコーディングの手法)

この記事ではExcel VBAで「変更に強い表」の実現方法を考えてみます。「始点から終点まで走査する」という処理は、VBAあるあるではないかと思います。例えば下記のように月次の売上金額を自動集計する場合です。

しかし表のレイアウト変更は意外と多く起こります。 集計項目が増えたり、始点セルの場所が変わったり..その度にコードを修正していては大変ですよね。そこで後からの変更にもカンタンに対応できるよう、3つのマクロを比較しながら考えてみます。

• 数値のベタ書き
• 終端セルの取得
• 始点/終端セルをワークシートで設定

数値のベタ書き

まずは最も汎用性が低い「数値のベタ書き」です。

Sub 合計1()
    Dim i As Long
    Dim 合計金額 As Long
    Dim 出力セル As Range
    Set 出力セル = Range("C8")
    
    For i = 3 To 7
        合計金額 = 合計金額 + Range("C" & i).Value
    Next
    出力セル = 合計金額
End Sub

この方法だと後から商品項目を追加した場合、プログラムを書き直す必要が出てきます。

終端セルの取得

後からの商品追加に対応したのが「終端セルの自動取得」です。

Sub 合計2()
    Dim 始点セル As Range
    Dim 終端セル As Range
    Dim 出力セル As Range
    
    ' セルの情報を設定
    Set 始点セル = Range("C3")
    Set 終端セル = Cells(Rows.Count, 始点セル.Column).End(xlUp)
    Set 出力セル = 終端セル.Offset(1, 0)
    
    Dim i As Long
    Dim 合計金額 As Long
    For i = 始点セル.Row To 終端セル.Row
        合計金額 = 合計金額 + Range("C" & i).Value
    Next
    出力セル = 合計金額

End Sub

この方法でも後からの変更へ柔軟に対応できます。しかしレイアウトが変更された場合（始点セルを移動した時？）、エディタを開いて内部のプログラムを修正しなければなりません。

始点/終端セルをワークシートで設定

アイディアとして、始点/終端セルをワークシートで設定してはどうでしょうか。何か変更があった場合にはコードを修正するのではなく、シートに記載した設定を修正するようにします（それでも対応が難しい時にだけコーディングを行います。）

Sub 合計3()
    ' 「設定」シートから情報を取得
    ' String型で書き込み先のアドレス情報を格納
    Dim 始点情報 As String
    Dim 終点情報 As String
    Dim 出力情報 As String
    With Worksheets("設定")
        始点情報 = .Range("C3").Value
        終点情報 = .Range("C4").Value
        出力情報 = .Range("C5").Value
    End With
    
    ' String型からRange型へセルを設定
    Dim 始点セル As Range
    Dim 終点セル As Range
    Dim 出力セル As Range
    With Worksheets("集計")
        Set 始点セル = .Range(始点情報)
        Set 終点セル = .Range(終点情報)
        Set 出力セル = .Range(出力情報)
    End With

    ' 合計金額を計算
    Dim i As Long
    Dim 合計金額 As Long
    For i = 始点セル.Row To 終点セル.Row
        合計金額 = 合計金額 + Cells(始点セル.Column, i).Value
    Next
    出力セル.Value = 合計金額

End Sub

これによってプログラムの汎用性が上がって、マクロを書けない人でも修正が可能となります。プログラム内で使う定数や変数を「外」に出すと柔軟性が向上する例もあったりするので、覚えておくと良いかもしれません。