2021-03-25

---

前言

对excel中的已有数据使用VBA自动作图

---

VBA自动绘图()

画jc柱状图&ror折线图

建立模块

i为最后一行有数字的行数，确定数据范围。

Sub 自动绘图()
Dim i%
i = [A2].End(xlDown).Row

添加图表

代码如下（示例）：

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select 

新建系列

新建系列
规定系列名称、系列值范围、系列坐标范围

    ActiveChart.SeriesCollection.NewSeries '新建系列
    ActiveChart.FullSeriesCollection(1).Name = "=Sheet1!$D$1"
    ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$D$2:$D$" & i
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$A$2:$A$" & i
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.FullSeriesCollection(2).Name = "=Sheet1!$E$1"
    ActiveChart.FullSeriesCollection(2).Values = "=Sheet1!$E$2:$E$" & i
    ActiveChart.FullSeriesCollection(2).XValues = "=Sheet1!$A$2:$A$" & i`

整理

选择图表类型、是否使用次坐标轴。

#ActiveWindow.SmallScroll Down:=-36 '窗口向上滚动36格
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(1).AxisGroup = 1
    ActiveChart.FullSeriesCollection(2).ChartType = xlLine
    ActiveChart.FullSeriesCollection(2).AxisGroup = 2
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.ChartGroups(1).GapWidth = 70
    ActiveChart.ChartGroups(1).Overlap = 0
    ActiveChart.ChartTitle.Select
    Selection.Delete
    'ActiveChart.Axes(xlValue).MajorGridlines.Select
    'Selection.Delete`

---

总结

简单介绍了VBA画图