エクセルVBAでテーブルのデータ行を追加して値を入力をする方法

photo credit: jeffdjevdet Shopping List via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

請求書作成マクロを題材としてエクセルVBAでテーブルを操作する方法についてお伝えするシリーズです。

前回の記事はこちら。

エクセルVBAでテーブルのデータ行についてループ処理を行う方法

請求書マクロを題材にエクセルVBAでテーブル操作する方法をお伝えしています。今回は、ListRowsオブジェクトがポイント。テーブルのデータ行についてループ処理を行う方法についてお伝えしていきます。

テーブルのデータ行についてループ処理をする方法をお伝えしました。

今回は、エクセルVBAでテーブルのデータ行を追加して値を入力をする方法をお伝えしていきますね。

では、行ってみましょう！

今回のお題
エクセルVBAでテーブルに行を追加してデータを入力する
まとめ
連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

今回のお題

さて、今回やりたいことですが、こんなことをやりたいです。

まず、こちらのシートをご覧ください。

請求書のひな形を表現した「ひな形」シートです。

「ひな形データ」というテーブル名にしたテーブルがありますが、見出し行と集計行はありますが、データ行は一行もありません。

今回はこのデータ行に、行を追加して、さらにデータを入力していきたいと思います。

エクセルVBAでテーブルに行を追加してデータを入力する

では、まずはコードから紹介します。

Sub テーブルの行の追加()

Dim tbTemplate As ListObject
Set tbTemplate = wsTemplate.ListObjects("ひな形データ")

With tbTemplate.ListRows.Add
    .Range(1).Value = "テレビ"
    .Range(2).Value = 100000
    .Range(3).Value = 1
End With

End Sub

なお、テーブルの4列目「価格」フィールドには「=[単価]*[数量]」を入力しておくことにしますね。

こちらのプロシージャを3回実行してみましょうか。

こうなります。

テーブルに実行回数分行が追加され、値も入力することができましたね。

ポイントを以下解説をしていきます。

Addメソッドでテーブルのデータ行を追加する

まずは、6行目ですね。

Withステートメントの対象として「tbTemplate.ListRows.Add」を指定していますね。

これは、「ひな形データ」テーブルの、行コレクションに、新たな行を追加せよ、という命令です。

もう少しちゃんと説明すると、ListRowsコレクションのAddメソッドを使うことで、対象のテーブルに行を追加するということですね。

ListRowsコレクション.Add

Addメソッドの戻り値は追加したListRowオブジェクトになりますので、Withブロックの中は追加したListRowオブジェクトに対する処理となります。

追加したデータ行のセル範囲を取得する

7~9行目を解説しておきましょう。

ここは、追加したListRowオブジェクトについて、Rangeプロパティを使っているということになりますね。

つまり、追加したデータ行のセル範囲の取得になります。

ListRowオブジェクト.Range

試しにイミディエイトウィンドウに以下のように入力して Enter をしてみてください。

? wsTemplate.ListObjects("ひな形データ").ListRows.Add.Range.Address

「ひな形データ」テーブルに1行追加された上で、そのセル範囲が出力されるはずです。

一行のセル範囲からインデックスでセルを指定する

さらに、そのRangeオブジェクトに対して括弧で数値を指定していますよね。

これはインデックスです。

つまり、以下の書式になります。

ListRowオブジェクト.Range(インデックス)

Rangeオブジェクトのインデックスは、セル単位で左から右に、続いて上から下という順番に振られます。

今回の場合、データ行ですから一行分、つまりテーブルの列数の分まで1から順にインデックスが振られているということになりますね。

さらに、品目がA列つまり列番号は1ですから、この振られているインデックスと列番号は等しいということになります。

まとめ

以上、エクセルVBAでテーブルに行を追加する方法と、その行にデータを入力する方法をお伝えしました。

テーブルを使わない場合は、以下の記事のように色々と工夫する必要があったのですが、それも必要ないっすね。

【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す

今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

テーブル様様です。

次回はいよいよテーブルを使った請求書マクロを作成していきます。

エクセルVBAでテーブルを走査して取引先ごとのブックを作るマクロ

請求書の作成マクロを題材に、エクセルVBAでテーブルを操作する方法をお伝えしています。今回は、テーブルを走査して取引先ごとにブックを作成して、請求書のひな形シートをコピーするマクロを紹介します。

どうぞお楽しみに！

連載目次：エクセルVBAでテーブルを活用した請求書マクロを作る

エクセルのとっても便利な機能「テーブル」。VBAで操作するときも、テーブルならではの便利さを味わうことができます。請求書マクロを題材にVBAによるテーブルの操作方法をお伝えしていきます。