使用 VBA 格式化图表数据标签
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40261969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Formatting chart data labels with VBA
提问by Gabriel Menezes
I am developing a dashboard that will have lots of charts, and as the data shown on these charts change, so will the format of the numbers. At the point I am right now, I've run into a problem trying to retrieve the intended format code from the spreadsheet where the data is based on, in the midst of looping through all the series in the chart. Here's the code so far:
我正在开发一个包含大量图表的仪表板，随着这些图表上显示的数据发生变化，数字的格式也会发生变化。就我现在而言，在循环遍历图表中的所有系列的过程中，我遇到了一个问题，试图从数据所基于的电子表格中检索预期的格式代码。这是到目前为止的代码：
Sub FixLabels(whichchart As String)
Dim cht As Chart
Dim i, z As Variant
Dim seriesname, seriesfmt As String
Dim seriesrng As Range
Set cht = Sheets("Dashboard").ChartObjects(whichchart).Chart
For i = 1 To cht.SeriesCollection.Count
If cht.SeriesCollection(i).name = "#N/D" Then
cht.SeriesCollection(i).DataLabels.ShowValue = False
Else
cht.SeriesCollection(i).DataLabels.ShowValue = True
seriesname = cht.SeriesCollection(i).name
Debug.Print seriesname
With this I am able to retrieve the name of de series that do not result in error, and hide the series that do. So far so good. Now on to the formatting: There's a column where all the possible series names for this workbook are stored, and one column to the left, there are my formatting codes, which are "int", for integer numbers, "#,#" for numbers with important decimal cases, and "%" for percent rates. These are stored as plain text. So the last bit of the code would look like:
有了这个，我就可以检索不会导致错误的 de 系列的名称，并隐藏会导致错误的系列。到现在为止还挺好。现在进入格式设置：有一列存储了此工作簿的所有可能的系列名称，左侧的一列是我的格式代码，“int”表示整数，“#,#”表示带有重要小数位的数字，以及百分比率的“%”。这些存储为纯文本。所以代码的最后一点看起来像：
Select Case seriesfmt
Case "int"
Cht.SeriesCollection(i).DataLabels.NumberFormat = "#"
Case "#,#"
Cht.SeriesCollection(i).DataLabels.NumberFormat = "#,###"
Case "%"
Cht.SeriesCollection(i).DataLabels.NumberFormat = "#.0%"
End Select
End If
Next i
Finally the real problem here: what goes in between. I cannot retrieve the series format! My best guess was:
最后真正的问题是：介于两者之间。我无法检索系列格式！我最好的猜测是：
With Sheets("CxC").Range("K22:K180")
seriesfmt = .Find(seriesname).Offset(0, -1).Value
End With
I got errors, telling me the With block was not defined. I tried several combinations of the same command, with or without the With method, with and without the Set method, I tried WorksheetFunction Match, to no avail. Any help solving this issue is greatly apreciated!
我收到错误，告诉我未定义 With 块。我尝试了相同命令的几种组合，有或没有 With 方法，有和没有 Set 方法，我尝试了 WorksheetFunction Match，但无济于事。非常感谢任何解决此问题的帮助！
采纳答案by user3598756
You can climb up to a series source range via its Formulaproperty.
您可以通过其Formula属性爬升到系列源范围。
Since it has the format:
由于它具有以下格式：
=SERIES(,,sheetname!sheetRange,)
then you're interested in its "3rd element", if you split it into an array with "," as delimiter
那么你对它的“第三个元素”感兴趣，如果你把它分成一个用“，”作为分隔符的数组
so you can code:
所以你可以编码：
Sub FixLabels(whichchart As String)
Dim cht As Chart
Dim i As Long
With Sheets("Dashboard").ChartObjects(whichchart).Chart '<--| reference your chart
For i = 1 To .SeriesCollection.Count '<--| loop through all series
With .SeriesCollection(i) '<--| reference current series
If .Name = "#N/D" Then
.DataLabels.ShowValue = False
Else
.HasDataLabels = True '<--| be sure labels are "activated"
.DataLabels.ShowValue = True '<--| show data labels
.DataLabels.NumberFormat = GetFormat(Split(.Formula, ",")(2)) '<-- set data label format
End If
End With
Next i
End With
End Sub
Function GetFormat(dataSource As Variant) As String
With Range(dataSource).Cells(1, 1) '<-- reference the first cell of the data source
Select Case True
Case InStr(.Text, "%") > 0
GetFormat = "#.0%"
Case Int(CDbl(.Text)) = CDbl(.Text)
GetFormat = "#"
Case Else
GetFormat = "#,###"
End Select
End With
End Function
