Home » エクセルマクロ・Excel VBAの使い方 » ListObjectオブジェクト » TableStyleのサンプルを作るExcelマクロ

TableStyleのサンプルを作るExcelマクロ

対象：Excel2007, Excel2010, Excel2013

VBA（Visual Basic for Applications）で、ListObjectオブジェクト・テーブルのスタイルを指定する場合、例えば、
　「テーブルスタイル (淡色) 1」なら「TableStyleLight1」
　「テーブルスタイル (中間) 1」なら「TableStyleMedium1」
　「テーブルスタイル (濃色) 1」なら「TableStyleDark1」
といった文字列を、ListObjects.Addメソッドの引数・TableStyleNameや、ListObject.TableStyleプロパティに指定します。

［ホーム］タブ－［スタイル］グループ－［テーブルとして書式設定］ボタンから、

表示されるスタイルの見本と、「TableStyleLight1」「TableStyleMedium1」「TableStyleDark1」といったスタイルを指定するときに利用する文字列の対応関係がわかり辛く感じたので、新規ワークシートにTableStyleのサンプルを作成するマクロを作ってみました。

［スポンサードリンク］

TableStyleのサンプルを作成するExcelマクロ

以下のマクロを実行すると、アクティブなブックの先頭にワークシートが挿入され、［ホーム］タブ－［スタイル］グループ－［テーブルとして書式設定］ボタンをクリックして表示される色見本と同じ配置でサンプルが作られます。

Sub TableStyleのサンプルを作成する()
　Sheets.Add Before:=Sheets(1)

　Dim r As Long, c As Long, idx As Long
　For r = 1 To 9
　　For c = 1 To 7

　　　idx = idx + 1
　　　Dim tbl_sty As TableStyle
　　　Set tbl_sty = ActiveWorkbook.TableStyles(idx)
　　　If Not tbl_sty.ShowAsAvailableTableStyle Then Exit For

　　　' 4行1列[.Resize(4, 1)]のTableStyleサンプルを
　　　' B2セル[Cells(r * 5 - 3, c * 2)]から作成
　　　With ActiveSheet.ListObjects.Add( _
　　　　　SourceType:=xlSrcRange, _
　　　　　Source:=Cells(r * 5 - 3, c * 2).Resize(4, 1), _
　　　　　XlListObjectHasHeaders:=xlYes)
　　　　　　.TableStyle = tbl_sty.Name
　　　　　　.HeaderRowRange.Value = tbl_sty.Name
　　　End With

　　Next c
　Next r

　With Columns("A:O")
　　.ColumnWidth = 1
　　.AutoFit
　End With

End Sub

TableStyleのサンプルを作成する処理の流れ

Excel 2007・2010・2013いずれも、［ホーム］タブ－［スタイル］グループ－［テーブルとして書式設定］ボタンをクリックすると、下図のように、9行×7列でテーブルのスタイルが表示されているので、

TableStyleのサンプルを作るExcelマクロ

行（Row）方向はカウンター変数rを1から9まで、列（Column）方向はカウンター変数cを1から7までという条件でFor～Nextループを回します。
　For r = 1 To 9
　　For c = 1 To 7

ループの中ではまず、idxをインクリメントしておいてから、
　　　idx = idx + 1

Workbook.TableStylesコレクションから、TableStyleオブジェクトを取得して、オブジェクト変数に格納しておきます。
　　　tbl_sty = ActiveWorkbook.TableStyles(idx)

TableStyesコレクションにはピボットテーブル等のスタイルも含まれているので、テーブルスタイルでなければ、For～Next文を抜けています。
　　　If Not tbl_sty.ShowAsAvailableTableStyle Then Exit For

次に、4行1列のサンプルテーブルをListObjects.Addメソッドで作成します。
　　　With ActiveSheet.ListObjects.Add( _
　　　　　SourceType:=xlSrcRange, _
　　　　　Source:=Cells(r * 5 - 3, c * 2).Resize(4, 1), _
　　　　　XlListObjectHasHeaders:=xlYes)

先日ご紹介した、選択されているセル範囲をテーブルにするマクロでは、
　　　　　Source:=Selection.Cells, _
となっていましたが、今回はテーブルを作成する位置を、ループを回しながら少しずつずらしたいので、
　　　　　Source:=Cells(r * 5 - 3, c * 2).Resize(4, 1), _
と指定しています。

テーブル作成後に、作成したテーブルのスタイルを設定して、
　　　　　　.TableStyle = tbl_sty.Name
テーブルのフィールド見出しにスタイル名を入力します。
　　　　　　.HeaderRowRange.Value = tbl_sty.Name

For～Nextループを抜けた後に、作成したサンプルの列幅を調整しています。
　With Columns("A:O")
　　.ColumnWidth = 1
　　.AutoFit