2022/10/05

LListObjectへのデータ追加

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

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

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

テーブルに対して、手動でレコード（＝データ行）を追加するには、大きく２種類の方法があります。１つは図１のように「行の位置を指定して、メニューからデータ行を挿入」する方法です。
テーブル上でデータ行を挿入
図１

まずテーブル上で「データを追加したい行を選択①」し、マウス右クリックで表示されるメニューの中から「挿入」→「テーブルの行」を選んで左クリック②します。すると図１の右側のように新たなデータ行③が挿入されますので、そこにデータを入力していきます。

また図２のように「データを追加したい行を選択④」した後、リボンの「ホーム」タブ→「セル」グループ→「挿入」をクリック⑤し、表示されるメニューから「セルの挿入」または「上に行を挿入」を選択クリックすると、同様にデータ行が挿入されます。
リボンのメニューからデータ行を挿入
図２

なお「シートの行を挿入」を選んでもデータ行が挿入されますが、これは「行全体を挿入（＝EntireRow.Insert）」の操作となりますので、テーブル以外の範囲にも行が挿入されてしまいます。そのため、同じシート上にテーブルを複数置いていたり、テーブル以外のデータがある場合には使用しない方が良いと思います。

もう一つの方法は、テーブルの最終行一つ下の行にデータを入力する方法です。
テーブルには「隣接するセルに値を入力するとテーブル範囲が広がる」機能がありますので、図３のように最終行の一つ下のセル（図３の⑥）に値を入力することで、テーブルは拡大⑦します。
テーブルの下に値を入力
図３

なおデータが無いテーブルの場合は、図４のように「InsertRowRange部」自体が入力場所ですので、そのまま値を入力すればOKです。なお見た目は同じですが、データを入力した途端にInsertRowRange部はDataBodyRange部に変わっています。
データの無いテーブルはInsertRowRangeに入力
図４

２．マクロによるデータ追加処理

ここで説明するマクロは、メインのコードから呼び出す「モジュール」の形とし、第一引数として処理する「テーブル（＝ListObjectオブジェクト）」を、第二引数として「１次元配列の形にした追加するデータ」を受け取るものとします。
図５は呼び出し側のコードの例を示しています。
  1. '==========　⇩(1)　実行プロシージャを呼び出す側　============
  2. Sub Test()
  3. 　Dim arrayData As Variant 　　 '←データの配列
  4. 　arrayData = Array(8, "青い空", , #10/20/2020#, 8, 0, "邦画")
  6. 　Call TableInsert_1(Sheets("sheet1").ListObjects(1), arrayData)
  7. End Sub
図５

０３行目「arrayData = Array(8, "青い空", , #10/20/2020#, 8, 0, "邦画")」でデータの配列を作成し、０５行目「Call TableInsert_1(Sheets("sheet1").ListObjects(1), arrayData)」で、テーブルとデータ配列を引数にして、モジュール（ここでは図６の「TableInsert_1」）を呼び出しています。
データは、０３行目のようにArray関数を使っても良いですし、１要素ずつ代入してもOKです。要素を空欄（＝データを入れない）にする時は「,（カンマ）」を続けて記載します。

また日付を直接指定する場合は、０３行目のように両端を「#（ハッシュマーク）」で囲むか、または「"2020/10/20"」のように「"（ダブルクォーテーション）」で囲っても良さそうです。但し、テーブル側のセル書式設定により使えない値になる可能性があるため注意して下さい。
例えば図５を実行した場合、書き込み先のテーブルのセル書式が「文字列」になっていると、書き込まれた値は「10/20/2020」という文字列となり、計算が出来なくなってしまいます。

２－１．データ行を挿入する方法

まず、図１や図２で示した手動での「テーブルに行を挿入」をマクロで実現させるのが図６です。
  1. '==========　⇩(2)　テーブルに行を挿入 １　============
  2. Sub TableInsert_1(T As ListObject, arrayData As Variant)
  3. 　Const RowNo As Long = 1 　　　 '←挿入する行位置
  5. 　T.ListRows.Add RowNo
  6. 　T.ListRows(RowNo).Range = arrayData
  7. End Sub
図６

１２行目「Const RowNo As Long = 1」で、定数として「挿入行の位置」を設定しています。
１４行目「T.ListRows.Add RowNo」では、挿入行位置を指定して、テーブルに行を挿入しています。Addメソッドには図７の２種類のパラメータがあります。
ListRows.Addメソッド
ﾊﾟﾗﾒｰﾀﾃﾞｰﾀ型内容
PositionVariant
(Integer)		挿入行の相対位置
省略時は最終行の下に追加
AlwaysInsertVarian
(Boolean)		True：1行下にｼﾌﾄ（既定値）
False：下のｾﾙが空白の時は上書きされ､空白で無い時は1行下にｼﾌﾄ
図７

１４行目は第１パラメータのPositionに「１」を指定している形なので、テーブルのデータ領域の１行目（＝データ行の一番上）に行挿入することになります。

１５行目「T.ListRows(RowNo).Range = arrayData」では、挿入した「１」行目に、配列に収められているデータを一気に貼り付けます。但し引数として受け取った配列の要素数が、テーブルの列数と異なる場合はデータ欠落などが発生します。
例えば図５の０３行目が「arrayData = Array(8, "青い空", , #10/20/2020#, 8, 0)」と、配列の要素数がテーブル列数よりも少ない場合は、図８のように「不足している場所には、#N/Aのエラー値」が入ることになります。
貼り付けるデータの要素数が列数より少ない場合
図８

また「arrayData = Array(8, "青い空", , #10/20/2020#, 8, 0, "邦画", "ABC")」と、配列の要素数が多かった場合には、図９のように「多い要素は無視」されます。
貼り付けるデータの要素数が列数より多い場合
図９

なお、図６では挿入する行位置を「１」としましたが、「テーブルの行数＋１ 以内」に設定しないと「インデックスが有効範囲にありません」という実行時エラーが発生します。データが無いテーブルの行数はゼロですので、「１」であればどんなテーブルでも成立することになります。

AddメソッドのPositionパラメータを省略したのが図１０です。省略時は最終行の下に新しい行が挿入されます。
  1. '==========　⇩(3)　テーブルに行を挿入 ２　============
  2. Sub TableInsert_2(T As ListObject, arrayData As Variant)
  3. 　T.ListRows.Add
  4. 　T.ListRows(T.ListRows.Count).Range = arrayData
  5. End Sub
図１０

２２行目「T.ListRows.Add」で、テーブルに対して行を挿入しますが、パラメータを指定していないので図７内の説明の通り「最終行の下」に追加する形になります。
２３行目「T.ListRows(T.ListRows.Count).Range = arrayData」は、新しく追加された行にデータを貼り付けています。２２行目を実行した時点で新たな行ができますので、２３行目時点での「テーブルの最終行位置は T.ListRows.Count」となります。

内容は図１０と同じ（最終行の下に行を挿入）ですが、追加した行のListRowオブジェクトを取得し、そのオブジェクトに対してデータを書き込んでいるのが図１１です。
  1. '==========　⇩(4)　テーブルに行を挿入 ３　============
  2. Sub TableInsert_3(T As ListObject, arrayData As Variant)
  3. 　Dim InsertRow As ListRow 　　 '←追加する行
  5. 　Set InsertRow = T.ListRows.Add
  6. 　InsertRow.Range = arrayData
  7. End Sub
図１１

３４行目「Set InsertRow = T.ListRows.Add」で、最終行の下に行挿入します。Addメソッドは戻り値として「新しく追加した行を表すListRowオブジェクト」を返しますので、それを変数InsertRowに代入します。
３５行目「InsertRow.Range = arrayData」では、「新しく追加された行（変数InsertRow）」にデータを書き込んでいます。

また内容は図１１と同じですが、Addメソッドが戻してくる「新しく追加した行のListRowオブジェクト」を変数で受けるのでは無くWithステートメントを使って受け取り、その流れの中でデータ貼り付け処理をしているのが図１２です。私見ですがコードは最もスッキリしています。
但し分かりにくいと思われる方は図１１のように一度変数として受け取る書き方でもOKと思います。
  1. '==========　⇩(5)　テーブルに行を挿入 ４　============
  2. Sub TableInsert_4(T As ListObject, arrayData As Variant)
  4. 　With T.ListRows.Add
  5. 　　.Range = arrayData
  6. 　End With
  8. End Sub
図１２

４３行目「With T.ListRows.Add」で最終行の下に行を追加し、追加した行に対して４４行目「.Range = arrayData」で値を書き込んでいます。

２－２．テーブル最終行の一つ下にデータ書き込み

上記の４つは「テーブルに行を追加」する手法でした。それとは異なり、手動での図３・図４に相当する「テーブルの最終行の下にデータを書き込む」ことで、テーブルにデータを追加するのが図１３です。
  1. '==========　⇩(6)　テーブルの最終行の下にデータを追加 １　============
  2. Sub TableInsert_5(T As ListObject, arrayData As Variant)
  4. 　If T.InsertRowRange Is Nothing Then
  5. 　　T.ListRows(T.ListRows.Count).Range.Offset(1, 0) = arrayData
  6. 　Else
  7. 　　T.InsertRowRange = arrayData
  8. 　End If
  10. End Sub
図１３

「テーブルの最終行の１つ下」と言っても、それはデータが存在する場合であって、データの無いテーブルではテーブル内の「InsertRowRange」に新データを書き込む必要があります。
そのため、テーブルにデータが有るか無いかで分岐をさせます。

５３行目「If T.InsertRowRange Is Nothing Then」は、データがある（InsertRowRangeが存在しない ＝ DataBodyRangeが存在する）場合を仕訳けています。「If Not T.DataBodyRange Is Nothing Then」でも同じ意味でOKです。
５４行目「T.ListRows(T.ListRows.Count).Range.Offset(1, 0) = arrayData」は、データ領域の最終行「 ListRows(T.ListRows.Count).Range 」の１つ下「 .Offset(1, 0) 」に、引数で受け取ったデータ配列を貼り付けます。
寄り道
ここでOffset(1, 0)を使わずに「T.ListRows(T.ListRows.Count + 1).Range = arrayData」としたくなるかもしれません。
しかし、ListRowsの中には「T.ListRows.Count 番目」までの行数しか無く、「T.ListRows.Count + 1 番目」の行はありませんので、実行時エラーが出ます。

５５行目「Else」のそれ以外（＝データが無いテーブル）の場合は、５６行目「T.InsertRowRange = arrayData」で、「InsertRowRange」の範囲にデータ配列を貼り付けます。
寄り道
データの無いテーブルに対して、図１４のようにテーブルスタイルのオプションで「見出し行」も「集計行」も無いスタイルにしたとします。
InserRowRangeのみのテーブル
図１４

このInsertRowRangeのみの状態で図１３のコードを実行すると、５６行目「T.InsertRowRange = arrayData」でデータ配列を貼り付けた直後に「テーブルが解除」されます。ついでにInsertRowRangeの行の背景色となっていた薄い青色も消えてしまいます。この現象は図１３のみで無く、図６・図１０・図１１・図１２のListRows.Addを使うコードでも発生します。
なおテーブルが解除される条件は、データを「配列」として一気に貼り付ける時であり、For～Next等を使って１セルずつ貼り付けた時には解除されません。また、手動でセル範囲をコピーしてInsertRowRange全体にペーストした時にも解除されません。

テーブルが解除されたり背景色が消えたりする原因は、分かりません。一時は、図１３の場合はInsertRowRangeの全域（＝Range領域）にデータ配列を入れるため、一気にDataBodyRangeに変わる（＝テーブルの全構造が変わる）ためかな？　と考えましたが、それは違うみたいです。
図６等のAddメソッドでInsertRowRange → DataBodyRangeに変わる時にはテーブルを保持しており、DataBodyRange全域（＝Range領域）にデータ配列を一気に貼り付ける時にテーブルが解除されるからです。
しかも、データ追加では無くデータ更新（SQLのUpdate相当）で「全く同じ値を貼り付け」てもテーブルが解除されるので、値が一気に変わるのが原因でも無さそうです。

また無関係かもしれませんが、タイトル行も集計行も無い「DataBodyRangeのみのテーブル」に対して全ての行を削除すると、やはりテーブルが忠告無しに解除されます。挙動と見ていると、テーブルのDataBodyRangeの範囲を「削除」→「上方向にシフト」をしているようです。

理由が分からず残念ですが、テーブルは既定状態「見出し行：有、集計行：無」で扱うのが、安全そうです。

図１３の代わりに、データ領域の行数を数えて「データ領域の一つ下の行」にデータを書き込むのが図１５です。図１３と異なるのは「データが無いテーブル（＝ListRowsは無い）のListRowsの数はゼロ」となる特性を使うことで、データ有無での分岐を無くしていることです。
  1. '==========　⇩(7)　テーブルの最終行の下にデータを追加 ２　============
  2. Sub TableInsert_6(T As ListObject, arrayData As Variant)
  3. 　T.HeaderRowRange.Offset(T.ListRows.Count + 1, 0) = arrayData
  4. End Sub
図１５

７２行目「T.HeaderRowRange.Offset(T.ListRows.Count + 1, 0) = arrayData」では、まず「T.ListRows.Count」でデータ領域の行数を取得しています。データの無いテーブルの場合は「T.ListRows.Count = 0」となります。
このデータ行数を使って、タイトル行（HeaderRowRange）から数えて「Offset(T.ListRows.Count + 1, 0)」番目の行にデータ配列を書き込んでいます。

アプリ実例

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