Power Pivotモデルオブジェクトを見てみた①

14617 ワード
自分用メモ Excel ExcelVBA PowerPivot Excel テキストリンク

いつのExcelからか、各種クラスが用意されているのに気づきました。
まだ、自分自身、データモデルを1個壊してしまいましたので、まだ現場で試せるレベルではありません。
以下、VBAコードはいずれもThisWorkbookモジュールに書いています。

注意点

  • 必ずサンプルのデータモデルで試してください。メジャーのうっかり削除はもちろんのこと、データモデル自体が壊れることもあります。
  • Power PivotアドインはONのまま固定して作業してください。OFFにした状態でコードを実行することはデータモデル破損の引き金になります。僕はOFFにして実行して、見事に壊しました。

まずは壊れた様子から

こうなってしまったら、元に戻せません。更新や再起動しても改善などしませんし、後述のコードも実行エラーとなります。

※以下は、壊れていない頃の挙動です。

気を取り直して、Modelオブジェクトから

Workbook配下にModelがあり、プロパティで取れます。全てはここからです。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.workbook.model

オブジェクトの全容を確かめる

まずはこんなコードでも書いて、中断し、ローカルウィンドウでオブジェクトの体系を見ます。

オブジェクト覗くだけ
Sub test1()
    Dim x As Excel.Model
    Set x = Me.Model
    Debug.Print x.Name
End Sub

メジャー関係

メジャーの情報を出力する

メジャーを作るのに1個1個VBAの方で手書きすることはないので、コードに使えるような各種設定値を得る必要があります。
例えば、既存のデータモデルからメジャー情報を得られれば、移植の道が開かれそうです。

メジャーはModel内のModelMeasuresプロパティにItemとして入っています。

ここから設定値を取ればよいですね。名前だけ書き出すコードで動作確認してみました。1始まりです。

メジャーの名前を書き出し
Sub test2()
    Dim Measures As ModelMeasures
    Set Measures = Me.Model.ModelMeasures
    Dim i As Long
    For i = 1 To Measures.Count
        Debug.Print Measures.Item(i).Name
    Next i
End Sub

メジャーを作ろう⇒足踏み

で、ModelMeasures.Addメソッドというのがあるので、これでメジャーを追加できそうです。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.modelmeasures.add
しかし、仕様によると、MODELTABLEオブジェクトを引数に入れろとのこと。先にそっちを攻略しないといけません。

テーブルのオブジェクトを取る

Modelのプロパティを通じて、ModelTableオブジェクトが取れます。これまた、名前の書き出しで動作確認してみました。1始まりです。

テーブルの名前を書き出し
Sub test3()
    Dim Tables As ModelTables
    Set Tables = Me.Model.ModelTables
    Dim i As Long
    For i = 1 To Tables.Count
        Debug.Print Tables.Item(i).Name
    Next i
End Sub

なお、テーブルの追加は受け付けない仕様になっているそうです。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.modeltable

今一度メジャーを作ろう

材料が揃いましたので、先ほどのModelMeasures.Addメソッドで、既存のデータモデルにメジャーを追加してみます。説明の都合上、各引数名を明示して書いています。
メジャーは追加先のテーブルは特に意味がありませんから、1個目のテーブルに追加しています。

メジャーを追加する
Sub test4()

    With Me.Model
        'メジャーを追加。最後のDescriptionはOptional。
        .ModelMeasures.Add _
            MeasureName:="年月の最大値", _
            AssociatedTable:=.ModelTables.Item(1), _
            Formula:="0.1+0.2", _
            FormatInformation:=.ModelFormatGeneral, _
            Description:="VBAから追加しましたよ。"
    End With

End Sub

追加できたようです。だいたい分かりました。

ちなみにメジャーを削除するメソッドは単品のModelMeasureの方に用意されてます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.modelmeasure.delete

Addメソッドの引数「FormatInformation」について

お気づきかもしれませんが、メジャーの追加のコードで付けている名前、変ですよね。これは当初、日付のメジャーを書こうとして上手くいかないので、方向転換した痕跡です。
FormatInformation引数に混乱していたのです。
公式referenceによると、Modelオブジェクトからプロパティで拾えることになっているのですが、当初上げたローカルウィンドウの画像の通り、サンプルのデータモデルにはModelFormatBooleanとModelFormatGeneralしか出てこないのです。
このため、他のエラーも重なって、日付は取れないのではないかと混乱しました。
ですが、結論としてはModelFormatDateオブジェクトは既存のModelオブジェクト内になくても、取ることができ、FormatStringプロパティの設定もできます。例えば、下記のようなコードも通りました。

日付を設定した例
Sub test5()
    Dim x As Excel.Model
    Set x = Me.Model
    '別オブジェクトに取ることでFormatStringの指定が効く。
    Dim y As ModelFormatDate
    Set y = x.ModelFormatDate
    y.FormatString = "yyyy/MM/dd"
    With x
        .ModelMeasures.Add _
            MeasureName:="年月の最小値", _
            AssociatedTable:=.ModelTables.Item(1), _
            Formula:="MIN('年月一覧'[年月])", _
            FormatInformation:=y, _
            Description:="VBAから追加したいなー。"
    End With
End Sub

↓結果が下記の通り。

他方、下記のようなコードだと、設定がなされません。

FormatStringの設定に失敗する例
Sub test5_2()
    Dim x As Excel.Model
    Set x = Me.Model
    'エラーにならないが、設定もされない。（デフォルトの日付型「全般」のまま）
    x.ModelFormatDate.FormatString = "yyyy/MM/dd"
    With x
        .ModelMeasures.Add _
            MeasureName:="年月の最小値2", _
            AssociatedTable:=.ModelTables.Item(1), _
            Formula:="MIN('年月一覧'[年月])", _
            FormatInformation:=.ModelFormatDate, _
            Description:="VBAから追加したいなー。"
    End With
End Sub

↓結果

テスト環境

Microsoft365のExcel（バージョン2011、64bit）

リンク集

Excel の PowerPivot モデル オブジェクトの概要 | Microsoft Docs