VBA練習問題
VBA100本ノック 45本目:テーブルに列追加

VBAを100本の練習問題で鍛えます
最終更新日:2021-02-22

VBA100本ノック 45本目:テーブルに列追加


テーブルに列を追加した横合計の計算式を入れる問題です。


ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。
マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。


出題

出題ツイートへのリンク

#VBA100本ノック 45本目
シートにB2から始まる5列(列1,列2,列3,列4,列5)のテーブルあります。
・列3の後ろに列挿入して列1から列3の合計列を作成、見出しは"合計列1"
・テーブルの右端に列4から列5の合計列を作成、見出しは"合計列2
出来上がりは画像を参照してください。
※シートは任意

マクロ VBA 100本ノック

マクロ VBA 100本ノック


サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_45.xlsm
https://excel-ubara.com/vba100sample/VBA100_45.zip


VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。


他の人の回答および解説を見て、書いたVBAを見直してみましょう。


頂いた回答

解説

テーブルの要素にアクセスするオブジェクトの辿り方は複数あります。
ListColumns
ListColumn
DataBodyRange
これらの組み合わせ方でVBAの書き方はいろいろ変わってきます。
ListColumns.Add
これはListColumnを返します。
ListColumn.DataBodyRangeこれは当該列のデータ部全行になります。

Sub VBA100_45_01()
  Dim ws As Worksheet: Set ws = ActiveSheet
  
  With ws.Range("B2").ListObject
    With .ListColumns.Add(.ListColumns("列3").Index + 1)
      .Name = "合計列1"
      .DataBodyRange.Value = "=SUM([@[列1]:[列3]])"
    End With
    
    With .ListColumns.Add
      .Name = "合計列2"
      .DataBodyRange.Formula = "=SUM([@[列4]:[列5]])"
    End With
  End With
End Sub

Sub VBA100_45_02()
  Dim ws As Worksheet: Set ws = ActiveSheet
  
  Dim col As Long
  With ws.Range("B2").ListObject
    col = .ListColumns("列3").Index + 1
    .ListColumns.Add Position:=col
    .ListColumns(col).Name = "合計列1"
    .DataBodyRange.Columns(col).Value = "=SUM([@[列1]:[列3]])"
    
    .ListColumns.Add
    col = .ListColumns.Count
    .ListColumns(col).Name = "合計列2"
    .DataBodyRange.Cells(1, col).Value = "=SUM([@[列4]:[列5]])"
  End With
End Sub


ListObject.DataBodyRange
これはデータ部全体になります。
同じ数式なので全行に入れても良いし、先頭セルにだけ入れても結果は同じになります。
記事にはVBAコードと若干の説明を追加しました。


補足

[列4]このような構造化参照せずに、直接セル参照やR1C1形式での指定も可能ですが、
せっかくテーブルにしているので、構造化参照を使う方が良いでしょう。

テーブルの要素にアクセスするオブジェクトの辿り方は複数あります。
HeaderRowRange
DataBodyRange
ListColumns
ListColumn
ListRows
ListRow
Range
Cells
これらが入り組んでいますので、適宜確認しつつVBAを書くことになります。
以下は、いろいろな書き方での参照するセル範囲がどのようになるかの確認用のVBAです。

Sub VBA100_45_sample()
  Dim ws As Worksheet: Set ws = ActiveSheet
  
  With ws.Range("B2").ListObject
    Debug.Print .Range.Address '$B$2:$F$12
    Debug.Print .HeaderRowRange.Address '$B$2:$F$2
    Debug.Print .DataBodyRange.Address '$B$3:$F$12
    
    Debug.Print .DataBodyRange.Columns(1).Address '$B$3:$B$12
    Debug.Print .ListColumns(1).Range.Address '$B$2:$B$12
    Debug.Print .ListColumns(1).DataBodyRange.Address '$B$3:$B$12
    
    Debug.Print .ListRows(1).Range.Address '$B$3:$F$3
    
    Debug.Print .DataBodyRange(1, 2).Address '$C$3
    Debug.Print .DataBodyRange.Cells(1, 2).Address '$C$3
    Debug.Print .DataBodyRange.Columns(2).Cells(1).Address '$C$3
    Debug.Print .ListColumns(2).Range(2).Address '$C$3
    Debug.Print .ListRows(1).Range(2).Address '$C$3
  End With
End Sub

コメントに記載した出力結果は、問題文のテーブルの場合の結果です。
各オブジェクトの参照する範囲を把握すれば、VBAはいろいろな書き方が可能になります。


サイト内関連ページ

第130回.テーブル操作の概要(ListObject)
・ListObjects コレクション ・ListObject オブジェクト ・テーブル操作のVBAコード
第131回.テーブル操作のVBAコード(ListObject,DataBodyRange)
・テーブル操作のVBAサンプル使用例 ・テーブルに設定 ・テーブルスタイル一覧 ・テーブルの存在確認 ・テーブルを範囲に変換 ・テーブルの範囲を再設定 ・テーブルのセルに値を入れる ・テーブルのセルの数式変更 ・テーブルの行・列のクリア ・テーブルの列の数式設定 ・テーブルの行挿入・削除 ・テーブルの列挿入・削除 ・テーブルのオートフィルター ・テーブルの並べ替え(ソート) ・テーブルの集計行挿入・削除・非表示 ・テーブルの右端に集計列追加 ・テーブルに新しい行列を含めない ・テーブルの使い方の基本 ・サイト内のテーブルに関するページ
第142回.テーブル全件処理とデータ最終行(ListObject,DataBodyRange)
・テーブル全件処理 ・テーブルの特定列のデータ最終行まで ・テーブルが拡張されないパターン ・サイト内のテーブルに関する他のページ




同じテーマ「VBA100本ノック」の記事

42本目:データベース形式に変換
43本目:CSV出力
44本目:全テーブル一覧作成
45本目:テーブルに列追加
46本目:名前定義に使える文字
47本目:Window操作
48本目:配列と数値型
49本目:条件付き書式の判定
50本目:トリボナッチ数列
51本目:シート一覧と印刷ページ数
52本目:複数シートの一括印刷


新着記事NEW ・・・新着記事一覧を見る

構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する|ツイッター出題回答 (2022-08-30)
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)|エクセル入門(2022-08-27)
IF関数の論理式で比較演算子を省略したCOUNT系関数を書くのは|ツイッター出題回答 (2022-08-23)
LAMBDA以降の新関数の使用例|エクセル入門(2022-08-22)
数珠順列(配置に条件付き)を全て出力する|ツイッター出題回答 (2022-08-20)
日付時刻のマイナス表示に対応する方法|ツイッター出題回答 (2022-08-17)
LAMBDA以降の新関数について|エクセル入門(2022-08-16)
条件付きの最大値と中央値("A"が2文字の条件)|ツイッター出題回答 (2022-08-14)
VBAマクロと操作対象データの分離について|ツイッター出題回答 (2022-08-11)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.並べ替え(Sort)|VBA入門
8.マクロって何?VBAって何?|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ