使用VBA格式化图表数据标签

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，无济于事。解决这个问题的任何帮助都是非常令人沮丧的！

1 个解决方案

#1

You can climb up to a series source range via its Formula property.

您可以通过其公式属性爬升到一系列源范围。

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

#1