PowerPivot VBA to enumerate measure format info

[macfuller]

What is the best way to enumerate the measure formats in a data model? Before I develop some truly awful VBA I want to check with this forum.

Enumeration of the measure info itself is pretty straightforward...

Dim objMs As ModelMeasures
Dim objM As ModelMeasure
Dim objMName As ModelMeasureNames
Dim objMTable As ModelTable
Dim objMFmtBool As ModelFormatBoolean
Dim objMFmtCur As ModelFormatCurrency
Dim objMFmtDate As ModelFormatDate
Dim objMFmtDec As ModelFormatDecimalNumber
Dim objMFmtGen As ModelFormatGeneral
Dim objMFmtPct As ModelFormatPercentageNumber
Dim objMFmtSci As ModelFormatScientificNumber
Dim objMFmtInt As ModelFormatWholeNumber
Dim i%

Set objMs = ActiveWorkbook.Model.ModelMeasures

For Each objM In objMs
Debug.Print objM.name
Debug.Print objM.Formula
Debug.Print objM.Description
Set objMTable = objM.AssociatedTable
Debug.Print objMTable.name
Next objM

If you already know the format you can set the object and get related info, as in

Set objMFmtCur = objM.FormatInformation
Debug.Print objMFmtCur.DecimalPlaces

But of course you'll get an error if you try to set the .FormatInformation to the wrong type of object such as

Set objMFmtBool = objM.FormatInformation

So rather than writing a convoluted error trapping mechanism to test each ModelFormat object, am I missing something simple to tell me the correct format type? Then I can use a Select Case to return the proper format properties.

 

[macfuller]

Here's what I came up with - a bit Rube Goldberg but I can get the formatting. With 140+ measures I can now do batch updates to formats and descriptions, not to mention running the formulas through www.daxformatter.com, and not have to wait 3 minutes for each one.

    Dim objMs As ModelMeasures
    Dim objM As ModelMeasure
    Dim objMName As ModelMeasureNames
    Dim objMTable As ModelTable
    Dim objMFmt As Object
   
    Dim strFormat As String, strSymbol As String, strDec As String, strDateString As String
    Dim iDec As Integer
    Dim bIsNum As Boolean, bThouSep As Boolean
    Dim i%
   
    Set objMs = ActiveWorkbook.Model.ModelMeasures
    i% = 1
   
    
    For Each objM In objMs
        i% = i% + 1
        bIsNum = False
        Set objMTable = objM.AssociatedTable
        Cells(i%, 1).Value = [URL="https://urldefense.proofpoint.com/v2/url?u=http-3A__objMTable.name&d=DwQFAg&c=aLnS6P8Ng0zSNhCF04OWImQ_He2L69sNWG3PbxeyieE&r=DDNp5ZBVbld05NKwgBkoImuoOJGgaI5Pv4JtcOd4DEk&m=WH2cQpR7e-5thfCGWFI1wp-2jGhKGZYCecRSpngURBw&s=8ol6q83oMm70GoG8VRlmijYI9mulVjnGlC3W43ud4Uo&e="]objMTable.name[/URL]
        Cells(i%, 2) = [URL="https://urldefense.proofpoint.com/v2/url?u=http-3A__objM.name&d=DwQFAg&c=aLnS6P8Ng0zSNhCF04OWImQ_He2L69sNWG3PbxeyieE&r=DDNp5ZBVbld05NKwgBkoImuoOJGgaI5Pv4JtcOd4DEk&m=WH2cQpR7e-5thfCGWFI1wp-2jGhKGZYCecRSpngURBw&s=4vIdS_ciQ3aDInxp1Uwf_ow_qehvWDpg1hpMQGecRIQ&e="]objM.name[/URL]
        Cells(i%, 3).Value = objM.Formula
        Cells(i%, 4).Value = objM.Description
        Cells(i%, 5).Value = objMTable.SourceName
 
        Set objMFmt = objM.FormatInformation
       
        Select Case TypeName(objMFmt)
            Case "ModelFormatBoolean"
                strFormat$ = "ModelFormatBoolean"
            Case "ModelFormatCurrency"
                strFormat$ = "ModelFormatCurrency"
                strDec$ = CStr(objMFmt.DecimalPlaces)
'                bThouSep = objMFmt.UseThousandSeparator
                strSymbol$ = objMFmt.Symbol
            Case "ModelFormatDate"
                strFormat$ = "ModelFormatDate"
                strDateString$ = objMFmt.FormatString
            Case "ModelFormatDecimalNumber"
                strFormat$ = "ModelFormatDecimalNumber"
                strDec$ = CStr(objMFmt.DecimalPlaces)
                bThouSep = objMFmt.UseThousandSeparator
                bIsNum = True
            Case "ModelFormatGeneral"
                strFormat$ = "ModelFormatGeneral"
            Case "ModelFormatPercentageNumber"
                strFormat$ = "ModelFormatPercentageNumber"
                strDec$ = CStr(objMFmt.DecimalPlaces)
            Case "ModelFormatScientificNumber"
                strFormat$ = "ModelFormatScientificNumber"
                strDec$ = CStr(objMFmt.DecimalPlaces)
                bThouSep = objMFmt.UseThousandSeparator
                bIsNum = True
            Case "ModelFormatWholeNumber"
                strFormat$ = "ModelFormatWholeNumber"
                bThouSep = objMFmt.UseThousandSeparator
                bIsNum = True
            Case Else
                strFormat$ = "not defined"
        End Select
        Cells(i%, 6).Value = strFormat$
        Cells(i%, 7).Value = strDec$
        Cells(i%, 8).Value = strSymbol$
        If bIsNum Then
            Cells(i%, 9).Value = IIf(bThouSep, "Yes", "No")
        End If
        Cells(i%, 10).Value = strDateString$
        strDec$ = ""
        strSymbol$ = ""
        strDateString$ = ""
    Next objM