VBA Excel多种图表的制作

Public Sub MonthlyCalc()
Application.ScreenUpdating = False
‘定义用于循环的整型变量
Dim Itemp As Integer
Dim ChartTypeArray() As Variant
Dim ChartCount As Integer
‘ For Itemp = 1 To 12
‘计算商品(甲)的销售额合计
‘ Cells(4, Itemp + 2) = Cells(2, Itemp + 2) * Cells(3, Itemp + 2)
‘计算商品(乙)的销售额合计
‘ Cells(7, Itemp + 2) = Cells(5, Itemp + 2) * Cells(6, Itemp + 2)
‘计算商品(丙)的销售额合计
‘ Cells(10, Itemp + 2) = Cells(8, Itemp + 2) * Cells(9, Itemp + 2)
‘计算三种商品总的销售额合计
‘ Cells(11, Itemp + 2) = Cells(4, Itemp + 2) + Cells(7, Itemp + 2) _
+ Cells(10, Itemp + 2)
‘Next Itemp

'ChartTypeArray = Array(xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100)
'ChartTypeArray = Array(63, 64, 65, 66, 67)
'88, 89, 90, 91 excluded
'ChartTypeArray = Array(-4169, -4151, -4120, -4102, -4101, -4100, -4098, 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)

'ChartTypeArray = Array(92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112)
ChartTypeArray = Array(-4169, -4151, -4120, -4102, -4101, -4100, -4098, 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)

ChartCount = 1
Do While (ChartCount <= (UBound(ChartTypeArray) + 1))
    '添加图表
    charts.Add
    '定义图表类型
    ActiveChart.ChartType = ChartTypeArray(ChartCount - 1)
    '图表数据源****请注意工作表名是否正确,否则会报错
    ActiveChart.SetSourceData Source:=Sheets("商品月销量").Range("A1:N1,A4:N4,A7:N7,A10:N10"), PlotBy:=xlRows
    'PlotBy:=xlColumns
    '设置图表添加的位置
   ' ActiveChart.SetSourceData Source:=Sheets("商品月销量").Range("A1:N11"), PlotBy:=xlRows
    ActiveChart.Location Where:=xlLocationAsObject, Name:="sheet1"
      'xlLocationAsNewSheet
    With ActiveChart
        '使图表带有“标题”
        .HasTitle = True
        '设置图表“标题”
        .ChartTitle.Characters.Text = ChartTypeArray(ChartCount - 1) & "——月销售情况对比"
        '使图表带有X坐标标题
        .Axes(xlCategory, xlPrimary).HasTitle = True
        '设置图表X坐标标题
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "月份"
        '使图表带有Y坐标标题
       .Axes(xlValue, xlPrimary).HasTitle = True
        '使图表带有Y坐标标题
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "合计(元)"
    End With

   With ActiveChart.Parent
       ' .Left = 10 + 368 * (ChartCount - 1) '此处用以修改图表在工作表中的位置
       ' .Top = 200
       .Left = 10 + Int(ChartCount / 15) * 356 '此处用以修改图表在工作表中的位置
        If (ChartCount Mod 15 <> 0) Then
            .Top = 222 * (ChartCount Mod 15)
             On Error Resume Next
          Else
            .Top = 222
        End If
    End With

    ChartCount = ChartCount + 1
Loop


Application.ScreenUpdating = True

End Sub

‘原文链接:https://blog.csdn.net/hpdlzu80100/article/details/73157227

cnliutz

计算机基础教师

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注