2022/10/05

LListObjectへのデータ追加

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

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

1.テーブルに対するデータ追加処理(手動)

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


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

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


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

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


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


2.マクロによるデータ追加処理

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


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

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

2-1.データ行を挿入する方法

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


12行目「Const RowNo As Long = 1」で、定数として「挿入行の位置」を設定しています。
14行目「T.ListRows.Add RowNo」では、挿入行位置を指定して、テーブルに行を挿入しています。Addメソッドには図7の2種類のパラメータがあります。
ListRows.Addメソッド
パラメータデータ型内容
PositionVariant
(Integer)
挿入行の相対位置
省略時は最終行の下に追加
AlwaysInsertVarian
(Boolean)
True:1行下にシフト(既定値)
False:下のセルが空白の時は上書きされ、空白で無い時は1行下にシフト
図7


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

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


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


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

AddメソッドのPositionパラメータを省略したのが図10です。省略時は最終行の下に新しい行が挿入されます。
  1. '========== ⇩(3) テーブルに行を挿入 2 ============
  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
図10


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

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


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

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


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

2-2.テーブル最終行の一つ下にデータ書き込み

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


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

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

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


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

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

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

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

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


72行目「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リストで個人タスク管理