Excel文書では既存の表に新しい列が追加されるなど、フォーマットが変わるケースが多々あります。
例えば以下の表に新しく「社会」の列を増やしたい、といった場合。
↓
このようなケースが多いため、VBAでコードを書く場合、以下のようにコード内で列番号をベタ書きしないようにしましょう。
新しい列がシートに追加されるたびに片っ端からコード内の列番号を書き換える必要が出てしまい、複雑なコードであればあるほど地獄を見ます。
VBAでマクロを組む場合、このように列の追加などのフォーマット変更に強いコードを書く必要があります。
方法① 列番号を定数で定義する
簡単な対策としては、以下のように定数で列番号を定義しておくケース。
列番号が変わったら定数の値を書き換え、「社会」の列が追加されたらその列の定数を追加で定義します。
GradeListSheet
' モジュール名
Const MODULE_NAME = "GradeListSheet"
' 「名前」列のテーブル内列番号
Const NAME_CLM = 1
' 「国語」列のテーブル内列番号
Const NL_CLM = 2
' 「数学」列のテーブル内列番号
Const MATH_CLM = 3
' 「英語」列のテーブル内列番号
Const SUM_CLM = 4
' 「合計」列のテーブル内列番号
Const SUM_CLM = 5
'============================================================
'
' テーブルからデータ読込
'
'============================================================
' テーブルからデータ読込
' ・テーブルから各ユーザの合計点を読み込んで出力
'
Public Sub readDataFromLO()
On Error GoTo ErrHdl
Dim i, uName, sumScore, lo As ListObject
' テーブルをセット
Set lo = GradeListSheet.ListObjects(1)
' 表内を見出し行の下から走査
For i = 1 To lo.DataBodyRange.Rows.count
' 氏名、合計を読込
uName = lo.DataBodyRange(i, NAME_CLM)
sumScore = lo.DataBodyRange(i, SUM_CLM)
' 出力
Debug.Print uName, sumScore
Next
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".readDataFromLO", Err.Description
End If
End Sub
列追加時の変更点が定数だけになるため、保守がしやすくなります。
また、関数内の数字を定数に置き換えたことで処理内容が分かりやすくなりますね。
方法② 列番号を動的に取得する
もう一つは列番号をキーワード検索で変数に動的にセットするケース。
列が追加された時にも既存の処理の部分ではコードの変更が不要です。
以下のように列番号を格納する変数を用意しておき、キーワード検索で列番号をセットします。
GradeListSheet
' テーブル名
Const TARGET_TABLE_NAME = "成績表"
' 「名前」列の見出しのキーワード
Const NAME_CLM_KEYWORD = "名前"
' 「合計」列の見出しのキーワード
Const SUM_CLM_KEYWORD = "合計"
' テーブル内列番号 氏名
Private NameClm As Long
' テーブル内列番号 合計
Private SumClm As Long
' テーブル
Private TLo As ListObject
'============================================================
'
' テーブルからデータ読込
'
'============================================================
' テーブルからデータ読込
' ・テーブルから各ユーザの合計点を読み込んで出力
'
Public Sub readDataFromLO()
On Error GoTo ErrHdl
Dim i, uName, sumScore
' テーブルをセット
Set TLo = GradeListSheet.ListObjects(TARGET_TABLE_NAME)
' 列番号を変数にセット
Call setClmNums
' 行を走査
For i = 1 To TLo.DataBodyRange.Rows.count
' 氏名、合計を読込
uName = TLo.DataBodyRange(i, NameClm)
sumScore = TLo.DataBodyRange(i, SumClm)
' 出力
Debug.Print uName, sumScore
Next
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".readDataFromLO", Err.Description
End If
End Sub
'============================================================
' 列番号を変数にセット
'============================================================
' 列番号を変数にセット
' ・テーブルの見出しをキーワード検索し、一致する列の番号を変数にセット
'
Private Sub setClmNums()
On Error GoTo ErrHdl
' 名前の列番号をセット
NameClm = Util.getLoClmNumByKeyword(TLo, NAME_CLM_KEYWORD, True)
' 合計の列番号をセット
SumClm = Util.getLoClmNumByKeyword(TLo, SUM_CLM_KEYWORD, True)
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".setClmNums", Err.Description
End If
End Sub
テーブルの列番号を検索で取得する関数は別モジュールに定義して複数プロジェクトで使いまわします。
Util.bas
' テーブルの見出しを走査し、特定のキーワードを含む列のテーブル内列番号を返す
'
' 引数： lo ListObject テーブル
' keyword String 検索キーワード
' isWhole Boolean Trueなら完全一致、Falseなら部分一致
' 戻値： Long
'
Public Function getLoClmNumByKeyword(ByRef lo As ListObject, ByVal keyword As String, Optional ByVal isWhole As Boolean = True) As Long
On Error GoTo ErrHdl
Dim clm, cellText As String
' 対象の行を1列ずつ走査
For clm = 1 To lo.Range.Columns.count
' 対象セルの値をセット
cellText = lo.Range(1, clm)
' キーワードと完全一致か判定
If isWhole Then
If cellText = keyword Then
getLoClmNumByKeyword = clm
Exit Function
End If
' 部分一致かを判定
Else
If InStr(cellText, keyword) Then
getLoClmNumByKeyword = clm
Exit Function
End If
End If
Next
' 対象列がなければエラー出力
Debug.Print MODULE_NAME & ".getLoClmNumByKeyword", "[LO内列番号セット失敗]" & vbTab & keyword & "を含む列が" & lo.name & "にありません。"
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".getLoClmNumByKeyword", Err.Description
End If
End Function
注意点として、表の見出しの文言（上記の例では"名前"、"合計"）が変化してしまった場合はエラーを吐くようなコードを書いておきましょう。
上の例では検索キーワードに一致する列がない場合にイミディエイトウィンドウに出力しています。
ロギングするとなお良いかと思います。
テーブルを使わないケース
なお、上記②では表にテーブルを使っていますが、テーブルを使わない場合の動的な列番号の取得方法も書いておきます。
テーブルを使わない場合、表の見出しの行番号をまず取得する必要があります。
キーワード検索で表の見出しのセルを取得し、該当行を見出しとして扱います。
GradeListSheet
' モジュール名
Const MODULE_NAME = "GradeListSheet"
' 「名前」列の見出しのキーワード
Const NAME_CLM_KEYWORD = "名前"
' 「合計」列の見出しのキーワード
Const SUM_CLM_KEYWORD = "合計"
' 読み込み対象シート
Private ISheet As Worksheet
' シート内列番号 氏名
Private NameClm As Long
' シート内列番号 合計
Private SumClm As Long
' 見出し行
Private HRow As Long
'============================================================
'
' シートからデータ読込
'
'============================================================
' シートからデータ読込
' ・シート上の表から各ユーザの合計点を読み込んで出力
'
Public Sub readDataFromSheet()
On Error GoTo ErrHdl
Dim i, uName, sumScore
' 読み込み対象シートをセット
Set ISheet = GradeListSheet
' 列番号を変数にセット
Call setClmNums
' 表内を走査
For i = HRow + 1 To ISheet.Rows.count
' 氏名、合計を読込
uName = ISheet.cells(i, NameClm)
sumScore = ISheet.cells(i, SumClm)
'氏名が空なら終了
If uName = "" Then
Exit For
End If
' 出力
Debug.Print uName, sumScore
Next
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".readDataFromSheet", Err.Description
End If
End Sub
'============================================================
' 列番号を変数にセット
'============================================================
' 列番号を変数にセット
' ・キーワード検索で見出し行の行番号をセットし、
' 見出し行から名前、合計の列番号をセット
'
Private Sub setClmNums()
On Error GoTo ErrHdl
Dim cl As Range, HRow
' 「名前」のセルを検索
Set cl = ISheet.cells.Find(What:=NAME_CLM_KEYWORD, lookat:=xlWhole)
' 見出しの行番号をセット
HRow = cl.row
' 名前列の列番号をセット
NameClm = cl.Column
' 合計の列番号を見出し行から検索してセット
SumClm = Util.getClmNumByKeyword(ISheet, HRow, SUM_CLM_KEYWORD, True)
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".setClmNums", Err.Description
End If
End Sub
同じく、シート内の列番号を検索で取得する関数は別モジュールに定義しておきます。
Util.bas
' 対象の行を走査し、特定のキーワードを含む列の番号を返す
'
' 引数： sh WorkSheet 対象ワークシート
' row Long 対象の行
' keyword String 検索キーワード
' isWhole Boolean Trueなら完全一致、Falseなら部分一致
' 戻値： Long
'
Public Function getClmNumByKeyword(ByRef sh As Worksheet, ByVal row As Long, ByVal keyword As String, Optional ByVal isWhole As Boolean = True) As Long
On Error GoTo ErrHdl
Dim clm, cellText As String
' 対象の行を1列ずつ走査
For clm = 1 To sh.Columns.count
' 対象セルの値をセット
cellText = sh.cells(row, clm).Value
' キーワードと完全一致か判定
If isWhole Then
If cellText = keyword Then
getClmNumByKeyword = clm
Exit Function
End If
' 部分一致かを判定
Else
If InStr(cellText, keyword) Then
getClmNumByKeyword = clm
Exit Function
End If
End If
Next
' 対象列がなければエラー出力
Debug.Print MODULE_NAME & ".getClmNumByKeyword", "[列番号セット失敗]" & vbTab & keyword & "を含む列が" & sh.name & "にありません。"
ErrHdl:
If Err.Number <> 0 Then
Debug.Print MODULE_NAME & ".getClmNumByKeyword", Err.Description
End If
End Function
見出しの行番号を取得する必要がある分、テーブルよりも手間になりますね。
同じような要領で、行番号の変化に強いコードを書くこともできます。
できる限りExcel文書のフォーマット変更後もそのまま動くようなコードを書けるように心がけましょう。