使用VBA的Excel图表的主题图表样式 [英] Themed Chart Styles for Excel charts using VBA
本文介绍了使用VBA的Excel图表的主题图表样式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试为仪表板中的excel图表构建一些自定义主题。在记录宏以了解如何实现这些宏之后，该宏记录了以下代码；
ActiveChart.ClearToMatchStyle
ActiveChart .ChartStyle = 268
我已经通过Google搜索了上下限以找到这些图表样式的列表，或者有关如何自定义它们的任何文档。每次搜索都会返回图表类型常量的链接，即xlLine，xlPie等。Excel功能区中图表工具-设计选项卡上没有可用的主题图表。
可以为我指出正确的方向。
编辑：
关于这些图表样式常量的文档很少，甚至没有可用的文档，因此我创建了一个示例工作簿，其中所有图表样式类型都显示为饼图。在这里为您提供。
如果有人知道如何在列表中添加可下载的版本，则可以在此处查看工作簿。
请发表评论
运行此代码-您可以使用停止，以查看发生的详细情况。
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）
对于j = LBound（varTypes）到UBound（varTypes）
cht.Chart.ChartType = varTypes（j）
对于i = 1到1000
错误恢复下一个
cht.Ch art.ChartStyle = i
如果Err.Number = 0，则
Debug.Print图表类型：& varTypes（j）& ;图表样式：&我和;总和：& varTypes（j）+ i
其他
Debug.Print图表样式错误：& i
End if
Stop
Next i
Stop
Next j
End Sub
Function GetChartTypes（ ）作为变量
Dim i作为整数
Dim varTypes（1至73）作为整数
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
对于i = 12至73
varTypes（i）= i + 39
接下来的i
GetChartTypes = varTypes
结束函数
GetChartTypes 的代码基于从此表：
|分组|图表类型| VALUE | VBA常数|
| ------------- | ------------------------------- ---------------- | ------- | ------------------------- --- |
| 3DAREA | 3D区域| -4098 | xl3DArea |
| 3DAREA | 3D堆叠区域| 78 | xl3DAreaStacked |
| 3DAREA | 3D 100％堆叠区域| 79 | xl3DAreaStacked100 |
| 3DBAR | 3D集群酒吧| 60 | xl3DBarClustered |
| 3DBAR | 3D STACKED BAR | 61 | xl3DBarStacked |
| 3DBAR | 3D 100％叠条| 62 | xl3DBarStacked100 |
| 3DCOLUMN | 3D群集列| 54 | xl3DColumnClustered |
| 3DCOLUMN | 3D栏| -4100 | xl3DColumn |
| 3DCOLUMN | 3D圆锥柱| 105 | xlConeCol |
| 3DCOLUMN | 3D圆柱柱| 98 | xlCylinderCol |
| 3DCOLUMN | 3D金字塔柱| 112 | xlPyramidCol |
| 3DCOLUMN | 3D堆叠柱| 55 | xl3DColumnStacked |
| 3DCOLUMN | 3D 100％堆叠柱| 56 | xl3DColumnStacked100 |
|面积|面积| 1 | xlArea |
|面积|堆积面积| 76 | xlAreaStacked |
|面积| 100％堆积面积| 77 | xlAreaStacked100 |
|酒吧|集群酒吧| 57 | xlBarClustered |
|酒吧|堆叠的酒吧| 58 | xlBarStacked |
|酒吧| 100％堆叠的酒吧| 59 | xlBarStacked100 |
|泡泡| 3D气泡，带3D效果的气泡| 87 | xlBubble3DEffect |
|泡泡|泡泡| 15 | xlBubble |
|栏|丛集栏| 51 | xlColumnClustered |
|栏|堆叠柱| 52 | xlColumnStacked |
|栏| 100％叠层柱| 53 | xlColumnStacked100 |
|锥体群集圆锥柱| 99 | xlConeColClustered |
|锥体叠锥柱| 100 | xlConeColStacked |
|锥体100％叠锥塔| 101 | xlConeColStacked100 |
| CONEBAR |簇状锥| 102 | xlConeBarClustered |
| CONEBAR |叠锥棒| 103 | xlConeBarStacked |
| CONEBAR | 100％叠锥形棒| 104 | xlConeBarStacked100 |
|气缸|集群圆柱列| 92 | xlCylinderColClustered |
|气缸|叠缸柱| 93 | xlCylinderColStacked |
|气缸| 100％叠层圆柱柱| 94 | xlCylinderColStacked100 |
|圆柱|簇状圆柱棒| 95 | xlCylinderBarClustered |
|圆柱|叠缸杆| 96 | xlCylinderBarStacked |
|圆柱| 100％叠层圆柱棒| 97 | xlCylinderBarStacked100 |
|甜甜圈|甜甜圈| -4120 | xlDoughnut |
|甜甜圈|炸面包圈| 80 | xlDoughnutExploded |
| LINE | 3D线| -4101 | xl3DLine |
| LINE | LINE | 4 | xlLine |
| LINE |标记线| 65 | xlLineMarkers |
| LINE |叠线| 63 | xlLineStacked |
| LINE | 100％叠线| 64 | xlLineStacked100 |
| LINE |带标记的叠线| 66 | xlLineMarkersStacked |
| LINE | 100％带标记的叠线| 67 | xlLineMarkersStacked100 |
| PIE | 3D PIE | -4102 | xl3DPie |
| PIE | 3D爆炸饼| 70 | xl3DPieExploded |
| PIE |派| 71 | xlBarOfPie |
| PIE |爆炸饼| 69 | xlPieExploded |
| PIE | PIE | 5 | xlPie |
| PIE | PIE OF PIE | 68 | xlPieOfPie |
|金字塔|簇状金字塔| 109 | xlPyramidBarClustered |
|金字塔|金字塔棒| 110 | xlPyramidBarStacked |
|金字塔| 100％堆砌的金字塔棒| 111 | xlPyramidBarStacked100 |
|金字塔|丛集的金字塔栏| 106 | xlPyramidColClustered |
|金字塔|金字塔堆积层| 107 | xlPyramidColStacked |
|金字塔| 100％堆叠金字塔形柱| 108 | xlPyramidColStacked100 |
|雷达|雷达| -4151 | xlRadar |
|雷达|填充雷达| 82 | xlRadarFilled |
|雷达|带有数据标记的雷达| 81 | xlRadarMarkers |
|散射|散射| -4169 | xlXYScatter |
|散射|线条散射| 74 | xlXYScatterLines |
|散射|带有行且没有数据标记的散射器| 75 | xlXYScatterLinesNoMarkers |
|散射|线条流畅的散布| 72 | xlXYScatterSmooth |
|散射|平滑线且无数据标记的散射器| 73 | xlXYScatterSmoothNoMarkers |
|库存股票HLC（高低关）| 88 | xlStockHLC |
|库存股票OHLC（开-高-低-闭）| 89 | xlStockOHLC |
|库存股票VHLC（高低低收盘价）| 90 | xlStockVHLC |
|库存库存量（音量-开-高-低-闭）| 91 | xlStockVOHLC |
|表面| 3D表面| 83 | xlSurface |
|表面| 3D表面线| 84 | xlSurfaceWireframe |
|表面|表面顶视图| 85 | xlSurfaceTopView |
|表面|表面顶视图线框| 86 | xlSurfaceTopViewWireframe |
HTH
I am trying to build some custom themes for excel charts in a dashboard. Upon recording a macro to see how these are implemented, the macro recorded the following code;
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 268
I have searched high and low via google to find a list of these chartstyles, or any documentation on how to customize them. Every search returns links for the chart type constants, i.e. xlLine, xlPie etc. NOT the themed charts available on the Chart Tools-Design tab in the Excel ribbon.
If anyone can point me in the right direction it would be much appreciated.
EDIT:
There is minimal to no documentation available for these Chart Style constants, so I created a sample workbook with all of the Chart Style types displayed as pie charts. It is available for you here. at least you will have a visual representation of the chart prior to choosing the type.
The workbook can be viewed here, if anyone knows how to add a downloadable version in the post please comment
You can build it yourself with the following code, just add a sheet named ChartStyles and create a data table named GolfRoundsPlayed and use this data
Month Rounds Played Jan 42 Feb 53 Mar 77 Apr 124 May 198 Jun 288 Jul 312 Aug 303 Sep 264 Oct 149 Nov 54 Dec 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
解决方案
Further to comment on MSDN that:
You can use a number from 1 to 48 to set the chart style.
My test shows that range 201 to 352 is valid as well. This is across all chart types.
Create an Excel workbook looks like this - note I already added a chart so ws.ChartObjects(1) can reference something:
The run this code - you can play with the Stops to see what is happening in more detail.
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
The code for GetChartTypes is based from this table:
| 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
这篇关于使用VBA的Excel图表的主题图表样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
