效果:
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
![](http://pubimage.360doc.com/wz/default.gif)
代码:
- 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