vba - 使用 VBA 的 Excel 图表的主题图表样式

Question

我正在尝试为仪表板中的 Excel 图表构建一些自定义主题。录制一个宏来看看它们是如何实现的，宏录制了以下代码；

ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 268

我通过谷歌进行了高低搜索，找到了这些图表样式的列表，或者任何有关如何自定义它们的文档。每次搜索都会返回图表类型常量的链接，即 xlLine、xlPie 等。而不是 Excel 功能区中“图表工具-设计”选项卡上提供的主题图表。

如果有人能指出我正确的方向，我将不胜感激。

编辑:

这些图表样式常量的可用文档很少甚至没有，因此我创建了一个示例工作簿，其中所有图表样式类型都显示为饼图。您可以在这里找到它。至少在选择类型之前您可以直观地看到图表。

可以在此处查看工作簿，如果有人知道如何在帖子中添加可下载版本，请评论

View the workbook here

您可以使用以下代码自行构建它，只需添加一个名为 ChartStyles 的工作表并创建一个名为 GolfRoundsPlayed 的数据表并使用此数据

已进行的月份回合1月42日2 月 53 日77 年 3 月124 年 4 月198 年 5 月288年6月312 年 7 月303 年 8 月264 年 9 月149 年 10 月11 月 54 日12 月 33 日

Sub BuildChartStyleSheet()
    Dim targetChart As Chart
    Dim targetSheet As Worksheet
    Dim top As Long
    Dim x As Integer, chtTitle As String
    top = 15

    Dim dataRange As Range
    Set dataRange = Range("GolfRoundsPlayed")
    Set targetSheet = Sheets("ChartStyles")

    Application.ScreenUpdating = False
    For x = 1 To 353
        If x > 1 Then top = top + 128
        On Error Resume Next
        Set targetChart = targetSheet.Shapes.AddChart2(x, xlPie, 2, top, 230, 125).Chart

        chtTitle = "ChartStyle for ChartStyle #" & x
        With targetChart
            .SetSourceData Source:=dataRange
            .chartTitle.Text = chtTitle
            .chartTitle.Format.TextFrame2.TextRange.Font.Size = 11
        End With
    Next x
   Application.ScreenUpdating = True
End Sub

Answer 1

进一步评论MSDN那:

You can use a number from 1 to 48 to set the chart style.

我的测试显示范围 201 到 352 也是有效的。这适用于所有图表类型。

创建一个 Excel 工作簿如下所示 - 请注意，我已经添加了一个图表，因此 ws.ChartObjects(1) 可以引用某些内容:

运行此代码 - 您可以使用 Stop 来更详细地查看发生的情况。

Option Explicit

Sub UnderstandChartStyle()

    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim varTypes As Variant
    Dim i As Integer, j As Integer

    varTypes = GetChartTypes
    Set ws = ThisWorkbook.Worksheets(1)
    Set cht = ws.ChartObjects(1)

    For j = LBound(varTypes) To UBound(varTypes)
        cht.Chart.ChartType = varTypes(j)
        For i = 1 To 1000
            On Error Resume Next
            cht.Chart.ChartStyle = i
            If Err.Number = 0 Then
                Debug.Print "Chart type: " & varTypes(j) & "; Chart style: " & i & "; Sum: " & varTypes(j) + i
            Else
                Debug.Print "Chart style error: " & i
            End If
            Stop
        Next i
        Stop
    Next j

End Sub

Function GetChartTypes() As Variant

    Dim i As Integer
    Dim varTypes(1 To 73) As Integer

    varTypes(1) = -4169
    varTypes(2) = -4151
    varTypes(3) = -4120
    varTypes(4) = -4102
    varTypes(5) = -4101
    varTypes(6) = -4100
    varTypes(7) = -4098
    varTypes(8) = 1
    varTypes(9) = 4
    varTypes(10) = 5
    varTypes(11) = 15

    For i = 12 To 73
        varTypes(i) = i + 39
    Next i

    GetChartTypes = varTypes

End Function

GetChartTypes 的代码基于此表:

| GROUPING      | CHART TYPE                                        | VALUE     | VBA CONSTANT                  ||-------------  |-----------------------------------------------    |-------    |----------------------------   || 3DAREA        | 3D AREA                                           | -4098     | xl3DArea                      || 3DAREA        | 3D STACKED AREA                                   | 78        | xl3DAreaStacked               || 3DAREA        | 3D 100% STACKED AREA                              | 79        | xl3DAreaStacked100            || 3DBAR         | 3D CLUSTERED BAR                                  | 60        | xl3DBarClustered              || 3DBAR         | 3D STACKED BAR                                    | 61        | xl3DBarStacked                || 3DBAR         | 3D 100% STACKED BAR                               | 62        | xl3DBarStacked100             || 3DCOLUMN      | 3D CLUSTERED COLUMN                               | 54        | xl3DColumnClustered           || 3DCOLUMN      | 3D COLUMN                                         | -4100     | xl3DColumn                    || 3DCOLUMN      | 3D CONE COLUMN                                    | 105       | xlConeCol                     || 3DCOLUMN      | 3D CYLINDER COLUMN                                | 98        | xlCylinderCol                 || 3DCOLUMN      | 3D PYRAMID COLUMN                                 | 112       | xlPyramidCol                  || 3DCOLUMN      | 3D STACKED COLUMN                                 | 55        | xl3DColumnStacked             || 3DCOLUMN      | 3D 100% STACKED COLUMN                            | 56        | xl3DColumnStacked100          || AREA          | AREA                                              | 1         | xlArea                        || AREA          | STACKED AREA                                      | 76        | xlAreaStacked                 || AREA          | 100% STACKED AREA                                 | 77        | xlAreaStacked100              || BAR           | CLUSTERED BAR                                     | 57        | xlBarClustered                || BAR           | STACKED BAR                                       | 58        | xlBarStacked                  || BAR           | 100% STACKED BAR                                  | 59        | xlBarStacked100               || BUBBLE        | 3D BUBBLE, BUBBLE WITH 3D EFFECTS                 | 87        | xlBubble3DEffect              || BUBBLE        | BUBBLE                                            | 15        | xlBubble                      || COLUMN        | CLUSTERED COLUMN                                  | 51        | xlColumnClustered             || COLUMN        | STACKED COLUMN                                    | 52        | xlColumnStacked               || COLUMN        | 100% STACKED COLUMN                               | 53        | xlColumnStacked100            || CONE          | CLUSTERED CONE COLUMN                             | 99        | xlConeColClustered            || CONE          | STACKED CONE COLUMN                               | 100       | xlConeColStacked              || CONE          | 100% STACKED CONE COLUMN                          | 101       | xlConeColStacked100           || CONEBAR       | CLUSTERED CONE BAR                                | 102       | xlConeBarClustered            || CONEBAR       | STACKED CONE BAR                                  | 103       | xlConeBarStacked              || CONEBAR       | 100% STACKED CONE BAR                             | 104       | xlConeBarStacked100           || CYLINDER      | CLUSTERED CYLINDER COLUMN                         | 92        | xlCylinderColClustered        || CYLINDER      | STACKED CYLINDER COLUMN                           | 93        | xlCylinderColStacked          || CYLINDER      | 100% STACKED CYLINDER COLUMN                      | 94        | xlCylinderColStacked100       || CYLINDERBAR   | CLUSTERED CYLINDER BAR                            | 95        | xlCylinderBarClustered        || CYLINDERBAR   | STACKED CYLINDER BAR                              | 96        | xlCylinderBarStacked          || CYLINDERBAR   | 100% STACKED CYLINDER BAR                         | 97        | xlCylinderBarStacked100       || DOUGHNUT      | DOUGHNUT                                          | -4120     | xlDoughnut                    || DOUGHNUT      | EXPLODED DOUGHNUT                                 | 80        | xlDoughnutExploded            || LINE          | 3D LINE                                           | -4101     | xl3DLine                      || LINE          | LINE                                              | 4         | xlLine                        || LINE          | LINE WITH MARKERS                                 | 65        | xlLineMarkers                 || LINE          | STACKED LINE                                      | 63        | xlLineStacked                 || LINE          | 100% STACKED LINE                                 | 64        | xlLineStacked100              || LINE          | STACKED LINE WITH MARKERS                         | 66        | xlLineMarkersStacked          || LINE          | 100% STACKED LINE WITH MARKERS                    | 67        | xlLineMarkersStacked100       || PIE           | 3D PIE                                            | -4102     | xl3DPie                       || PIE           | 3D EXPLODED PIE                                   | 70        | xl3DPieExploded               || PIE           | BAR OF PIE                                        | 71        | xlBarOfPie                    || PIE           | EXPLODED PIE                                      | 69        | xlPieExploded                 || PIE           | PIE                                               | 5         | xlPie                         || PIE           | PIE OF PIE                                        | 68        | xlPieOfPie                    || PYRAMID       | CLUSTERED PYRAMID BAR                             | 109       | xlPyramidBarClustered         || PYRAMID       | STACKED PYRAMID BAR                               | 110       | xlPyramidBarStacked           || PYRAMID       | 100% STACKED PYRAMID BAR                          | 111       | xlPyramidBarStacked100        || PYRAMID       | CLUSTERED PYRAMID COLUMN                          | 106       | xlPyramidColClustered         || PYRAMID       | STACKED PYRAMID COLUMN                            | 107       | xlPyramidColStacked           || PYRAMID       | 100% STACKED PYRAMID COLUMN                       | 108       | xlPyramidColStacked100        || RADAR         | RADAR                                             | -4151     | xlRadar                       || RADAR         | FILLED RADAR                                      | 82        | xlRadarFilled                 || RADAR         | RADAR WITH DATA MARKERS                           | 81        | xlRadarMarkers                || SCATTER       | SCATTER                                           | -4169     | xlXYScatter                   || SCATTER       | SCATTER WITH LINES                                | 74        | xlXYScatterLines              || SCATTER       | SCATTER WITH LINES AND NO DATA MARKERS            | 75        | xlXYScatterLinesNoMarkers     || SCATTER       | SCATTER WITH SMOOTH LINES                         | 72        | xlXYScatterSmooth             || SCATTER       | SCATTER WITH SMOOTH LINES AND NO DATA MARKERS     | 73        | xlXYScatterSmoothNoMarkers    || STOCK         | STOCK HLC (HIGH-LOW-CLOSE)                        | 88        | xlStockHLC                    || STOCK         | STOCK OHLC (OPEN-HIGH-LOW-CLOSE)                  | 89        | xlStockOHLC                   || STOCK         | STOCK VHLC (VOLUME-HIGH-LOW-CLOSE)                | 90        | xlStockVHLC                   || STOCK         | STOCK VOHLC (VOLUME-OPEN-HIGH-LOW-CLOSE)          | 91        | xlStockVOHLC                  || SURFACE       | 3D SURFACE                                        | 83        | xlSurface                     || SURFACE       | 3D SURFACE WIREFRAME                              | 84        | xlSurfaceWireframe            || SURFACE       | SURFACE TOP VIEW                                  | 85        | xlSurfaceTopView              || SURFACE       | SURFACE TOP VIEW WIREFRAME                        | 86        | xlSurfaceTopViewWireframe     |

HTH