学以致用——一键生成所有Excel图表类型 （Generate all Excel graphs with one click）

一键生成所有类型的Excel图表源码。

效果：

代码：

Public Sub generateAllExcelCharts()
Application.ScreenUpdating = False
'定义用于循环的整型变量
Dim ChartTypeArray() As Variant
Dim ChartCount As Integer
Dim ChartTypeName As String
ChartTypeArray = Array(1, 4, 5, 15, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, _
67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, _
102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, -4169, -4151, -4120, -4102, -4101, -4100, -4098)
ChartCount = 1
Do While (ChartCount <= (UBound(ChartTypeArray) + 1))
'添加图表
Charts.Add
'定义图表类型
ActiveChart.ChartType = ChartTypeArray(ChartCount - 1)
'图表数据源
ActiveChart.SetSourceData Source:=Sheets("AllCharts").Range("B6:AK11"), PlotBy:=xlRows
'设置图表添加的位置
ActiveChart.Location Where:=xlLocationAsObject, Name:="AllCharts"
With ActiveChart
'使图表带有“标题”
.HasTitle = True
'设置图表“标题”
Select Case .ChartType '根据图表类型代码，获取其对应的中文名称
Case 1
ChartTypeName = "面积图"
Case 4
ChartTypeName = "折线图"
Case 5
ChartTypeName = "饼图"
Case 15
ChartTypeName = "气泡图"
Case 51
ChartTypeName = "簇状柱形图"
Case 52
ChartTypeName = "堆积柱形图"
Case 53
ChartTypeName = "百分比堆积柱形图"
Case 54
ChartTypeName = "三维簇状柱形图"
Case 55
ChartTypeName = "三维堆积柱形图"
Case 56
ChartTypeName = "三维百分比堆积柱形图"
Case 57
ChartTypeName = "簇状条形图"
Case 58
ChartTypeName = "堆积条形图"
Case 59
ChartTypeName = "百分比堆积条形图"
Case 60
ChartTypeName = "三维簇状条形图"
Case 61
ChartTypeName = "三维堆积条形图"
Case 62
ChartTypeName = "三维百分比堆积条形图"
Case 63
ChartTypeName = "堆积折线图"
Case 64
ChartTypeName = "百分比堆积折线图"
Case 65
ChartTypeName = "数据点折线图"
Case 66
ChartTypeName = "堆积数据点折线图"
Case 67
ChartTypeName = "百分比堆积数据点折线图"
Case 68
ChartTypeName = "复合饼图"
Case 69
ChartTypeName = "分离型饼图"
Case 70
ChartTypeName = "分离型三维饼图"
Case 71
ChartTypeName = "复合条饼图"
Case 72
ChartTypeName = "平滑线散点图"
Case 73
ChartTypeName = "无数据点平滑线散点图"
Case 74
ChartTypeName = "折线散点图"
Case 75
ChartTypeName = "无数据点折线散点图"
Case 76
ChartTypeName = "堆积面积图"
Case 77
ChartTypeName = "百分比堆积面积图"
Case 78
ChartTypeName = "三维堆积面积图"
Case 79
ChartTypeName = "百分比堆积面积图"
Case 80
ChartTypeName = "分离型圆环图"
Case 81
ChartTypeName = "数据点雷达图"
Case 82
ChartTypeName = "填充雷达图"
Case 83
ChartTypeName = "三维曲面图"
Case 84
ChartTypeName = "三维曲面图（框架图）"
Case 85
ChartTypeName = "曲面图（俯视图）"
Case 86
ChartTypeName = "曲面图（俯视框架图）"
Case 87
ChartTypeName = "三维气泡图"
Case 88
ChartTypeName = "盘高-盘低-收盘图"
Case 89
ChartTypeName = "开盘-盘高-盘低-收盘图"
Case 90
ChartTypeName = "成交量-盘高-盘低-收盘图"
Case 91
ChartTypeName = "成交量-开盘-盘高-盘低-收盘图"
Case 92
ChartTypeName = "簇状柱形圆锥图"
Case 93
ChartTypeName = "堆积柱形圆锥图"
Case 94
ChartTypeName = "百分比堆积柱形圆柱图"
Case 95
ChartTypeName = "簇状条形圆柱图"
Case 96
ChartTypeName = "堆积条形圆柱图"
Case 97
ChartTypeName = "百分比堆积条形圆柱图"
Case 98
ChartTypeName = "三维柱形圆柱图"
Case 99
ChartTypeName = "簇状柱形圆锥图"
Case 100
ChartTypeName = "堆积柱形圆锥图"
Case 101
ChartTypeName = "百分比堆积柱形圆锥图"
Case 102
ChartTypeName = "簇状条形圆锥图"
Case 103
ChartTypeName = "堆积条形圆锥图"
Case 104
ChartTypeName = "百分比堆积条形圆锥图"
Case 105
ChartTypeName = "三维柱形圆锥图"
Case 106
ChartTypeName = "簇状柱形棱锥图"
Case 107
ChartTypeName = "堆积柱形棱锥图"
Case 108
ChartTypeName = "百分比堆积柱形棱锥图"
Case 109
ChartTypeName = "簇状条形棱锥图"
Case 110
ChartTypeName = "堆积条形棱锥图"
Case 111
ChartTypeName = "百分比堆积条形棱锥图"
Case 112
ChartTypeName = "三维柱形棱锥图"
Case -4169
ChartTypeName = "散点图"
Case -4151
ChartTypeName = "雷达图"
Case -4120
ChartTypeName = "圆环图"
Case -4102
ChartTypeName = "三维饼图"
Case -4101
ChartTypeName = "三维折线图"
Case -4100
ChartTypeName = "三维柱形图"
Case -4098
ChartTypeName = "三维面积图"
End Select
.ChartTitle.Characters.Text = "GDP—" & ChartTypeName & "（" & ChartTypeArray(ChartCount - 1) & "）" '设置图表标题
If .FullSeriesCollection.Count = 5 Then '2011年至2015年共5个数据系列，分别命名以提高图表的可读性
.FullSeriesCollection(1).Name = "2015年"
.FullSeriesCollection(2).Name = "2014年"
.FullSeriesCollection(3).Name = "2013年"
.FullSeriesCollection(4).Name = "2012年"
.FullSeriesCollection(5).Name = "2011年"
End If
If .FullSeriesCollection.Count = 3 Then  '气泡图仅显示3个系列的数据
.FullSeriesCollection(1).Name = "2015年"
.FullSeriesCollection(2).Name = "2014年"
.FullSeriesCollection(3).Name = "2013年"
End If
End With
With ActiveChart.Parent
If ((ChartCount Mod 3) <> 0) Then   '每张图的高度为222磅，每输出3张图表后，下一图表左上角向下偏移222磅
.Top = 222 * (Int(ChartCount / 3) + 1)
Else
.Top = 222 * (ChartCount / 3)
End If
If ((ChartCount Mod 3) <> 0) Then   '每张图的高度为222磅，每输出3张图表后，下一图表左上角向右偏移356磅
.Left = 10 + ((ChartCount Mod 3) - 1) * 356
Else
.Left = 10 + 2 * 356
End If
End With
Debug.Print ChartCount & ActiveChart.ChartType & "-" & ChartTypeName & "->"
ChartCount = ChartCount + 1
Loop
Debug.Print "共生成图表" & (ChartCount - 1)
Application.ScreenUpdating = True
End Sub