EXCEL VBA テーブル作成・テーブル解除・設定の方法

2020年1月27日

EXCEL VBA テーブル作成・テーブル解除・設定の方法（ListObjectsｺﾚｸｼｮﾝのAddﾒｿﾞｯﾄ）

●はじめに

EXCELの機能でデータベースを管理する場合、とても便利な機能でテーブル機能があります。このテーブル機能は、データベースを管理する場合にとても便利な機能です。今回は、そのテーブル機能をVBAで作成・解除・設定する方法を説明いたします。VBAでテーブル機能を設定するには、ListObjectsコレクションのAddメゾットを利用します。それでは、VBAでのテーブル作成方法をサンプルプログラムを交えて説明いたします。

●書式の説明 [ ListObjectsコレクションのAddメゾット]

【テーブル作成　ListObjectsコレクションのAddメゾットの利用方法】

WorkSheetオブジェクト.ListObjects.Add
(SourceType,Source,LinkSource,XlListObjectHeaders,Destination)

ListObjectsコレクションのAddメゾット
SourceType	（省略可能）元データの種類を指定します。
Source	（省略可能）元データのセル範囲を指定します。
LinkSource	（省略可能）外部データベースをリンクするかを論理値で指定します。
XlListObjectHeaders	（省略可能）元データの先頭行が見出し行かどうかを定数で指定します。
Destination	（省略可能）テーブルを作成する左上のセルをRangeオブジェクトで指定します。

※テーブルをVBAで作成するにあたり、詳細に設定する事は可能ですが、実際は定数を設定せずに、ほぼ省略できるので特にこだわりが無ければ、LinkSourceのみの設定で良いと思います。それでは、下記に３つのサンプルプログラムを作成しましたので、参考して下さい。

EXCEL VBA テーブル作成・設定の方法・セル範囲のテーブル化（ListObjectsｺﾚｸｼｮﾝのAddﾒｿﾞｯﾄ）

●プログラム説明　（サンプル①）

下記のサンプルプログラムは、EXCELデータからテーブルを作成します。作成するテーブルについては、ワークシート「品名リスト」からテーブル名「品目テーブル」で作成します。

※テーブルは、一度作成してあるテーブルに対して再度、テーブルを作成するとエラーが発生しますので、注意してください。

Sub テーブル設定1()

    Dim ws01 As Worksheet

    Set ws01 = Worksheets("品名リスト") 'シート名（”品目リスト”）⇒　Ws01にセット


    ws01.ListObjects.Add(Source:=Range("A2").CurrentRegion).Name = "品目テーブル"
    'テーブルを設定します。セルA2から始まるテーブルを設定（定義）：テーブル名を品目テーブル
               
End Sub

Sub テーブル設定1()

Dim ws01 As Worksheet

Set ws01 = Worksheets("品名リスト") 'シート名（”品目リスト”）⇒　Ws01にセット

ws01.ListObjects.Add(Source:=Range("A2").CurrentRegion).Name = "品目テーブル"

'テーブルを設定します。セルA2から始まるテーブルを設定（定義）：テーブル名を品目テーブル

End Sub

●実行前～実行後　※プログラム実行後、EXCELデータからテーブルが作成されました。
（画面クリックして拡大）

EXCEL VBA テーブル作成・テーブル書式・設定の方法（デザインの設定・変更：TableStyle）

●プログラム説明　（サンプル②）

下記のサンプルプログラムは、テーブルを作成して作成したデザインを順番に変更するサンプルプログラムです。EXCELでテーブルデザインを変更する際は、デザインを選びながら設定する必要がありますが、VBAプログラムで順番にデザインが変わるプログラムを作成する事で確認しながら好きなデザインを選ぶ事ができます。

※テーブル書式の一覧（TableStyle）は、下記を参照してください。

【テーブル書式・デザインの設定：例】
ActiveSheet.ListObjects(“品目テーブル”).TableStyle = “TableStyleMedium1”
※ (テーブルスタイル (中間)TableStyleMediumの１番のデザインが設定されます。)

テーブルのスタイル種類	TableStyle	範囲
テーブルスタイル (淡色)	TableStyleLight	１～21
テーブルスタイル (中間)	TableStyleMedium	１～28
テーブルスタイル (濃色)	TableStyleDark	１～11

Sub テーブル設定2()  'デザイン選択
    
    Dim i As Long
    
    
    On Error GoTo Err_Shori  'テーブルを設定していない場合は、↓処理でエラーが発生するので「Err_Shori」へ飛ぶ

    ActiveSheet.ListObjects(1).Unlist  'テーブルを設定している場合は、テーブル解除
    
Err_Shori:


    Cells.ClearFormats  'シートの背景色と書式をクリアー
    

    ActiveSheet.ListObjects.Add(Source:=Range("A2").CurrentRegion).Name = "品目テーブル"  'セルA2から始まるテーブルを設定（定義）：テーブル名を「品目テーブル」
    
        
        
    'テーブルのデザインを選択（順番に表示します）
    
    For i = 1 To 28  'TableStyleMediumのデザインは、１～２８までの種類（デザイン）があります。
    
        ActiveSheet.ListObjects("品目テーブル").TableStyle = "TableStyleMedium" & i  'テーブルデザイン（書式）を設定します。
        
        MsgBox "このデザインは「TableStyleMedium" & i & "」です"
        
    Next i

End Sub

Sub テーブル設定2() 'デザイン選択

Dim i As Long

On Error GoTo Err_Shori 'テーブルを設定していない場合は、↓処理でエラーが発生するので「Err_Shori」へ飛ぶ

ActiveSheet.ListObjects(1).Unlist 'テーブルを設定している場合は、テーブル解除

Err_Shori:

Cells.ClearFormats 'シートの背景色と書式をクリアー

ActiveSheet.ListObjects.Add(Source:=Range("A2").CurrentRegion).Name = "品目テーブル" 'セルA2から始まるテーブルを設定（定義）：テーブル名を「品目テーブル」

'テーブルのデザインを選択（順番に表示します）

For i = 1 To 28 'TableStyleMediumのデザインは、１～２８までの種類（デザイン）があります。

ActiveSheet.ListObjects("品目テーブル").TableStyle = "TableStyleMedium" & i 'テーブルデザイン（書式）を設定します。

MsgBox "このデザインは「TableStyleMedium" & i & "」です"

Next i

End Sub

●実行前～実行後　※プログラム実行後、テーブルのデザインが順番に切り替わります。ひとつずつ手動で切り替えるのは大変ですが、プログラム化して実行する事で順番にデザイン（Style）が切り替わり好きなデザインを選ぶ事が出来ます。
（画面クリックして拡大）

EXCEL VBA テーブル作成（他のシートからデータを収集しテーブルを作成・再設定）

●プログラム説明　（サンプル③）

下記のサンプルプログラムは、各シート（東京支店・大阪支店）にあるデータを集計シートにデータを収集して、収集した集計シートにテーブルを作成します。今回のプログラムは、何度でも収集出来る様に作成しています。また、シートも条件により増やしても集計出来る様にプログラムを作成いています。

Sub テーブル設定03() '他のシートから集計しテーブルを再設定

    Dim ws01, ws02, ws03 As Worksheet
    Dim i As Integer
    Set ws01 = Worksheets("集計") 'シート名（"集計"）⇒　Ws01にセット
    Set ws02 = Worksheets("東京支店") 'シート名（"東京支店"）⇒　Ws02にセット
    Set ws03 = Worksheets("大阪支店")  'シート名（"大阪支店"）⇒　Ws03にセット
    

    On Error GoTo Err_Shori  'テーブルを設定していない場合は、↓処理でエラーが発生するので「Err_Shori」へ飛ぶ

    ws01.ListObjects(1).Unlist  'テーブルを設定している場合は、テーブル解除
    
Err_Shori:

    ws01.Cells.Clear 'シート（集計）をクリアー

    ws02.Range("A1:F1").Copy '東京支店１行目の見出しをコピー
    ws01.Range("A1").PasteSpecial Paste:=xlPasteValues  'シート（集計）に見出しを貼り付け

    For i = 2 To Worksheets.Count  'シートNo.２：東京支店⇒最後のシート　大阪支店までループ
    
        Worksheets(i).Range("A1").CurrentRegion.Offset(1).Copy  'シートの内容コピー
        ws01.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
                                  'シート「集計」にデータの最終行に貼り付ける（文字列のみ）

    Next i

    ws01.ListObjects.Add(Source:=Range("A1").CurrentRegion).Name = "集計テーブル"
                'シート「集計」に各シートから集めたデータをテーブル設定します。

End Sub

Sub テーブル設定03() '他のシートから集計しテーブルを再設定

Dim ws01, ws02, ws03 As Worksheet

Dim i As Integer

Set ws01 = Worksheets("集計") 'シート名（"集計"）⇒　Ws01にセット

Set ws02 = Worksheets("東京支店") 'シート名（"東京支店"）⇒　Ws02にセット

Set ws03 = Worksheets("大阪支店") 'シート名（"大阪支店"）⇒　Ws03にセット

On Error GoTo Err_Shori 'テーブルを設定していない場合は、↓処理でエラーが発生するので「Err_Shori」へ飛ぶ

ws01.ListObjects(1).Unlist 'テーブルを設定している場合は、テーブル解除

Err_Shori:

ws01.Cells.Clear 'シート（集計）をクリアー

ws02.Range("A1:F1").Copy '東京支店１行目の見出しをコピー

ws01.Range("A1").PasteSpecial Paste:=xlPasteValues 'シート（集計）に見出しを貼り付け

For i = 2 To Worksheets.Count 'シートNo.２：東京支店⇒最後のシート　大阪支店までループ

Worksheets(i).Range("A1").CurrentRegion.Offset(1).Copy 'シートの内容コピー

ws01.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues

'シート「集計」にデータの最終行に貼り付ける（文字列のみ）

Next i

ws01.ListObjects.Add(Source:=Range("A1").CurrentRegion).Name = "集計テーブル"

'シート「集計」に各シートから集めたデータをテーブル設定します。

End Sub

●実行前～実行後　※プログラム実行後、シート「集計」に各シートからデータ集まりシート「集計」にテーブルを作成されました。
（画面クリックして拡大）

EXCEL VBA テーブルの設定を削除（作成したテーブルを解除）

●プログラム説明　（サンプル④）

下記のサンプルプログラムは、作成したテーブルを解除するプログラムです。

Sub テーブル解除()

    On Error GoTo Err_Shori    '既にテーブル解除している場合エラーが発生するので、エラー処理

    ActiveSheet.ListObjects(1).Unlist  'テーブルを解除
    
    Exit Sub  'テーブル解除したので終了

Err_Shori:

    MsgBox "解除済み"
        
End Sub