手探りでマクロ作成 - Infomentのブログ～Excel VBA奮闘記～

私の場合、日々、Excel マクロの作成は手探りだ。
立て板に水の如くコードが書けると良いのだが、なかなか上手くはいかない。
自分の知らない機能を自動化に組み込むときは、特にそう。
f:id:Infoment:20200406224629p:plain

例えば、いつもの「なんちゃって個人情報」。
f:id:Infoment:20200406224750p:plain

このテーブルに、県名でフィルターを掛けるためのスライサーを追加したい。早速、マクロの記録で確認してみよう。

↓ 出来立てのマクロ。

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects( _
        "Table_20200406_224340"), "都道府県").Slicers.Add ActiveSheet, , "都道府県", "都道府県", _
        213.75, 340.5, 144, 187.5
    ActiveSheet.Shapes.Range(Array("都道府県")).Select
End Sub

まず、冒頭の不要なコメント行は全て削除する。
すると、少し綺麗になる。

Sub Macro2()
    ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects( _
        "Table_20200406_224340"), "都道府県").Slicers.Add ActiveSheet, , "都道府県", "都道府県", _
        213.75, 340.5, 144, 187.5
    ActiveSheet.Shapes.Range(Array("都道府県")).Select
End Sub

選択する必要は無いので、Selectの一行も消してしまおう。
f:id:Infoment:20200406225222p:plain

すると、実質一行だけが残る。
今度は、作成したスライサーを何某か操作することを考えて、変数にセットしたくなる。そこでまず、適当にセットする。

Sub Macro2()
    ' テキトーな変数を準備。型不明。
    Dim a
    
    Set a = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects( _
        "Table_20200406_224340"), "都道府県").Slicers.Add(ActiveSheet, , "都道府県", "都道府県", _
        213.75, 340.5, 144, 187.5)
End Sub

実行してみると、Excelに怒られなかった。どうやら正しい記述のようだ。
f:id:Infoment:20200406225626p:plain

次に、セット後の変数の型を調べてみる。
f:id:Infoment:20200406225741p:plain

すると、変数の型が「Slicer」であることが分かる。
f:id:Infoment:20200406225821p:plain

F2を押して、オブジェクトブラウザで確認してみよう。
f:id:Infoment:20200406225910p:plain

なるほど、これで変数の型が決まった。
f:id:Infoment:20200406230024p:plain

ところで、最後の４つの数字、これは何だろう。
f:id:Infoment:20200406230133p:plain

多分これは、スライサーのサイズと位置だな。後で調整するから、正直、作成時点ではどうでもいい。ならば、無指定で作成してみよう。

Sub Macro2()
    Dim Slicer As Excel.Slicer
    
    Set Slicer = ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects( _
        "Table_20200406_224340"), "都道府県").Slicers.Add(ActiveSheet, , "都道府県", "都道府県")
End Sub

これでも作成できることが、確認できた。
あとは、固定の部分を引数にしてみる。これぐらいかな。

テーブル
ラベル名
作成するシート

Sub Macro2(source_table As ListObject, _
           target_label As String, _
           destination_sheetname As String)
    Dim Slicer As Excel.Slicer
    Dim Sh As Worksheet
    Set Sh = Sheets(destination_sheetname)
    
    Set Slicer = ActiveWorkbook.SlicerCaches.Add2(source_table, _
                    target_label).Slicers.Add(Sh, , target_label, target_label)
End Sub

Sub test()
    Macro2 ActiveSheet.ListObjects(1), _
           "都道府県", _
           "Sheet1"
End Sub

ここまで来ると、Subプロシージャではなく、Functionプロシージャにしようかという気になる。

Function AddedSlicer(source_table As ListObject, _
                     target_label As String, _
                     destination_sheetname As String) As Excel.Slicer
    Dim Sh As Worksheet
    Set Sh = Sheets(destination_sheetname)
    
    Set AddedSlicer = ActiveWorkbook.SlicerCaches.Add2(source_table, _
                    target_label).Slicers.Add(Sh, , target_label, target_label)
End Function

Sub test()
    With AddedSlicer(ActiveSheet.ListObjects(1), _
                    "都道府県", _
                    "Sheet1")
        .Left = Range("B4").Left
        .Top = Range("B4").Top
    End With
End Sub