Power Pivot - How to get ModelMeasureNames Collection (or Calculated Fields Manager) of model using Excel/VBA

[VladimirV]

Hello everybody,

I have a trouble with getting ModelMeasureNames collection of Power Pivot model by using Excel/VBA. I can't find any function that would allow me to do this. I can get the Power Pivot model of the workbook by ActiveWorkbook.model. But this model object does not have any function that would allow me to get ModelMeasuresNames collection. The only thing I find is that there is workbook event "Private Sub Workbook_ModelChange(ByVal Changes As ModelChanges)" and the variable Changes allows me to get the ModelMeasureNames collection by Changes.MeasuresAdded. But this is not relevant to me as I am not catching events. I am using Excel 2013. Any help and suggestions are very appreciated.

Best regards,
Vladimir

 

[anvg]

Hi
Use ModelMeasures collection instead https://msdn.microsoft.com/en-us/library/office/mt574895.aspx
Each its member is ModelMeasure which contains a Name property.
Regards,

 

[VladimirV]

Hello anvg,

Thanks a lot for your reply. Unfortunately, this does not work to me. Here is my code:
Set PPmodel = ActiveWorkbook.model
MsgBox PPmodel.Modelmeasures.Item(1).Name
And there is an error on the second line. Moreover, I can't understand, why in the ActiveWorkbook.model the word "model" starts with lower case (it is set automatically for me even if I try to write "model" with upper case). And when I write PPmodel. I do not have any suggestions to choose property "ModelMeasures" (but I see such properties as ModelTables, ModelRelationships).

Thank again!

Best regards,
Vladimir

 

[VladimirV]

Anvg,

I also do not see suggestions to choose the following properties of Model object: ModelFormatBoolean, ModelFormatCurrency, ModelFormatDate, ModelFormatDecimalNumber, ModelFormatGeneral, ModelFormatPercentageNumber, ModelFormatScientificNumber, ModelFormatWholeNumber and ModelMeasures. Maybe something wrong with my Excel 2013?

 

[anvg]

Hi, Vladimir.
I am sorry. Maybe reinstall your Excel 2013? Manually does Powerpivot work (calculates measures, shows tables)?
Regards,

 

[VladimirV]

Hi, Anvg,

Thanks, I will try to do it. Manually Powerpivot works fine. I can create my measures (calculated fields) and use them in power pivot tables.

Best wishes,