none
VBA PowerPivot object model- Adding measures with ModelMeasures.Add RRS feed

  • Question

  • Hello,

    Just started playing around with the PowerPivot object mode and I'm trying to add a calculated field to an existing PowerPivot table with VBA in Excel 2016, however I can't seem to get anywhere.

    I figured something like this would work but get an Invalid Procedure Call error on line 3:

    Dim tbl As ModelTable
    Set tbl = ActiveWorkbook.Model.ModelTables(1)
    ActiveWorkbook.Model.ModelMeasures.Add "Test", tbl, "=1+1", ModelFormatWholeNumber

    Any ideas? I'm guessing it relates to the table parameter?

    Thanks

    Friday, July 8, 2016 10:08 AM

Answers

  • I got this to work using the macro recorder and right-clicking to edit an existing measure.  I turns out it actually deletes then adds.  Here is the code:

        ActiveWorkbook.Model.ModelMeasures.Add "$ My Measure", ActiveWorkbook. _
            Model.ModelTables("Fact"), "sum([AMT])", _
            ActiveWorkbook.Model.ModelFormatCurrency("Default", 0)

    • Marked as answer by blaaaaaaargh Wednesday, July 13, 2016 1:37 PM
    Tuesday, July 12, 2016 9:59 PM

All replies

  • I got this to work using the macro recorder and right-clicking to edit an existing measure.  I turns out it actually deletes then adds.  Here is the code:

        ActiveWorkbook.Model.ModelMeasures.Add "$ My Measure", ActiveWorkbook. _
            Model.ModelTables("Fact"), "sum([AMT])", _
            ActiveWorkbook.Model.ModelFormatCurrency("Default", 0)

    • Marked as answer by blaaaaaaargh Wednesday, July 13, 2016 1:37 PM
    Tuesday, July 12, 2016 9:59 PM
  • Great thanks - looks like my error was that in thinking that the FormatInformation was a property rather than an object.

    My amended code for anyone interested:

    Dim Mdl As Model
    Dim tbl As ModelTable
    Set Mdl = ActiveWorkbook.Model
    Set tbl = Mdl.ModelTables(1)
    
    Mdl.ModelMeasures.Add "$ TestMeasure", tbl, "1+1", Mdl.ModelFormatWholeNumber(1)


    Wednesday, July 13, 2016 1:48 PM
  • How u use this method? This no "ModelMeasures" property at "Model" object!

    https://msdn.microsoft.com/en-us/library/office/dn133245.aspx

    Thursday, November 3, 2016 2:44 PM