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

いつのExcelからか、各種クラスが用意されているのに気づきました。
まだ、自分自身、データモデルを1個壊してしまいましたので、まだ現場で試せるレベルではありません。
以下、VBAコードはいずれも`ThisWorkbook`モジュールに書いています。
##注意点
- 必ずサンプルのデータモデルで試してください。メジャーのうっかり削除はもちろんのこと、データモデル自体が壊れることもあります。
- Power PivotアドインはONのまま固定して作業してください。OFFにした状態でコードを実行することはデータモデル破損の引き金になります。僕はOFFにして実行して、見事に壊しました。:expressionless:


##まずは壊れた様子から
こうなってしまったら、元に戻せません。更新や再起動しても改善などしませんし、後述のコードも実行エラーとなります。
![無題0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/4dca0f9a-1213-dd79-a1eb-ed84a5f2f48a.png)
※以下は、壊れていない頃の挙動です。
##気を取り直して、Modelオブジェクトから
Workbook配下に`Model`があり、プロパティで取れます。全てはここからです。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.workbook.model

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

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

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

メジャーは`Model`内の`ModelMeasures`プロパティにItemとして入っています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/bcd5856e-f2e0-c22c-3c53-07aa4f9838c5.png)

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

```vb:メジャーの名前を書き出し
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始まりです。

```vb:テーブルの名前を書き出し
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個目のテーブルに追加しています。

```vb:メジャーを追加する
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

```
追加できたようです。だいたい分かりました。
![無題.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/e5bbad4e-e216-0557-53e1-467d76b5412a.png)


ちなみにメジャーを削除するメソッドは単品の`ModelMeasure`の方に用意されてます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.modelmeasure.delete
###Addメソッドの引数「FormatInformation」について
お気づきかもしれませんが、メジャーの追加のコードで付けている名前、変ですよね。これは当初、日付のメジャーを書こうとして上手くいかないので、方向転換した痕跡です。:dizzy_face:
`FormatInformation`引数に混乱していたのです。
公式referenceによると、`Model`オブジェクトからプロパティで拾えることになっているのですが、当初上げたローカルウィンドウの画像の通り、サンプルのデータモデルには`ModelFormatBoolean`と`ModelFormatGeneral`しか出てこないのです。
このため、他のエラーも重なって、日付は取れないのではないかと混乱しました。
ですが、結論としては`ModelFormatDate`オブジェクトは既存の`Model`オブジェクト内になくても、取ることができ、`FormatString`プロパティの設定もできます。例えば、下記のようなコードも通りました。

```vb:日付を設定した例
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
```
↓結果が下記の通り。
![無題2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/9780fbad-4302-a9cd-719b-9f36fd0f0246.png)

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

```vb: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
```
↓結果
![無題3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/1cf32778-812a-e552-9e51-6daf88c4bdc7.png)
##テスト環境
Microsoft365のExcel（バージョン2011、64bit）


##リンク集
[Excel の PowerPivot モデル オブジェクトの概要 | Microsoft Docs](https://docs.microsoft.com/ja-jp/office/vba/excel/concepts/about-the-powerpivot-model-object-in-excel)