Change data label format in PowerPoint Charts

Question

I have a PPT-Presentation with a number of charts. Each chart has data labels. These data labels are formated ##.##. I am trying to change it to ##.0 via VBA. Here is what I do:

Sub Format_Datalabels()

    Dim Layout As CustomLayout
    Dim Slide As Slide
    Dim Shape As Shape
    
    
    For Each Slide In ActivePresentation.Slides
        For Each Shape In Slide.Shapes
            With Shape
                If .HasChart Then
                    With .Chart
                        For Each Point In .SeriesCollection(1)
                        .DataLabels.NumberFormat = "##.0"
                    End With
                End If
            End With
        Next

End Sub

I guess, I am not actually getting a hold of the actual data labels. I am thrown the error "Method or data object not found". Any idea how I can actually format all data labels of all charts in a given presentation?

Answer 1

NumberFormat is poorly documented. In this context, 0 is not treated as a literal but as a special character. If there is a number in the first decimal position, then that number is displayed. If there is no number, then 0 will display, so 4 becomes 4.0, but 4.1 stays 4.1.

Normally in math, if every decimal number is 0, you don't display it at all. 4 is better than 4.0. But if you need to replace all first position decimals with 0, here's how:

.DataLabels.NumberFormat = "##"".0"""

Here, because .0 is in double double quotes, it's treated as a literal. I haven't tried debugging your code. Here's a macro that is tested as working:

Sub ChangeDataLabelNumberFormat()
    With ActivePresentation.Slides(1).Shapes(1)
        If .HasChart Then
            With .Chart.SeriesCollection(1)
                .HasDataLabels = True
                .DataLabels.NumberFormat = "##"".0""" 'Displays all numbers as XX.0
            End With
        End If
    End With
End Sub