Excel&VBA解説サイト「エクセルの神髄」様出題の問題集、
「VBA100本ノック」に対する私の回答と解説のページです。
100本ノックの出題リストはこちらから
excel-ubara.com
出題：テーブルの扱いと年齢計算
#VBA100本ノック 53本目
画像のようにA1から始まるテーブルがあります。
「東京都」の「男」で「35歳以上」の人のみ、備考欄に「対象」と入れてください。
年齢はマクロ実行年（つまり今年）の12月31日で計算。
※シートは任意
◇ 出題ページはこちら
ソースコード
定義モジュール
Option Explicit ' データ Public Const R1stデータ = 2 Public Const C1stデータ = 1 Public Enum CNoデータ 名前 = C1stデータ 性別 誕生日 都道府県 備考 End Enum Public Const CLastデータ = CNoデータ.備考
メインモジュール
Option Explicit ' 100本ノック053：テーブルの扱いと年齢計算 Sub 対象者を抽出して備考欄に出力する() With WSデータ Dim R As Long For R = .ListObjects(1).DataBodyRange.Row To Get最終行(.ListObjects(1)) If .Cells(R, CNoデータ.性別) = "男" And _ .Cells(R, CNoデータ.都道府県) = "東京都" And _ Get暦年年齢(.Cells(R, CNoデータ.誕生日), Year(Date)) >= 35 Then .Cells(R, CNoデータ.備考) = "対象" Else .Cells(R, CNoデータ.備考) = "" End If Next End With End Sub
汎用関数モジュール
' 年齢の計算 ' 参考：https://www.limecode.jp/entry/utility/datedif-calculate-age Function Get年齢(ByVal 生年月日 As Date, ByVal 基準日 As Date) As Long Dim mmdd生年月日 As Long: mmdd生年月日 = Month(生年月日) * 100 + Day(生年月日) Dim mmdd基準日 As Long: mmdd基準日 = Month(基準日) * 100 + Day(基準日) If mmdd基準日 >= mmdd生年月日 Then Get年齢 = Year(基準日) - Year(生年月日) Else Get年齢 = Year(基準日) - Year(生年月日) - 1 End If End Function Function Get年度年齢(ByVal 生年月日 As Date, ByVal 年度 As Long) As Long Get年度年齢 = Get年齢(生年月日, DateSerial(年度 + 1, 3, 31)) End Function Function Get学年年齢(ByVal 生年月日 As Date, ByVal 年度 As Long) As Long Get学年年齢 = Get年齢(生年月日, DateSerial(年度 + 1, 4, 1)) End Function Function Get暦年年齢(ByVal 生年月日 As Date, ByVal 年 As Long) As Long Get暦年年齢 = Get年齢(生年月日, DateSerial(年, 12, 31)) End Function ' 最終行の取得 ' 参考：https://www.limecode.jp/entry/library/get-lastrow-lastcolumn Function Get最終行(指定オブジェクト As Variant, Optional ByVal C As Long = -1) As Long ' 渡されたオブジェクトからセル範囲を取得 Dim 対象セル範囲 As Range Select Case TypeName(指定オブジェクト) Case "Range" If 指定オブジェクト.Cells.CountLarge = 1 Then ' 単独セルにはCurrentRegionを取る Set 対象セル範囲 = 指定オブジェクト.CurrentRegion Else Set 対象セル範囲 = 指定オブジェクト End If Case "Worksheet" Set 対象セル範囲 = 指定オブジェクト.UsedRange Case "AutoFilter", "ListObject" Set 対象セル範囲 = 指定オブジェクト.Range Case Else Err.Raise 1000, , "対象外のオブジェクト「" & TypeName(指定オブジェクト) & "」が指定されました。" End Select ' エリアの最終行を取得 Get最終行 = 対象セル範囲.Rows.Count + 対象セル範囲.Row - 1 ' 列が指定されていればその列の入力最終行を取得 If C <> -1 Then Do While 対象セル範囲.Worksheet.Cells(Get最終行, C) = "" Get最終行 = Get最終行 - 1 If Get最終行 < 対象セル範囲.Row Then Get最終行 = 0 Exit Function End If Loop End If End Function
解説
テーブルの扱いと年齢の計算を問う問題でした。
まずは「テーブルであることは意識せず純粋な行列指定」で解答しています。
年齢の計算はシート上で行えるのであればDATEDIF関数が使えますが、
VBA上で行う場合は何らかの方法を考えなければいけません。
※ VBAのDateDiff関数は年齢の計算には使えない。
詳細はこちらの記事をご覧ください。
本サイトは「汎用関数づくりと使い方」をテーマにノックを解答していますので、
年齢計算も汎用関数にして対応しています。
使用していませんが「Get年度年齢」「Get学年年齢」の関数も置いておきました。
実務では「3/31時点の年齢」の方が良く出てきてしかも面倒ですので、
しっかり汎用関数にして持っておくとよいでしょう。
ただ、実務の話をするのであれば、
そもそもテーブル内に「年齢」列を絶対に作るべきです。
（もちろんこれは問題用のテーブルなので出題がおかしいという意味ではない）
列を増やすだけでマクロが簡単になるのであれば、
そちらに手を入れるスタンスはしっかり持っておいてください。
テーブル内のセルをループする方法
テーブル内のセルを指定する方法はたくさんあり、
それぞれに一長一短の特徴があります。
並べてみますので参考にしてみてください。
DataBodyRange.Rowを活用
Sub 対象者を抽出して備考欄に出力する_DataBodyRange利用() Dim テーブル各行 As Range For Each テーブル各行 In WSデータ.ListObjects(1).DataBodyRange.Rows If テーブル各行.Cells(CNoデータ.性別) = "男" And _ テーブル各行.Cells(CNoデータ.都道府県) = "東京都" And _ Get暦年年齢(テーブル各行.Cells(CNoデータ.誕生日), Year(Date)) >= 35 Then テーブル各行.Cells(CNoデータ.備考) = "対象" Else テーブル各行.Cells(CNoデータ.備考) = "" End If Next End Sub
DataBodyRange.RowsをFor Eachでループすることで、
「行番号」を意識しなくてよくなったコードです。
テーブルが上下に移動してもコードを書き替えなくてよいですが、
テーブルがA列始まりでない場合はEnumの調整が必要な点にご注意ください。
最終行の取得をしなくてもよくなることもメリットなのですが、
「Get最終行」の関数を用意すればそんなに差はありません。
ListRowオブジェクトを活用
Sub 対象者を抽出して備考欄に出力する_ListRow利用() Dim テーブル各行 As ListRow For Each テーブル各行 In WSデータ.ListObjects(1).ListRows If テーブル各行.Range(CNoデータ.性別) = "男" And _ テーブル各行.Range(CNoデータ.都道府県) = "東京都" And _ Get暦年年齢(テーブル各行.Range(CNoデータ.誕生日), Year(Date)) >= 35 Then テーブル各行.Range(CNoデータ.備考) = "対象" Else テーブル各行.Range(CNoデータ.備考) = "" End If Next End Sub
ListRowsをFor Eachでループすることで、
DataBodyRange.Rowsと同様「行番号」を意識しなくてよくなったコードです。
最初からRangeオブジェクトを取得できるのがDataBodyRange.Rowsの良さで、
ListRowオブジェクトはそのあと.Rangeを挟む必要があります。
ListRow専用メソッドで使えるものはDeleteくらいしかありませんので、
DataBodyRange.Rowsとほとんど同じコードだと思ってください。
ListColumns.Index(列番号取得)を活用
Sub 対象者を抽出して備考欄に出力する_ListColumnsIndex利用() With WSデータ.ListObjects(1) Dim テーブル各行 As ListRow For Each テーブル各行 In .ListRows If テーブル各行.Range(.ListColumns("性別").Index) = "男" And _ テーブル各行.Range(.ListColumns("都道府県").Index) = "東京都" And _ Get暦年年齢(テーブル各行.Range(.ListColumns("誕生日").Index), Year(Date)) >= 35 Then テーブル各行.Range(.ListColumns("備考").Index) = "対象" Else テーブル各行.Range(.ListColumns("備考").Index) = "" End If Next End With End Sub
列番号を「.ListColumns("列名").Index」で取得するパターンです。
ここまで組むとテーブルが上下左右に移動してもコード書き換え不要になりますが、
反面、列名変更で動かなくなるのがデメリットですね。
テーブルの性質を余すことなく利用したコードと言えるでしょう。
このコードは、
If WSデータ.Cells(R, 2) = "男" Then
という列番号ベタ打ちコードに比べると画期的に感じますが、
If WSデータ.Cells(R, CNoデータ.性別) = "男" Then
というEnum活用コードを使っている方にとっては、一長一短という気がします。
Enumは定義する手間がありますが、
そのあとは選択肢から入力ができるという強力なメリットがありますからね。
テーブルが動いてもコード改修不要というのは確かにメリットですが、
Enumも定義部分の調整だけで済むため、大きく差がある部分ではありません。
状況に応じて使い分けてみてください。
ListColumnオブジェクトを活用
Sub 対象者を抽出して備考欄に出力する_ListColumnオブジェクト利用() With WSデータ.ListObjects(1) Dim i As Long For i = 1 To .DataBodyRange.Rows.Count If .ListColumns("性別").DataBodyRange(i) = "男" And _ .ListColumns("都道府県").DataBodyRange(i) = "東京都" And _ Get暦年年齢(.ListColumns("誕生日").DataBodyRange(i), Year(Date)) >= 35 Then .ListColumns("備考").DataBodyRange(i) = "対象" Else .ListColumns("備考").DataBodyRange(i) = "" End If Next End With End Sub
最後にListColumnをそのままオブジェクトとして活用したパターンです。
今まではテーブルを横(行ごと)にスライスして扱うイメージのコードでしたが、
こちらはテーブルを縦(列ごと)に割ったRangeを使うイメージですね。
何か明確なメリットがあるわけではなく、
処理のイメージがしづらいのでわざわざ使う必要はないかもしれません。
この書き方だと明確に書きやすいような処理があれば使ってみてください。
