仕様変更に応えられるVBAマクロの記述方法 | 株式会社 埼玉電算研究所

仕様変更に応えられるVBAマクロの記述方法

業務システムにエクセルのVBAマクロを利用していると、実装後に仕様変更を迫られる事があります。その都度、マクロを書き換えれば済むのですが、できればコードを編集せずに終わらせたいものです。ここでは、仕様変更に耐えられるよう、編集量を極力抑える方法紹介します。

RANGEを定数定義で指定で編集頻度を下げる

セルA1からA3にデータが入っており、
1)B1=A1からA3の総和
2)B2=B1/ A1からA3の数
と、B2にA1からA3の平均値が入る機能を作ります。
excel 素材
これを定数定義を使って記述します。

Private Const DATA_RANGE = "A1:A3"
Private Const TOTAL_RANGE = "B1"
Private Const AVE_RANGE = "B2"

Sub Macro()
    Dim total As Long
    total = 0

    Dim data As Range
    Set data = Range(DATA_RANGE)
    
    Dim cell As Range
    For Each cell In data
        total = total + CLng(cell.Value)
    Next

    Range(TOTAL_RANGE).Value = total
    Range(AVE_RANGE).Value = CLng(Range(TOTAL_RANGE).Value) / data.Count
End Sub
もし仕様変更要求に伴いB1(総和)の位置が変わるような事があれば、TOTAL_RANGEに移動先のアドレスを指定します。するとSum Macro()内に直接記述する場合と比べて、編集量が少なくなります。 その上、B1の位置が変更されたら、”Private Const”で宣言をしている定数を変えれば済み、変更漏れが回避できるのです。
このような定数定義を利用した仕様変更への対応は、他の言語でもできます。しかし、これに留まらずエクセルのVBAは、他の言語では真似のできない方法で仕様変更を吸収できます。

RANGEを名前の管理で指定

数式タブの「名前の管理」でRANGEに名前が付けられ、この名前をマクロで指定する事でマクロ編集を回避できます。
B2,A1:A3,B1に下記のように名前を付けます。 excel 素材
先のマクロを名前指定で書き直します。

Private Const DATA_RANGE = "DataRange"
Private Const TOTAL_RANGE = "TotalRange"
Private Const AVE_RANGE = "AveRange"

Sub Macro()
  略 
    
Dim data As Range
    Set data = Range(DATA_RANGE)
  
   略 

    Range(TOTAL_RANGE).Value = total
    Range(AVE_RANGE).Value = CLng(Range(TOTAL_RANGE).Value) / data.Count
    
End Sub
Range()関数の引数に、「名前の管理」で指定した「名前」を設定するだけですので、定数定義を置き換えるだけです。 セルにつけられた名前は、行・列の挿入が行われても付いてきます。例えば、B1(TotalRange)に名前が付いた状態でA列とB列の間に行を入れると、名前の付いたセルはB1からC1に移動します。移動に伴いC1がTotalRangeになります。従って、列が追加されてもマクロを編集する必要はありません。
下のように大胆に表を変更しても、先のマクロを編集せずに利用できます。 excel 素材

テーブルの列名で範囲指定

「名前の管理」の名前をマクロで利用するのは便利ですが、欠点があります。セルを削除された場合、名前が残っていても、「#REF!」と範囲を指定できていない状態になります。そこで、テーブルを利用するのです。テーブルを利用すると、範囲指定がカラム名で指定できるからです。
例えば、先の図のように、体重、身長、BMIのカラム名を持つテーブル(テーブル名はテーブル1)を作成した場合、「身長」のデータの範囲指定は”テーブル1[身長]”で指定できます。

Private Const DATA_RANGE = "テーブル1[身長]"
Private Const TOTAL_RANGE = "TotalRange"
Private Const AVE_RANGE = "AveRange"

Sub Macro()
  略 
    
Dim data As Range
    Set data = Range(DATA_RANGE)
  
   略 

    Range(TOTAL_RANGE).Value = total
    Range(AVE_RANGE).Value = CLng(Range(TOTAL_RANGE).Value) / data.Count
    
End Sub
「身長」を「体重」の列を入れ替えても、マクロは動作します。
しかし、カラム名「身長」が変更されてしまうとマクロは動作しません。これはテーブルの列に名前を付ける事で回避できます。
数式タブの「名前の管理」で、”テーブル1[身長]”に名前を付けます。 excel 素材
マクロでShinchoを利用すれば、カラム名「身長」が「背の高さ」に変更されても、マクロを編集する必要はありません。

Private Const DATA_RANGE = "Shincho"
	略