使用VBA的Excel图表的主题图表样式-Java 学习之路

我正在尝试为仪表板中的Excel图表构建一些自定义主题 . 在记录宏以查看它们是如何实现时，宏记录了以下代码;

ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 268

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

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

EDIT:

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

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

View the workbook here

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

月轮播放1月42日2月53日3月77 4月124 5月198 6月288 7月312 8月303 9月264 10月149 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

1 回答

进一步评论MSDN：

您可以使用1到48之间的数字来设置图表样式 .

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

创建一个Excel工作簿看起来像这样 - 注意我已经添加了一个图表，所以 ws.ChartObjects(1) 可以引用一些东西：
enter image description here

运行此代码 - 您可以使用 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

回复于 2024-05-02T15:44:12+08:00

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

1 回答

相关问题