vba控制图表,excel图表,一键完成

来源http://club.excelhome.net/thread-1417686-1-1.html

官方教程链接

https://docs.microsoft.com/zh-cn/office/vba/api/excel.worksheet.chartobjects

运行宏就显示图表

Sub test()
Dim ch As ChartObject, ws As Worksheet
    Dim lastrow&
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet1")
    If ws.ChartObjects.Count > 0 Then ws.ChartObjects.Delete
    lastrow = ws.Range("a" & Rows.Count).End(xlUp).Row
    Set ch = ws.ChartObjects.Add(ws.[f5].Left, ws.[f5].Top, 360, 215) '
    ch.Name = ws.[b1]
    With ch.Chart
        .ChartType = xlLineMarkers
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Values = ws.Range("b3:b" & lastrow)
        .SeriesCollection(1).XValues = ws.Range("a3:a" & lastrow)
        .SeriesCollection(1).Name = ws.[b2]
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Values = ws.Range("c3:c" & lastrow)
        .SeriesCollection(2).XValues = ws.Range("a3:a" & lastrow)
        .SeriesCollection(2).Name = ws.[c2]
    End With
   With ch.Chart.Axes(xlValue, xlPrimary)
        .CrossesAt = .MinimumScale
        .TickLabels.Font.Size = 8
        .MajorGridlines.Border.ColorIndex = 5
    End With
    With ch.Chart.Axes(xlCategory)
        .TickLabels.Font.Size = 8
    End With
    With ch.Chart
        .HasTitle = True
        .ChartTitle.Text = ch.Name
        .ChartTitle.Font.Size = 18
        .ChartTitle.Left = 137
        .ChartTitle.Top = 2
        .HasLegend = False
        .PlotArea.Width = 347
        .PlotArea.Left = 0
        .PlotArea.Top = 20
        .PlotArea.Height = 181
        .Export ThisWorkbook.Path & "	est.gif"
   End With
   ch.Delete
   Set ch = Nothing
End Sub

Excel图表能直观地呈现数字的规律，是分析数据的利器。本教程是使用VBA程序来绘制Excel嵌入式图表。如果你还不知道什么是图表，没关系，下图就是所谓的图表（嵌入式）：

它的数据来源是如下的表格：

要想使用VBA绘制图表，那首先得了解它的结构。图表对象由图表区、图表标题、绘图区、垂直（值）轴、水平（分类）轴、图例、网格线、系列等区域，每个区域都可以单独设置外观、颜色、填充、字体等等属性，只有熟悉这些区域才能用VBA设计出符合自己需求的图表。下图是对图表对象各区域的分解图，其中次坐标轴只在性质不同的系列之间比较时才可能用到：

绘图区在上图中未指出，请看下图：

我们直接以实例来讲解。以上边提供的中信银行部分数据为例进行演示。

一、创建图表容器
Dim ch As ChartObject, ws As Worksheet
Dim lastrow&
Application.ScreenUpdating = False
Set ws = Worksheets("Risk")
If ws.ChartObjects.Count > 0 Then ws.ChartObjects.Delete
lastrow = ws.Range("a" & Rows.Count).End(xlUp).Row
Set ch = ws.ChartObjects.Add(ws.[f5].Left, ws.[f5].Top, 360, 215) '
ch.Name = ws.[b1]
我们先定义一个ChartObject对象ch，，接着删除Risk工作表中所有的图表（嵌入式图表，下同，以后不再指明），并计算A列最后一个非空行，最后用Add方法新增一个图表。
Add方法的语法为：工作表对象表达式.ChartObjects.Add(Left, Top, Width, Height)，图表绘制在指定的工作表中，数据和图表可以不在同一个工作表。Left和Top表示图表的左上角坐标位置，以磅为单位，该坐标是相对于工作表上单元格 A1的左上角顶点的位置，我们也可以指定指定在某个单元格位置，比如示例的F5单元格。Width和Heigh是图表的宽度和高度，单位也是磅。1磅约=0.03527厘米,1厘米=28.35磅。
新增了图表，我们需要给它起一个名字，名字的值为B1单元格中的“中信银行”。名字不是必须的，你不指定，程序会默认给一个图表1这样的名字。我建议指定名字，这样方便引用，如ws.ChartObjects(“中信银行”)就能引用到这个图表，并可以做进一步操作。
创建了图表，其实是一个空的方框，什么也没有，空空如也，如下图：

其实ChartObject对象只是 Chart对象的容器，ChartObject对象的属性和方法只能控制图表的外观（边框、填充、背景等等）和大小，真正的图表是Chart对象。我们在绘制窗体时，文本框、标签、命令按钮、Listview等控件才是我们需要的，窗体只是一个容器，它的存在只是为了容纳控件，ChartObject对象其实类似窗体的作用。
二、绘制系列线
1.基本系列线
我们以日期为横坐标，市盈率和市净率为两个系列，绘制图表。
With ch.Chart
.ChartType = xlLineMarkers
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = ws.Range("b3:b" & lastrow)
.SeriesCollection(1).XValues = ws.Range("a3:a" & lastrow)
.SeriesCollection(1).Name = "市盈率"
.SeriesCollection(1).AxisGroup = 1
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = ws.Range("c3:c" & lastrow)
.SeriesCollection(2).XValues = ws.Range("a3:a" & lastrow)
.SeriesCollection(2).Name = "市净率"
.SeriesCollection(2).AxisGroup = 2
End With
刚才说过，ChartObject只是一个容器，真正的图表是Chart对象。ChartType = xlLineMarkers指定图表类型为数据点折线图。其值可以为下表中的任何类型，我们可以根据需要选择合适的图表类型：
名称 数值 图表类型描述
xl3DArea -4098 三维面积图。
xl3DAreaStacked 78 三维堆积面积图。
xl3DAreaStacked100 79 百分比堆积面积图。
xl3DBarClustered 60 三维簇状条形图。
xl3DBarStacked 61 三维堆积条形图。
xl3DBarStacked100 62 三维百分比堆积条形图。
xl3DColumn -4100 三维柱形图。
xl3DColumnClustered 54 三维簇状柱形图。
xl3DColumnStacked 55 三维堆积柱形图。
xl3DColumnStacked100 56 三维百分比堆积柱形图。
xl3DLine -4101 三维折线图。
xl3DPie -4102 三维饼图。
xl3DPieExploded 70 分离型三维饼图。
xlArea 1 面积图
xlAreaStacked 76 堆积面积图。
xlAreaStacked100 77 百分比堆积面积图。
xlBarClustered 57 簇状条形图。
xlBarOfPie 71 复合条饼图。
xlBarStacked 58 堆积条形图。
xlBarStacked100 59 百分比堆积条形图。
xlBubble 15 气泡图。
xlBubble3DEffect 87 三维气泡图。
xlColumnClustered 51 簇状柱形图。
xlColumnStacked 52 堆积柱形图。
xlColumnStacked100 53 百分比堆积柱形图。
xlConeBarClustered 102 簇状条形圆锥图。
xlConeBarStacked 103 堆积条形圆锥图。
xlConeBarStacked100 104 百分比堆积条形圆锥图。
xlConeCol 105 三维柱形圆锥图。
xlConeColClustered 99 簇状柱形圆锥图。
xlConeColStacked 100 堆积柱形圆锥图。
xlConeColStacked100 101 百分比堆积柱形圆锥图。
xlCylinderBarClustered 95 簇状条形圆柱图。
xlCylinderBarStacked 96 堆积条形圆柱图。
xlCylinderBarStacked100 97 百分比堆积条形圆柱图。
xlCylinderCol 98 三维柱形圆柱图。
xlCylinderColClustered 92 簇状柱形圆锥图。
xlCylinderColStacked 93 堆积柱形圆锥图。
xlCylinderColStacked100 94 百分比堆积柱形圆柱图。
xlDoughnut -4120 圆环图。
xlDoughnutExploded 80 分离型圆环图。
xlLine 4 折线图。
xlLineMarkers 65 数据点折线图。
xlLineMarkersStacked 66 堆积数据点折线图。
xlLineMarkersStacked100 67 百分比堆积数据点折线图。
xlLineStacked 63 堆积折线图。
xlLineStacked100 64 百分比堆积折线图。
xlPie 5 饼图。
xlPieExploded 69 分离型饼图。
xlPieOfPie 68 复合饼图。
xlPyramidBarClustered 109 簇状条形棱锥图。
xlPyramidBarStacked 110 堆积条形棱锥图。
xlPyramidBarStacked100 111 百分比堆积条形棱锥图。
xlPyramidCol 112 三维柱形棱锥图。
xlPyramidColClustered 106 簇状柱形棱锥图。
xlPyramidColStacked 107 堆积柱形棱锥图。
xlPyramidColStacked100 108 百分比堆积柱形棱锥图。
xlRadar -4151 雷达图。
xlRadarFilled 82 填充雷达图。
xlRadarMarkers 81 数据点雷达图。
xlStockHLC 88 盘高-盘低-收盘图。
xlStockOHLC 89 开盘-盘高-盘低-收盘图。
xlStockVHLC 90 成交量-盘高-盘低-收盘图。
xlStockVOHLC 91 成交量-开盘-盘高-盘低-收盘图。
xlSurface 83 三维曲面图。
xlSurfaceTopView 85 曲面图（俯视图）。
xlSurfaceTopViewWireframe 86 曲面图（俯视线框图）。
xlSurfaceWireframe 84 三维曲面图（线框）。
xlXYScatter -4169 散点图。
xlXYScatterLines 74 折线散点图。
xlXYScatterLinesNoMarkers 75 无数据点折线散点图。
xlXYScatterSmooth 72 平滑线散点图。
xlXYScatterSmoothNoMarkers 73 无数据点平滑线散点图。
接着用SeriesCollection.NewSeries方法绘制系列，X轴是日期范围（Range(“A3:A23”），Y轴是市盈率（Range(“B3:B23”）。如果不指定X轴，系统默认按自然数系列（1,2,3……）产生X轴。
SeriesCollection(1).Name = "市盈率"，该系列的名称为“市盈率”，用Name属性指定。系列的引用序号按添加系列的顺序确定，第一个系列是SeriesCollection(1)，第二个系列是SeriesCollection(2)。
SeriesCollection(1).AxisGroup = 1指定指定系列的组类型。通俗地讲，就是这个系列的Y轴是哪一个，只可以选择主坐标轴组（其值为1）和次坐标轴组（其值为2）。我们这里指定市盈率系列是主坐标轴组。
接着产生第二个系列，名称为“市净率”，属于次坐标轴组。为什么要指定2个坐标轴呢？因为市盈率和市净率是不一样的内容，如果不指定次坐标轴，那么市盈率和市净率共用主坐标轴，组合起来是不是莫名其妙？其效果图如下：

从上图中可见，市净率也使用市盈率的Y轴数值。因为市盈率和市净率数值相差甚大，导致市净率的系列线几乎看不到任何起伏，这显然不是我们想要的效果。我们给市净率单独的一个Y轴，就能解决这个问题。SeriesCollection(2).AxisGroup = 2是让市净率系列用它自己的Y轴（次坐标轴），加了这句话之后，图表变成下图，显然效果杠杠的：

2.系列线进阶
除了使用默认设置，系列线还可以设置丰富的属性。系列线的分解图如下：

With ch.Chart
With .SeriesCollection.NewSeries
.Values = ws.Range("b3:b" & lastrow)
.XValues = ws.Range("a3:a" & lastrow)
.ChartType = xlArea
.Interior.Color = RGB(127, 255, 212)
End With
With .SeriesCollection.NewSeries
.Values = ws.Range("b3:b" & lastrow)
.XValues = ws.Range("a3:a" & lastrow)
.ChartType = xlLineMarkers
.MarkerSize = 5 '数据标记大小
.MarkerStyle = xlMarkerStyleCircle '数据标记类型
.MarkerBackgroundColor = RGB(255, 106, 106)
.MarkerForegroundColor = RGB(90, 178, 238)
.HasDataLabels = True '有数据标签
.DataLabels.NumberFormat = "0.00"
.HasLeaderLines = True
.LeaderLines.Border.ColorIndex = 5
.Trendlines.Add Type:=xlPolynomial '趋势线
.Trendlines(1).Border.LineStyle = xlDash
.Trendlines(1).Border.Color = vbRed
End With
.HasLegend = False
End With
我们不指定Chart的图表类型，而直接指定系列的类型，且一个图表中不同趋势线可以使用不同的类型。第一条系列的类型是面积图（xlArea），而第二天系列是折线图（xlLineMarkers）。
在折线图中，我们还指定了数据标记的形状和大小，其形状可为如下的选项：
xlMarkerStyleAutomatic 自动设置标记
xlMarkerStyleCircle 圆形标记
xlMarkerStyleDash 长条形标记
xlMarkerStyleDiamond 菱形标记
xlMarkerStyleDot 短条形标记
xlMarkerStyleNone 无标记
xlMarkerStylePicture 图片标记
xlMarkerStylePlus 带加号的方形标记
xlMarkerStyleSquare 方形标记
xlMarkerStyleStar 带星号的方形标记
xlMarkerStyleTriangle 三角形标记
xlMarkerStyleX 带 X 记号的方形标记
HasDataLabels = True 指定有数据标签，就是系列线上得到那些数字。HasLeaderLines = True设置引导线。这些设置基本没有多少用途，反而让图表显得更复杂。Pass吧！
.Trendlines.Add Type:=xlPolynomial，添加一条趋势线，代表数据的运动规律。类型有如下选项，这个需要非常丰富的经验，不然无法选择准确的类型。
名称 值 描述
xlExponential 5 使用公式（如 y=ab^x）计算数据点的最小平方拟合值。
xlLinear -4132 使用线性公式 y = mx + b 计算数据点的最小平方拟合值。
xlLogarithmic -4133 使用公式 y = c ln x + b 计算数据点的最小平方拟合值。
xlMovingAvg 6 使用通过数据系列中某些部分计算出的一系列平均值。数据点个数等于数据系列中数据点的总数减去为周期指定的数值。
xlPolynomial 3 使用公式（如 y = ax^6 + bx^5 + cx^4 + dx^3 + ex^2 + fx + g）计算数据点的最小平方拟合值。
xlPower 4 使用公式（如 y = ax^b）计算数据点的最小平方拟合值。
最后绘制出来的图表如下：

三、坐标轴的设定
With ch.Chart.Axes(xlValue, xlPrimary)
.CrossesAt = .MinimumScale
.TickLabels.Font.Size = 8
.MajorGridlines.Border.ColorIndex = 20
.HasTitle = True
.AxisTitle.Text = "市盈率"
.AxisTitle.Orientation = xlVertical
End With
With ch.Chart.Axes(xlValue, xlSecondary)
.CrossesAt = .MinimumScale
.TickLabels.Font.Size = 8
.HasTitle = True
.AxisTitle.Text = "市净率"
.AxisTitle.Orientation = xlVertical
End With
With ch.Chart.Axes(xlCategory)
.TickLabels.Font.Size = 8
.TickLabels.NumberFormatLocal = "yyyy/m/d"
.HasTitle = True
.AxisTitle.Text = "日期"
.AxisTitle.Characters.Font.Size = 8
.AxisTitle.Characters.Font.Color = vbRed
End With
用Chart.Axes方法返回一个代表图表上坐标轴的对象。语法为：Chart.Axes(Type, AxisGroup)，Type指定要返回的坐标轴的类型，为以下常量之一：xlValue、xlCategory 或 xlSeriesAxis（xlSeriesAxis 仅对三维图表有效）。Excel图表把X轴叫做水平（分类）轴，Y轴叫垂直（值）轴。
名称 值 描述
xlCategory 1 坐标轴显示类别。
xlSeriesAxis 3 坐标轴显示数据系列。
xlValue 2 坐标轴显示值。
AxisGroup指定坐标轴组类型。如果省略该参数，则使用主坐标轴组（三维图表仅有一个坐标轴组）。其可取值已经在第二节讲过了，这里重复一遍：
名称 值 描述
xlPrimary 1 主坐标轴组。
xlSecondary 2 次坐标轴组。
Chart.Axes(xlValue, xlPrimary)返回主坐标轴对象，主坐标轴就是图表左边的Y轴。Chart.Axes(xlValue, xlSecondary)返回次坐标轴对象，次坐标轴就是图表右边的Y轴。Chart.Axes(xlCategory)返回的是X轴对象。坐标轴对象的方法只有Delete和SELECT两个，代表删除和选择，这个容易理解，其属性有几十种，像坐标轴的大小、位置、是否有箭头等等， Excel自动设置的已经足够好，我们基本上都不需要自行设置。
1. 设置坐标轴的标题区
HasTitle = True表示有标题。必须设置为True才能设置坐标轴标题的其他属性。
AxisTitle.Text和AxisTitle.Caption效果一样，设置坐标标题的显示文本。
AxisTitle.Left设置从坐标轴标题框左边缘到图表区左边缘的距离，AxisTitle.Top设置从坐标轴标题框上边缘到图表区顶部的距离，都以磅为单位。
AxisTitle.Orientation设置文本方向。此属性的值可设为–90 到90度之间的整数旋转值或以下常量之一：
名称 值 说明
xlDownward -4170 文字向下排列。
xlHorizontal -4128 文字水平排列。
xlUpward -4171 文字向上排列。
xlVertical -4166 向下居中排列。
上表的说明是我搜索微软官网的，貌似不太符合，我只好逐一试验，对应下边四种格式，第三种格式是Y轴的默认文字方向：

AxisTitle.Characters.Font.Size = 8设置坐标轴标题文字的大小为8.
AxisTitle.Characters.Font.Color = vbRed设置坐标轴标题文字的颜色为红色。
坐标轴标题默认是没有的，可以不设置，大家看自己的需求自行设置即可。效果图如下：

2.坐标轴的刻度线标签文本
什么是刻度线标签？X轴的2017/12/1、2017/12/29，Y轴的6.9、7.1这些就是刻度线标签，我们可以设置其属性，比如TickLabels.Font.Size = 8设置字体大小（字体、颜色也如此设置，这里不全部举例了，都是很简单的设置），可以设置其数字格式，如X轴是日期，我们可以这样设置TickLabels.NumberFormatLocal = "yyyy/m/d"，如果是数字，我们可以设置小数点位数，跟平时我们按Ctrl+1设置单元格格式没有任何区别。
TickLabels.Orientation设置刻度线标签的文字方向，此属性值可设为 -90 到 90 度之间的整数旋转值或以下常量之一：
名称 值 描述
xlTickLabelOrientationAutomatic -4105 由 Excel 设置文本方向。
xlTickLabelOrientationDownward -4170 向下排列文本。
xlTickLabelOrientationHorizontal -4128 水平排列字符。
xlTickLabelOrientationUpward -4171 向上排列文本。
3. 设置坐标轴的取值范围和刻度单位
坐标轴的最大值、最小值、主要刻度单位、次要刻度单位默认都是自动设置，Excel根据数据源区域的值自动合理设置。我们在需要的时候可以自行设置。刻度线标签的显示文本是由坐标轴的最大值、最小值、主要刻度单位、次要刻度单位确定的。
.MinimumScale 属性返回或设置数值轴上的最小值，.MaximumScale 属性返回或设置数值轴上的最大值。上图Y轴最小值自动设置为6.9，你可以设置为.MinimumScale =6.8，最大值也是一样的设置。
上图中可以看出，主要刻度单位为0.1，因为刻度线标签从6.9、7.0、7.1顺序排列下去，我们可以设置为主要刻度单位.MajorUnit = 0.05，这样Y轴就是6.95、7.00、7.05……，次要刻度单位.MinorUnit =0.01；X轴的主要刻度为2天，因为12/1、12/3这种序列，我们可以.MajorUnit =1，这样就可以把每天的日期都列出（如果有必要的话）。

4.设置X轴与Y轴的交点
CrossesAt 属性设置数值轴中与分类坐标轴的交点。CrossesAt = .MinimumScale是设置Y轴的最小值与X轴相交。因为市盈率最小值是6.9，所以前面的图表中Y轴的6.9与X轴相交。如果我们想要X轴与Y轴的交点位于Y轴上数值7.3 的位置，怎么设置呢？我们设置CrossesAt = 7.3，得到的图如下：

5.设置网格线
.MajorGridlines属性指定坐标轴的主要网格线。只有主要坐标轴组中的坐标轴才能有网格线。可以设置网格线的样式和颜色。
.HasMajorGridlines = True ’Excel图表默认有网格线，可省略该句
.MajorGridlines.Border.ColorIndex = 20 ’设置网格线颜色
.MajorGridlines.Border.LineStyle = xlDash ’可使用默认线型，省略该句
设置次要网格线用HasMinorGridlines和MinorGridlines，参数跟主要网格线相同。
四、设置图表的标题区
With ch.Chart
.HasTitle = True
.ChartTitle.Text = ch.Name
.ChartTitle.Font.Size = 18
.ChartTitle.Left = 137
.ChartTitle.Top = 2
End With
HasTitle = True，设置图表有标题。HasTitle必须为True才能设置标题区的属性。ChartTitle.Left = 137和ChartTitle.Top = 2是标题框相对于图表边框左边缘和上边缘的距离，单位为磅。这个比较简单易懂，不再讲解。

五、设置图表的绘图区
With ch.Chart
.PlotArea.Width = 347
.PlotArea.Left = 0
.PlotArea.Top = 20
.PlotArea.Height = 181
End With
必须添加了系列和设置了坐标轴之后，才能设置图表的绘图区的大小、位置和外观，否则报错。可以用PlotArea.Border.LineStyle给绘图区设置点线边框。PlotArea.Width和PlotArea.Height指定了绘图区的宽度和高度，而PlotArea.Left和PlotArea.Top指定绘图区相对于图表区边缘的距离。

六、设置图表的图例区
With ch.Chart
.HasLegend = True
.Legend.Font.Size = 8
.Legend.Font.ColorIndex = 5
.Legend.Position = xlLegendPositionRight
End With
可以用Left和Top属性设置图例的位置，Height和Width设置图例区域的大小，也可以用 Legend.Position指定图例在图表中的位置，可为如下值之一：
名称 值 描述
xlLegendPositionBottom -4107 位于图表下方。
xlLegendPositionCorner 2 位于图表边框的右上角。
xlLegendPositionCustom -4161 位于自定义的位置上。
xlLegendPositionLeft -4131 位于图表的左侧。
xlLegendPositionRight -4152 位于图表的右侧。
xlLegendPositionTop -4160 位于图表的上方。

图表可以用Export方法以图形格式导出图表到文件。语法为：
表达式.Export(Filename, FilterName, Interactive)，其中表达式是代表一个Chart对象的变量。Filename为被导出的文件的名称，后两个参数可选。可以导出的格式为PNG、GIF等图片格式，如ch.Chart.Export "g: est.png"，将该图表保存到G盘，文件名为test.png.图表可以导出为文件，然后用LoadPicture加载到窗体的图片控件中，也可以使用ChartSpace直接在窗体上绘制图表。ChartSpace是微软Office Web Components（简称OWC）中的控件，主要提供 Spreadsheet, Chart, PivotTable等组件。OWC能将部分Office的功能扩展到Web上，是一款优秀的服务器端图表引擎。使用前需要安装OWC11,。官方下载地址：https://www.microsoft.com/zh-cn/download/details.aspx?id=22276。安装好之后，在窗体上添加控件，引用：Microsoft Office Chart 11.0就可以了。