Excel VBA组图表系列颜色

Question

我有一个程序，它会提示用户打开文件并从文件数据生成折线图。该系列将被绘制在同一张图表上。将会有两个命令按钮：一个供用户选择文件;另一个用于生成图表。每次单击图表的命令按钮时，系列将根据新打开的文件数据添加。

With ThisWorkbook.cht 

For a = 1 To lastRow 

' Add each series 
    Set chtSeries = .SeriesCollection.NewSeries 

    With chtSeries 

     .Values = rng 
     .XValues = Worksheets(sheet).Range(Worksheets(sheet).Cells(a, 1), Worksheets(sheet).Cells(a, 10)) 


    End With 

    Next a 

    End With 

但是，我需要对系列行进行分组，由此来自同一文件的行由相同颜色表示。

Answer 1

下面是一些详细的代码，应该可以帮助您：

Sub Graph() 

Dim Gr As Chart 

     Set Gr = ActiveWorkbook.Charts.Add 
      With Gr 
      'Whole data source 
      .SetSourceData Source:=Range(Sheets(Src_Name).Cells(2, 1), Sheets(Src_Name).Cells(20, 5)), PlotBy:=xlRows 
      'Graph type 
      .ChartType = xlXYScatterSmooth 
      'Place 
      .Location Where:=xlLocationAsNewSheet, Name:=NewSheetName 
      'Title 
      .HasTitle = True 
      .ChartTitle.Characters.Text = "Chart Title" 

      For a = 1 To 20 
       'Data Series 1 
       .SeriesCollection.NewSeries 
       .SeriesCollection(a).Values = Range(Sheets(Src_Name).Cells(2, 2), Sheets(Src_Name).Cells(20, 5)) 'change with a 
       .SeriesCollection(a).XValues = Range(Sheets(Src_Name).Cells(2, 1), Sheets(Src_Name).Cells(20, 1)) 'not changing I think 
       .SeriesCollection(a).AxisGroup = 1 
       .SeriesCollection(a).Name = "MTTF" 
       '.SeriesCollection(i).Format.Line.Weight = 1 
       '.SeriesCollection(i).Format.Line.ForeColor.RGB = RGB(int1 as integer, int1 as integer, int3 as integer) ' for a row/line 
       '.SeriesCollection(i).Format.Fill.ForeColor.RGB = RGB(int1 as integer, int1 as integer, int3 as integer) 
      Next a 



      'Axis parameters 
      .Axes(xlCategory, xlPrimary).HasTitle = True 
      .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Age" 
      .Axes(xlValue, xlPrimary).HasTitle = True 
      .Axes(xlValue, xlPrimary).AxisTitle.Text = "Hours" 
      .PlotArea.Interior.ColorIndex = 2 
      .Axes(xlValue).MajorGridlines.Border.LineStyle = xlDot 
      .ChartArea.Font.Size = 14 
      .Deselect 
      End With 

      'Legend positioning 
      With ActiveChart.Legend 
       .Left = 350 
       .Top = 75 
      End With 
      'Drawing area positiong 
      With ActiveChart.PlotArea 
       .Width = 550 
       .Height = 350 
      End With 



'Clean memory 
Set Gr = Nothing 



End Sub