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

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/80888782
去年开发了一个小程序，可以一键生成所有类型的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