2022/10/09

ListObjectのデータ変更

ListObjectについて、下記のようなシリーズで説明しています。
　・ListObjectの作成と概要
　・ListObjectの並べ替え
　・ListObjectの絞り込み
　・ListObjectの絞り込み解除
　・ListObjectの絞り込みデータの配列化
　・ListObjectへのデータ追加
　・ListObjectのデータ変更　　←今回
　・ListObjectのデータ削除

今回は、ListObjectオブジェクトに対する「データ変更」について説明します。

１．テーブルに対するデータ変更処理（手動）

ワークシート上のあるデータを変更しようとする場合は、１つ１つのセルをチェックし見つけては置き換える　という方法や、「ホーム」タブ→「編集」グループ→「検索と選択」にあるツールを使って見つけ出し置き換える方法などが考えられます。
しかしテーブルの場合は「１行（レコード）をセットとしたデータベース」として扱っていて、「各列には同じ種類のデータ」が入っていますので、列ごとにまとめて置き換えることが可能です。また先頭行にはフィルターボタンもついていますので、目的のデータは容易に抽出できるようになっています。

データ変更をSQL的に表現すると「Update テーブル名 Set 列名 = 新たな値 Where 絞り込み条件 」となります。まずWhere句でテーブルを絞り込み、その絞り込んだ行（＝レコード）に対して、指定した列の値を置き換えます。

この手順で考えていくと、まず「テーブルを絞り込む」のが図１になります。図１ではTitle列（２列目）が「"ジュマンジ"」のものに絞り込んでいます。
テーブル上でデータ行を挿入
図１

この絞り込んだテーブルの行に対して、CreateD列（４列目）の値を「2022/11/04」という値に書き換えているのが図２です。手動で変更を加えていて、絞り込んだ１行目（１列目のDvdNo=2）の値を書き換え、次に２行目（DvdNo=6）を書き換えています。１行目から２行目に移動するには、絞り込み行以外は「非表示」ですので「Enterキーで次の行へ」移動できます。
テーブル上でデータ行を挿入
図２

抽出行数が多い場合は、いくつも同じ値を入力しなければならず大変です。その時にはどこかのセルに「新たな値（今回の場合は、2022/11/04 という日付）」を記入しておき、まずコピー（図３の一番右側）して、クリップボードに一時保管します。
次に、絞り込んだテーブルの書き換えたい列全体をセル選択し、貼り付け（ペースト）を実行します。すると、絞り込まれた行のみが書き変わります。
テーブル上でデータ行を挿入
図３

２．マクロによるデータ変更処理

テーブルの絞り込みについては「ListObjectの絞り込み」を参考にして下さい。

今回の、テーブルを絞り込み後「行を指定して新たな値で変更」するマクロは、メインのコード（図４の０７行目）から呼び出される実行モジュールの形としています。第一引数には処理する「テーブル（=ListObjectオブジェクト）」、第二引数には「変更をする列名、または列番号」、第三引数には「変更する値」を受け取ります。
  1. '==========　⇩(1)　実行プロシージャを呼び出す側　============
  2. Sub Test()
  3. 　Const Col As Variant = "CreateD" 　　　　 '←変更を加える列
  4. 　Const uniData As Variant = #11/4/2022# 　 '←変更値
  6. '　　テーブル絞り込みのコード
  8. 　Call TableUpdate_1(Sheets("sheet1").ListObjects(1), Col, uniData)
  9. End Sub
図４

０２行目「Const Col As Variant = "CreateD"」は、変更を加える列を指定しています。ここでは「列名（String型）」ですが、列位置（Integer型）を使って「Const Col As Variant = 4 」などと指定してもOKです。
０３行目「Const uniData As Variant = #11/4/2022#」は、変更値です。ここでは日付を指定していますが、列に合ったデータ型で指定します。

テーブルの絞り込みを行った後、０７行目「Call TableUpdate_1(Sheets("sheet1").ListObjects(1), Col, uniData)」で実行プロシージャを呼び出します。引数として「テーブル」「列名（列位置）」「変更値」を渡します。

２－１．絞り込み行だったらデータ変更

テーブルは絞り込まれた状態のはずなので、値を変更する行は可視（＝変更しない行は非表示）になっています。ですので、テーブルの全ての行を確認し「可視行の場合」にデータを書き換える方法が図５です。
  1. '==========　⇩(2)　全行を調べ可視行の場合にデータ書き換え　============
  2. Sub TableUpdate_1(T As ListObject, Col As Variant, uniData As Variant)
  3. 　Dim i As Long 　　 '←テーブルのデータ行数
  5. 　For i = 1 To T.ListRows.Count
  6. 　　If T.DataBodyRange.Rows(i).Hidden = False Then
  7. 　　　T.ListColumns(Col).DataBodyRange(i) = uniData
  8. 　　End If
  9. 　Next i
  11. End Sub
図５

１４行目「For i = 1 To T.ListRows.Count」では、データ行数（T.ListRows.Count）の分だけカウンタ変数ｉを回します。なお、データの無いテーブルの場合は「T.ListRows.Count = 0」ですのでFor～Nextは一度も回らずに終了します。
１５行目「If T.DataBodyRange.Rows(i).Hidden = False Then」では、その行が「可視行か非表示行か」を調べます。可視行だった場合は１６行目「T.ListColumns(Col).DataBodyRange(i) = uniData」で、列位置の場所のセルを第三引数で指定された「変更値（uniData）」で置き換えます。

２－２．絞り込み行のみに対してデータ変更

可視行だけを拾いだして、その行の指定列にデータを書き込む方法が図６です。
  1. '==========　⇩(3)　絞り込み行のみをデータ変更　============
  2. Sub TableUpdate_2(T As ListObject, Col As Variant, uniData As Variant)
  3. 　Dim r As Range 　　 '←テーブル内の可視行×指定列のセル範囲
  5. 　On Error Resume Next
  6. 　　If T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count = 0 Then
  7. 　　　Exit Sub
  8. 　　End If
  9. 　On Error GoTo 0
  11. 　For Each r In T.ListColumns(Col).DataBodyRange.SpecialCells(xlCellTypeVisible)
  12. 　　r = uniData
  13. 　Next r
  15. End Sub
図６

まず３５行目「If T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count = 0 Then」では、データ領域の可視行のセル数を数えています。
データ領域に可視行がある（＝抽出行が存在する）場合は、セル数は１以上の値となるので３６行目「Exit Sub」は実行されずにIf文を抜け、４０行目以降に進みます。

一方、可視行が無い（＝抽出された行が無い）場合には「SpecialCells(xlCellTypeVisible)」の部分でエラーが発生し、よって３５行目のIf文は評価されずに進み、３６行目「Exit Sub」が実行されてプロシージャを抜け出してしまい、「データの変更はしない」ことになります。
また、データの無いテーブルの場合はDataBodyRangeが存在しないため、３５行目の中の「DataBodyRange」の部分でエラーが発生し、可視行が無い場合と同様に３６行目「Exit Sub」が実行されてプロシージャを抜け出します。
なお３５行目でエラーが出ても、止まらずに次の３６行目を実行するように、３４行目「On Error Resume Next」でエラーをスルーさせています。

可視行が存在する場合は、４０行目「For Each r In T.ListColumns(Col).DataBodyRange.SpecialCells(xlCellTypeVisible)」で、指定した列位置（T.ListColumns(Col)）の中で、可視行（.DataBodyRange.SpecialCells(xlCellTypeVisible)）のセル範囲を１つずつ取り出し、変数rにしています。
４１行目「r = uniData」では、４０行目で取得した「可視セル」に、引数で受け取った「変更値（uniData）」を書き込みます。

２－３．テーブルの列全体に対してデータ変更

絞り込みをしたテーブルの「列全体に対して変更値を貼り付ける」方法が図７です。
  1. '==========　⇩(4)　テーブルの列全体に対してデータ変更　============
  2. Sub TableUpdate_3(T As ListObject, Col As Variant, uniData As Variant)
  4. 　On Error Resume Next
  5. 　　If T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count = 0 Then
  6. 　　　Exit Sub
  7. 　　End If
  8. 　On Error GoTo 0
  10. 　T.ListColumns(Col).DataBodyRange = uniData
  12. End Sub
図７

５３～５７行目は、図６と同じで「抽出行が一つもない」場合や「データが無いテーブル」の場合に５５行目「Exit Sub」でプロシージャを抜け出します。
可視行が存在する場合は、５９行目「T.ListColumns(Col).DataBodyRange = uniData」で、指定した列全体（T.ListColumns(Col).DataBodyRange）に対して、引数で受け取った「変更値（uniData）」を書き込みます。
寄り道
この操作は、一見すると「列のすべての値が変わってしまいそう」ですが、ある方法でテーブルの絞り込みを行うと「可視行のみが値変更」されます。その方法は「マクロから絞り込みの操作」をすることです。
例えば「Sheets("sheet1").ListObjects(1).Range.AutoFilter Field:=2, Criteria1:="ジュマンジ"」のようなコードで絞り込みをすればOKです。
逆に、手動で「Title列のフィルターボタンを押下」→「"ジュマンジ" だけを表示するようにチェックを付ける」という手順で絞り込みを行った後に図７のコードを実行すると「列のすべての値が変更値に置換」してしまうので注意が必要です。

なお、手動で絞り込みをしたとしても、その後で図８の〇印のような操作を「マクロ側から」行えば、可視セルのみを置き換える事ができます。
No.結果操作内容
1〇ﾃｰﾌﾞﾙのSheetをActiveにしてから､ﾃｰﾌﾞﾙの一部または全体をｾﾙ選択
(例:ﾃｰﾌﾞﾙ.Range(1).Select ､ ﾃｰﾌﾞﾙ.Range.Select)
2〇非絞込列を絞り込み解除
(例:ﾃｰﾌﾞﾙ.Range.AutoFilter Field:=1)
3〇非絞込列を無意味に絞り込み
(例:ﾃｰﾌﾞﾙ.Range.AutoFilter Field:=1,Criteria1:=">=1")
4×集計行のON-OFF
(例:ﾃｰﾌﾞﾙ.ShowTotals=True/False)
5×先頭列の太文字ON-OFF
(例:ﾃｰﾌﾞﾙ.ShowTableStyleFirstColumn=True/False)
6×非絞込列の並べ替え
(例:ﾃｰﾌﾞﾙ.Sort ･･･)
図８

図８を見て分かるように、テーブルに対してマクロ側から目に見えるような操作をしても、置き換えが成功している訳ではありません。No.4～6のように明らかにテーブルの構造や書式を変更しているにも関わらず失敗します。何が書き換えの成功可否を左右しているのか、今のところ把握できていません。
成功の中でもNo.1は、「テーブルをActive」にしていないとエラーが発生しますし、Sheet間移動を「Application.ScreenUpdating = False」等でユーザーの目には見えない様にすると置換が失敗します。
ですので、ユーザーが操作する可能性のあるシステムでは、No.2～3の「無関係の列に対する、意味のない絞り込み／絞り込み解除」が有効そうです。

なお図８のNo.3については、手動絞り込みの前に実行しておいたとしても、手動絞り込み直後に図７を実行すると正しく「可視セルのみが書き換わる」ようです。また無意味な絞り込みでは無く、必要な絞り込みでも当然同じ結果が得られます。

また、絞り込んだ結果「抽出行が１つも無い状態」で図７の５９行目だけを実行すると、「列の全てのセルが変更値に置き換わる」ことになります。この現象は今のところ避ける手段が見つからないので、５３～５７行目のように「抽出行がゼロの場合は、セル値の置き換え処理をしない」ようにして逃げています。

変更するデータ行が無い場合を排除するコードを除けば、５９行目の１行のみでデータ変更が出来ます。但し「よりみち」で説明した通り、手動で絞り込みをされたり、抽出行がゼロの場合を排除しなかったりすると「全データが書き換わってしまう」という危険があります。「テーブル ≒ データベース」とは言っても、マクロから書き換えるとRollBack（「ホーム」タブ→「元に戻す」グループ→「元に戻す」）は出来ません。
と言って、図５・図６の方法も For～Next や For Eack～Next が途中で止まってしまったら完全性が失われてしまいますので、同様の危険性があると言えます。

テーブルはExcelのみで完結できるデータベースなので、システムが簡易に作れるというメリットがありますが、以上のようなデメリットを良く知っておくことが大切だと思います。

アプリ実例

「DVD等の内容・保管場所等管理システム」
「先行予約可能な備品予約・貸出システム」
「ToDoリストで個人タスク管理」