リスト・テーブル（ListObject オブジェクト）

2016/08/02 2016/08/19

リスト・テーブル（ListObject オブジェクト）を取得

【書式】

object.ListObject [.Item]( index )

引数・戻り値

object ・・・対象となる Worksheet オブジェクトを指定します。
index ・・・インデックス番号（1 から始まる整数値）または、テーブル名（文字列）を指定します。

ListObjects コレクションに関しての詳細は、リスト・テーブル（ListObjects オブジェクト）を参照してください。

ListObject オブジェクト使用例 1

サンプルVBAソース 1

　
Sub Sample_ListObject()
  
    Sheets("Sheet4").Activate
    
    Dim mysheet As Worksheet
    Dim mytbl As ListObject
    Set mysheet = ActiveWorkbook.ActiveSheet
    
    On Error GoTo ErrH
    
    'テーブル（リスト）の取得
    Set mytbl = mysheet.ListObjects.Item(1)
    
    'テーブルの名前を表示
    MsgBox mytbl.Name
        
    Exit Sub
    
ErrH:
    MsgBox "テーブル（リスト）はありません。"

End Sub

Sub Sample_ListObject()

Sheets("Sheet4").Activate

Dim mysheet As Worksheet

Dim mytbl As ListObject

Set mysheet = ActiveWorkbook.ActiveSheet

On Error GoTo ErrH

'テーブル（リスト）の取得

Set mytbl = mysheet.ListObjects.Item(1)

'テーブルの名前を表示

MsgBox mytbl.Name

Exit Sub

ErrH:

MsgBox "テーブル（リスト）はありません。"

End Sub

実行結果 1

サンプルVBAソース 1 実行前

サンプルVBAソース 1 実行後

ListObject オブジェクトの主なメソッド

Delete メソッド

ListObject オブジェクトを削除し、ワークシートからセルデータをクリアします。

【書式】

object.Delete

引数・戻り値

object ・・・対象となる ListObject オブジェクトを指定します。

Unlist メソッド

作成したテーブル（リスト）を通常の表（範囲）に戻します。

【書式】

object.Unlist

引数・戻り値

object ・・・対象となる ListObject オブジェクトを指定します。

Excel 2007 以降で、テーブルを作成するとテーブルのスタイルが設定されますが、Unlink メソッドを使用しても、そのスタイルは残ってしまいます。スタイルを解除するには、Unlink メソッドを実行する前に、TableStyle プロパティ に、””（空文字）を設定します。

ListObject オブジェクト使用例 2

サンプルVBAソース 2

　
Sub Sample_ListObject_Unlist()
  
    Sheets("Sheet4").Activate
    
    Dim mysheet As Worksheet
    Dim mytbl As ListObject
    Set mysheet = ActiveWorkbook.ActiveSheet
    
    On Error GoTo ErrH
    
    'テーブル（リスト）の取得
    Set mytbl = mysheet.ListObjects.Item(1)
    
    'テーブルのスタイル解除
    mytbl.TableStyle = ""
    
    'テーブルを通常の範囲に戻す
    mytbl.Unlist
        
    Exit Sub
    
ErrH:
    MsgBox "テーブル（リスト）はありません。"

End Sub

Sub Sample_ListObject_Unlist()

Sheets("Sheet4").Activate

Dim mysheet As Worksheet

Dim mytbl As ListObject

Set mysheet = ActiveWorkbook.ActiveSheet

On Error GoTo ErrH

'テーブル（リスト）の取得

Set mytbl = mysheet.ListObjects.Item(1)

'テーブルのスタイル解除

mytbl.TableStyle = ""

'テーブルを通常の範囲に戻す

mytbl.Unlist

Exit Sub

ErrH:

MsgBox "テーブル（リスト）はありません。"

End Sub

実行結果 2

サンプルVBAソース 2 実行前

サンプルVBAソース 2 実行後

サンプルVBAソース 3

　
Sub Sample_ListObject_Delete()
  
    Sheets("Sheet4").Activate
    
    Dim mysheet As Worksheet
    Dim mytbl As ListObject
    Set mysheet = ActiveWorkbook.ActiveSheet
    
    On Error GoTo ErrH
    
    'テーブル（リスト）の取得
    Set mytbl = mysheet.ListObjects.Item(1)
    
    'テーブルを削除
    mytbl.Delete
        
    Exit Sub
    
ErrH:
    MsgBox "テーブル（リスト）はありません。"

End Sub

Sub Sample_ListObject_Delete()

Sheets("Sheet4").Activate

Dim mysheet As Worksheet

Dim mytbl As ListObject

Set mysheet = ActiveWorkbook.ActiveSheet

On Error GoTo ErrH

'テーブル（リスト）の取得

Set mytbl = mysheet.ListObjects.Item(1)

'テーブルを削除

mytbl.Delete

Exit Sub

ErrH:

MsgBox "テーブル（リスト）はありません。"

End Sub

実行結果 3

サンプルVBAソース 3 実行前

サンプルVBAソース 3 実行後

ListObject オブジェクトの主なプロパティ

プロパティ	内容
Name プロパティ	テーブル（リスト）の名前を取得・設定します。 ※ テーブル作成時、名前付きセル範囲として自動的に生成されます。ただし、その範囲は、見出しの部分を除いた範囲が、登録されます。
Range プロパティ	テーブル（リスト）の範囲を取得します。
HeaderRowRange プロパティ	テーブル（リスト）の見出しの部分の範囲を取得します。
DataBodyRange プロパティ	テーブル（リスト）のレコード部分（見出しを除いた部分）の範囲を取得します。
ShowTotals プロパティ	テーブル（リスト）の集計行を表示するかどうかを設定します。
TableStyle プロパティ	テーブル（リスト）のスタイルを設定します。
ListColumns プロパティ	テーブル（リスト）内のフィールドを取得します。
ListRows プロパティ	テーブル（リスト）内のレコードを取得します。

- リスト・テーブル ListObject オブジェクト, ListObject.DataBodyRange プロパティ, ListObject.Delete メソッド, ListObject.HeaderRowRange プロパティ, ListObject.ListColumns プロパティ, ListObject.ListRows プロパティ, ListObject.Range プロパティ, ListObject.ShowTotals プロパティ, ListObject.TableStyle プロパティ, ListObject.Unlist メソッド, ListObjects.Item プロパティ, VBA基本, エクセルVBA, リスト・テーブル

Comment

Table より:

2018年3月15日 12:26 PM

はじめまして
ListObjectで質問です。

ブック内の全テーブル名と参照範囲を取得して
ActiveWorksheetsに書きだすことは、可能でしょうか？
また、書きだした後にテーブル名を変更し
戻すことは、可能でしょうか？

宜しければ教えて下さいませ。
宜しくお願い致します。

返信
- サイト管理人より:
  
  2018年3月15日 9:54 PM
  
  コメントありがとうございます。
  まず、ブック内の全テーブル名と参照範囲を ActiveSheet に書き出すサンプルです。
  
  Sub Sample_GetListObject()
  
  Dim sht As Worksheet
  
  Dim tbl As ListObject
  
  Dim i As Long
  
  With ActiveSheet
  
  Cells(1, 1).Value = “テーブル名”
  
  Cells(1, 2).Value = “範囲”
  
  i = 2
  
  For Each sht In ThisWorkbook.Worksheets
  
  For Each tbl In sht.ListObjects
  
  .Cells(i, 1).Value = tbl.Name
  
  .Cells(i, 2).Value = sht.Name & “!” & _
  
  tbl.Range.Address(rowabsolute:=False, columnabsolute:=False)
  
  i = i + 1
  
  Next
  
  Next
  
  End With
  
  End Sub
  
  あと、後半の
  「また、書きだした後にテーブル名を変更し戻すことは、可能でしょうか？」
  という部分の意味がよくわからなかったのですが、とりあえず
  「テーブル3」という名前のテーブルがあったとして、その名前を「Table3」に変更するサンプルを作ってみました。
  
  Sub Sample_SetTableName()
  
  Dim sht As Worksheet
  
  Dim tbl As ListObject
  
  For Each sht In ThisWorkbook.Worksheets
  
  For Each tbl In sht.ListObjects
  
  If tbl.Name = “テーブル3” Then
  
  tbl.Name = “Table3”
  
  GoTo ENDLINE:
  
  End If
  
  Next
  
  Next
  
  ENDLINE:
  End Sub
  
  返信
  - Table より:
    
    2018年3月16日 10:14 AM
    
    管理人様
    
    返信ありがとうございます。
    
    ＞書きだした後にテーブル名を変更し戻すことは、可能でしょうか？
    ＞という部分の意味がよくわからなかったのですが、
    
    説明が下手で申し訳ございません。
    以下のコードを参考にしているのですが、
    このコードは、定義された名前にしかできないので
    これをテーブル名で実行したいです。
    簡単に説明致しますと、
    テーブル名と参照範囲を一度アクティブなシートに書出し、
    シート上で名前を変更する等の書換えを行い、
    それを名前の定義に戻したいのです。
    そのようなことは、可能でしょうか？
    
    名前の定義を書出し戻すサンプルコードです。
    
    ‘————————————————–
    Sub NamesListsOut()
    ‘名前定義書き出し
    Dim i As Integer
    With ActiveWorkbook
    For i = 1 To .Names.Count
    　Cells(i, 1).Value = .Names(i).Name
    　Cells(i, 2).Value = “‘” & .Names(i).RefersToLocal
    Next i
    End With
    End Sub
    ‘————————————————–
    
    Sub NamesListsIn()
    ‘名前定義登録
    Dim n As Name
    Dim i As Integer
    
    With ActiveWorkbook
    
    ‘一旦名前定義を削除
    For Each n In .Names
    　n.Delete
    Next n
    ‘登録
    On Error Resume Next
    For i = 1 To Range(“A65536”).End(xlUp).Row
    　 .Names.Add Cells(i, 1).Value, Cells(i, 2).Value
    Next i
    On Error GoTo 0
    End With
    End Sub
    ‘————————————————–
    
    返信
    - サイト管理人より:
      
      2018年3月16日 10:20 PM
      
      ブック内の全テーブル名と参照範囲を ActiveSheet に書き出すサンプルです。
      これは前回と同じです。
      
      Sub Sample_GetListObject()
      
      Dim sht As Worksheet
      
      Dim tbl As ListObject
      
      Dim i As Long
      
      With ActiveSheet
      
      Cells(1, 1).Value = “テーブル名”
      
      Cells(1, 2).Value = “範囲”
      
      i = 2
      
      For Each sht In ThisWorkbook.Worksheets
      
      For Each tbl In sht.ListObjects
      
      .Cells(i, 1).Value = tbl.Name
      
      .Cells(i, 2).Value = sht.Name & “!” & _
      
      tbl.Range.Address
      
      i = i + 1
      
      Next
      
      Next
      
      End With
      
      End Sub
      
      上記マクロを実行するとアクティブシートに、ブック内の全テーブルの名前と参照範囲が入力されます。
      シートのテーブル名を書き換えて以下のマクロを実行するとテーブル名が変更されます。
      
      Sub Sample_SetTbleName2()
      
      Dim sht As Worksheet
      
      Dim tbl As ListObject
      
      Dim strTableRange As String
      
      Dim i As Long
      
      With ActiveSheet
      
      On Error Resume Next
      
      For Each sht In ThisWorkbook.Worksheets
      
      For Each tbl In sht.ListObjects
      
      strTableRange = sht.Name & “!” & tbl.Range.Address
      
      For i = 2 To .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
      
      If strTableRange = .Cells(i, 2).Value Then
      
      tbl.Name = .Cells(i, 1).Value
      
      Exit For
      
      End If
      
      Next i
      
      Next tbl
      
      Next sht
      
      End With
      
      End Sub
      
      返信
      - Table より:
        
        2018年3月19日 10:55 AM
        
        管理人様
        
        ありがとうございます。
        テーブル名の書出しと登録が無事にできました。
        いろいろと勉強になりました。
        また、宜しくお願い致します。