Checking values on a pie chart, deleting if 0%-CodePudding

I have to print out a few thousand pie charts. I don't want to show 0% and was trying to make a macro that would check for that value and then delete if it was 0%. I used Record Macro to get the following:

    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Points(1).DataLabel.Select    
    Selection.Delete

So I tried to add an if statement like this:

    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Points(1).DataLabel.Select
    If Selection.Value = 0 Then
    Selection.Delete
    End If

But it doesn't work. Any suggestions on how to check the value?

CodePudding user response：

May this can help you:

Set cht = ActiveSheet.ChartObjects(1).Chart
Set a = cht.SeriesCollection(1)

Valueschart = a.Values
    
For x = LBound(Valueschart) To UBound(Valueschart)
    If Valueschart(x) = 0 Then
        With a.Points(x)
        ActiveSheet.ChartObjects(1).Delete
        End With
    End If
Next x

CodePudding user response：

Your code fails because a DataLabel has no Value property. It has a Caption-property, so you could check if the caption is 0%, but that's rather dirty and will fail if the data label is formatted differently (eg showing absolute values).

You can check the value itself by reading the Values-property of the data series that is displayed, that's much cleaner.

Furthermore, you should avoid using Select. The macro recorder needs to work on Selection, but as a programmer, you can use the matching objects directly.

I would suggest that you don't delete the labels. Problem with deleting is that once the data is changed and the value is no longer 0, it will be tricky to get it back. The following code is not deleting the labels but just makes them invisible by setting the height to 0. When the values changes and you run the code again, it will get it's original height and is visible again.

The following routine sets the labels for one chart:

Sub HideZeroValueLabels(ch As Chart)
    ' Get the Values of the data series
    Dim vals
    vals = ch.FullSeriesCollection(1).Values
    
    ' First loop: Remember height of labels (for restore)
    Dim i As Long, p As Point, maxPointLableHeight As Double
    For i = 1 To ch.FullSeriesCollection(1).Points.Count
        Set p = ch.FullSeriesCollection(1).Points(i)
        On Error Resume Next
        If p.DataLabel.Height > maxPointLableHeight Then maxPointLableHeight = p.DataLabel.Height
        On Error GoTo 0
    Next
    
    ' Second loop: Set height to 0 if value is 0, this makes it invisible
    For i = 1 To ch.FullSeriesCollection(1).Points.Count
        Set p = ch.FullSeriesCollection(1).Points(i)
        On Error Resume Next
        If vals(i) = 0 Then
            p.DataLabel.Height = 0
        Else
            p.DataLabel.Height = maxPointLableHeight
        End If
        On Error GoTo 0
    Next
End Sub

You can call the routine for every Pie chart of a sheet:

Dim co As ChartObject, ch As Chart
For Each co In activeSheet.ChartObjects
    Set ch = co.Chart
    If ch.ChartType = xl3DPie Or ch.ChartType = xl3DPieExploded _
    Or ch.ChartType = xlPie Or ch.ChartType = xlPieExploded Then
        HideZeroValueLabels ch
    End If
Next