In the previous article describing a similar macro for PowerPoint
Joy asked for the same, but for Excel; so, here it goes:
First, a helper method that handles the hiding of data labels for a given chart.
valueThreshold is the limit below which data labels will be hidden.
The first loop loops through all series of a chart; the second loop - through all points of a single series.
If it finds a point that has a value below the threshold - it hides its data label (by setting HasDataLabel to False).
(This code is exactly the same as the respective method in the PowerPoint macro.)
Sub RemoveSmallValuesFromChart(chart)
Const valueThreshold As Double = 0.05
For Each series In chart.SeriesCollection
If series.HasDataLabels Then
Dim pointCount As Integer
Dim pointValues As Variant
pointCount = series.Points.Count
pointValues = series.Values
For pointIndex = 1 To pointCount
If pointValues(pointIndex) < valueThreshold Then
series.Points(pointIndex).HasDataLabel = False
End If
Next pointIndex
End If
Next series
End Sub
The next method loops through all worksheets and all charts on these worksheet;
if it finds a chart, it calls the first method to clean up the chart.
The first part handles charts embedded in standard data worksheets, the second part - charts that are on separate tabs.
Sub RemoveSmallValuesFromAllCharts()
' Loop over embedded charts:
For Each worksheet In Worksheets
If Not (worksheet.ChartObjects Is Nothing) Then
For Each chartObject In worksheet.ChartObjects
RemoveSmallValuesFromChart chartObject.Chart
Next chartObject
End If
Next worksheet
' Loop over chart sheets:
For Each chart In Charts
RemoveSmallValuesFromChart chart
Next chart
End Sub
Finally, we have the method to clean up the chart selected by the user (a chart will be selected when any part of the chart is clicked).
Sub RemoveSmallValuesFromSelectedChart()
Set chart = ActiveChart
If chart Is Nothing Then
MsgBox "Please select a chart first."
Else
RemoveSmallValuesFromChart chart
End If
End Sub
HTH
Top
|
Texts
