vba图表常用命令

ActiveSheet.UsedRange.Rows.Count获取工作表的行数(注：考虑向前兼容性)Cells.Item(5,C)引单元格C5Cells.Item(5,3)引单元格C5Application.WorksheetFunction.IsNumber(A1)使用工作表函数检查A1单元格中的数据是否为数字Range(A:A).Find(Application.WorksheetFunction.Max(Range(A:A))).Activate激活单元格区域A列中最大值的单元格Cells(8,8).FormulaArray==SUM(R2C[-1]:R[-1]C[-1]*R2C:R[-1]C)在单元格中输入数组公式。注意必须使用R1C1样式的表达式ActiveSheet.ChartObjects.Count获取当前工作表中图表的个数ActiveSheet.ChartObjects(Chart1).Select选中当前工作表中图表Chart1ActiveSheet.ChartObjects(Chart1).Activate选中当前图表区域ActiveChart.ChartArea.Select选中当前图表区域WorkSheets(Sheet1).ChartObjects(Chart2).Chart.ChartArea.Interior.ColorIndex=2更改工作表中图表的图表区的背景颜色Sheets(Chart2).ChartArea.Interior.ColorIndex=2更改图表工作表中图表区的颜色Charts.Add添加新的图表工作表ActiveChart.SetSourceDataSource:=Sheets(Sheet1).Range(A1:D5),PlotBy:=xlColumns指定图表数据源并按列排列ActiveChart.LocationWhere:=xlLocationAsNewSheet新图表作为新图表工作表ActiveChart.PlotArea.Interior.ColorIndex=xlNone将绘图区颜色变为白色WorkSheets(Sheet1).ChartObjects(1).Chart.ExportFileName:=C：MyChart.gif,FilterName:=GIF将图表1导出到C盘上并命名为MyChart.gifActiveSheet.ChartObjects.Delete删除工作表上所有的ChartObject对象ActiveWorkbook.Charts.Delete删除当前工作簿中所有的图表工作表Excel中的VBA常量和编码值所代表的标准图表类型图表类型——描述——ExcelVBA常量——编码值(注：下面按此顺序排列)——————————————————————————柱形图—簇状柱形图—xlColumnClustered—513D簇状柱形图—xl3DColumnClustered—54堆积柱形图—xlColumnStacked—523D堆积柱形图—xl3DColumnStacked—55百分比堆积柱形图—xlColumnStacked100—533D百分比堆积柱形图—xl3DColumnStacked100—563D柱形图—xl3DColumn—-4100——————————————————————————条形图—簇状条形图—xlBarClustered—573D簇状条形图—xl3DBarClustered—60堆积条形图—xlBarStacked—583D堆积条形图—xl3DBarStacked—61百分比堆积条形图—xlBarStacked100—593D百分比堆积条形图—xl3DBarStacked100—62——————————————————————————折线图—折线图—xlLine—4数据点折线图—xlLineMarkers—65堆积折线图—xlLineStacked—63堆积数据点折线图—xlLineMarkersStacked—66百分比堆积折线图—xlLineStacked100—64百分比堆积数据点折线图—xlLineMarkersstacked100—673D折线图—xl3DLine—-4101——————————————————————————饼图—饼图—xlPie—5分离型饼图—xlPieExploded—693D饼图—xl3DPie—-4102分离型3D饼图—xl3DPieExploded—70复合饼图—xlPieOfPie—68复合条饼图—xlBarOfPie—71——————————————————————————XY(散点)图—散点图—xlXYScatter—-4169平滑线散点图—xlXYScatterSmooth—72无数据点平滑线散点图—xlXYScatterSmoothNoMarkers—73折线散点图—xlXYScatterLines—74无数据点折线散点图—xlXYScatterLinesNoMarkers—75——————————————————————————气泡图—气泡图—xlBubble—153D气泡图—xlBubble3DEffect—87——————————————————————————面积图—面积图—xlArea—13D面积图—xl3DArea—-4098堆积面积图—xlAreaStacked—763D堆积面积图—xl3DAreaStacked—78百分比堆积面积图—xlAreaStacked100—773D百分比堆积面积图—xl3DAreaStacked100—79——————————————————————————圆环图—圆环图—xlDoughnut—-4120分离型圆环图—xlDoughnutExploded—80——————————————————————————雷达图—雷达图—xlRadar—-4151数据点雷达图—xlRadarMarkers—81填充雷达图—xlRadarFilled—82——————————————————————————曲面图—3D曲面图—xlSurface—83曲面图(俯视)—xlSurfaceTopView—853D曲面图(框架图)—xlSurfaceWireframe—84曲面图(俯视框架图)—xlSurfaceWireframeTopView—86——————————————————————————股价图—盘高-盘低-收盘图—xlStockHLC—88成交量-盘高-盘低-收盘图—xlStockVHLC—90开盘-盘高-盘低-收盘图—xlStockOHLC—89成交量-开盘-盘高-盘低-收盘图—xlStockVOHLC—91——————————————————————————圆柱图—柱形圆柱图—xlCylinderColClustered—92条形圆柱图—xlCylinderBarClustered—95堆积柱形圆柱图—xlCylinderColStacked—93堆积条形圆柱图—xlCylinderBarStacked—96百分比堆积柱形圆柱图—xlCylinderColStacked100—94百分比堆积条形圆柱图—xlCylinderBarStacked100—973D柱形圆柱图—xlCylinderCol—98——————————————————————————圆锥图—柱形圆锥图—xlConeColClustered—99条形圆锥图—xlConeBarClustered—102堆积柱形圆锥图—xlConeColStacked—100堆积条形圆锥图—xlConeBarStacked—103百分比堆积柱形圆锥图—xlConeColStacked100—101百分比堆积条形圆锥图—xlConeBarStacked100—1043D柱形圆锥图—xlConeCol—105——————————————————————————棱锥图—柱形棱锥图—xlPyramidColClustered—106条形棱锥图—xlPyramidBarClustered—109堆积柱形棱锥图—xlPyramidColStacked—107堆积条形棱锥图—xlPyramidBarStacked—110百分比堆积柱形棱锥图—xlPyramidColStacked100—108百分比堆积条形棱锥图—xlPyramidBarStacked100—1113D柱形棱锥图—xlPyramidCol—112回复举报#2颜新祥2010-01-1422:17利用以上参数，可用VBA写出批量产生Excel图表的程序。代码如下：PrivateSubcmdCompareSales_Click()DimiRows,iChartType,iChartTypeRowsAsIntegerDimiTempAsIntegerDimsChartTitle,sCategoryTitle,sValueTitleAsStringDimsChartNameAsStringDimlArrayChartType(73)AsLong,sArrayChartConst(73)AsString,sArrayChartExplain(73)AsStringForiTemp=0To72lArrayChartType(iTemp)=Sheets(Sheet2).Cells(iTemp+1,1).ValuesArrayChartConst(iTemp)=Sheets(Sheet2).Cells(iTemp+1,3).ValuesArrayChartExplain(iTemp)=Sheets(Sheet2).Cells(iTemp+1,2).ValueNextiTempSheets(Sheet1).ActivatesChartTitle=销售量比较图sCategoryTitle=Category标题sValueTitle=Value标题iRows=Sheets(sheet1).UsedRange.Rows.CountOnErrorResumeNextSheets(sheet1).ChartObjects.DeleteForiChartType=0To72Step1Charts.AddActiveChart.ChartType=lArrayChartType(iChartType)ActiveChart.SetSourceDataSource:=Sheets(Sheet1).Range(A2:M&CStr(iRows)),PlotBy:=xlRowsActiveChart.Locationwhere:=xlLocationAutomatic,Name:=Sheet1WithActiveChart.HasTitle=True.ChartTitle.Characters.Text=sChartTitle&sArrayChartConst(iChartType)&sArrayChartExplain(iChartType)'.ChartTitle.Characters.Text=sChartTitle&xl3DArea.Axes(xlCategory,xlPrimary).HasTitle=False'.Axes(xlCategory,xlPrimary).AxisTitle.Text=sCategoryTitle.Axes(xlValue,xlPrimary).HasTitle=False'.Axes(xlValue,xlPrimary).AxisTitle.Text=sValueTitleEndWithsChartName=Mid(ActiveChart.Name,8,6)ActiveSheet.Shapes(sChartName).Left=Range(B&Str(18*(iChartType+1))).LeftActiveSheet.Shapes(sChartName).Top=Range(B&Str(18*(iChartT