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

この記事から得るもの

ListObjectの追加した行に、数式を一括代入する方法が分かる。
ListObjectの追加した行に、値のみを代入する方法が分かる。

１　今回のお題

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

f:id:bimori466:20210122032443p:plain

excel-ubara.com

２　今回のお題の意図

DataBodyRangeを使って数式を一括代入すると楽ということを伝えたい。

３　回答

ListObjectについてまだ知識が浅いので、サイト管理者のコードをひとまずコピペしました。

サイト管理者のコード

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]])"
            .DataBodyRange.Select
        End With
    
        With .ListColumns.Add
            .Name = "合計列2"
            .DataBodyRange.Formula = "=SUM([@[列4]:[列5]])"
        End With
    End With
End Sub

DataBodyRangeって何？？

マイクロソフトのサイト解説によると、「テーブルの見出しの行を除く、値の範囲を表す Range オブジェクトを返します。読み取り専用です。」との記述でした。

例文

ActiveSheet.ListObjects.Item(1).DataBodyRange.Select

この処理を実行すると、選択されるセルは以下の画像の通りです。

f:id:bimori466:20210122033459p:plain

マイクロソフトの解説の通り、テーブルの見出しの行を除く、値の範囲を表す Range オブジェクトが選択されました。

しかし、これではすべてのデータ部が選択されてしまいます。なので、追加した列だけを選択するにはどうしたらいいか。
以下のコード部をみてください。

　　With ws.Range("B2").ListObject
        With .ListColumns.Add(.ListColumns("列3").Index + 1)
            .Name = "合計列1"
            .DataBodyRange.Value = "=SUM([@[列1]:[列3]])"
            .DataBodyRange.Select
        End With

「.DataBodyRange.Select」を実行すると、追加した行のデータ部のみを選択します。
また、「.DataBodyRange.Value = "=SUM([@[列1]:[列3]])"」で一括で追加した行にSumの数式を代入でき、簡単に数値の合計を入力できます。以下の画像をご覧ください。

f:id:bimori466:20210122034359p:plain

セルに数式を入れたくない！という私のポリシー

このデータ量なら問題ないのですが、リストが大きくなると数式を入れるとデータ量が重くなり動作が遅くなります。個人的には、セルに数式を代入するのは好きではありません。
しかし、「.DataBodyRange.Value」は数式を一括代入はできるのですが、値のみを代入することはできないようです。そのため、私の考えたコードは以下になります。

私の作成したコード

Sub ノック45本目_1()

    '変数定義-------------------------------------------------------
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim lastRow As Long, k_listRow As Long
    '________________________________________________________________
    
    
    lastRow = ws.Range("A1").ListObject.Range.Rows.Count
    
    '追加行のデータ作成、数値合計
    With ws.Range("A1").ListObject
        .ListColumns.Add Position:=4
            
        .ListColumns(4).Range(1) = "合計列1"
        
        For k_listRow = 2 To lastRow
            .ListColumns(4).Range(k_listRow) = WorksheetFunction.Sum(Range(Cells(k_listRow, 1), Cells(k_listRow, 3)))
        Next
        
        
        .ListColumns.Add
        
        .ListColumns(7).Range(1) = "合計列2"
        
        For k_listRow = 2 To lastRow
            .ListColumns(7).Range(k_listRow) = WorksheetFunction.Sum(Cells(k_listRow, 5), Cells(k_listRow, 6))
        Next
    End With
    
End Sub

リストを追加する部分は同じです。異なる点は、「.DataBodyRange.Value = "=SUM([@[列1]:[列3]])"」で数式を一括代入するのではなく、For Nextでループし、「WorksheetFunction.Sum」で数値を合計し値のみを追加した行に代入しています。これなら、大量のリストでもデータ量は軽くなるばずです。