Sub 五月九月價(jià)差圖()

Dim ch As ChartObject
Dim myrow As Long
Dim sh, sh59 As Worksheet
Dim rngD, rngc1, rngc2, rngp1, rngp2, rngs As Range

Set sh = Worksheets("workingarea")
Set sh59 = Worksheets("九月減五月")
sh59.Activate
myrow = sh.[a65536].End(xlUp).Row
'-----------------------------------------------------------------------------------------------------------------------
'
'定義時(shí)1為近月，2為遠(yuǎn)月，這樣也為了以后方便修改
Set rngD = sh.Range("a3:a" & myrow)     '日期列
Set rngc1 = sh.Range("e3:e" & myrow)    '五月收盤
Set rngc2 = sh.Range("h3:h" & myrow) '九月收盤
Set rngp1 = sh.Range("g3:g" & myrow) '五月持倉
Set rngp2 = sh.Range("j3:j" & myrow)  '九月持倉
Set rngs = sh.Range("o3:o" & myrow) '五九價(jià)差

'開始繪圖
On Error GoTo err:
sh59.ChartObjects("五九價(jià)差與持倉").Delete   '確保這是唯一的圖
err:
Set ch = sh59.ChartObjects.Add(0, 0, 600, 300)  '定義位置極其大小
ch.Name = "五九價(jià)差與持倉"    '圖表定名
'為圖表填加數(shù)據(jù)
With ch.Chart
            .ChartType = xlLine
            .SeriesCollection.NewSeries    '這個(gè)必須得有
            .SeriesCollection(1).Values = rngs  '價(jià)差
            .SeriesCollection(1).XValues = rngD  '橫軸為時(shí)間
            .SeriesCollection(1).Name = "五九價(jià)差"
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = rngp1 '五月持倉
            .SeriesCollection(2).XValues = rngD
            .SeriesCollection(2).Name = "五月持倉"
            .SeriesCollection.NewSeries
            .SeriesCollection(3).Values = rngp2 '九月持倉
            .SeriesCollection(3).XValues = rngD
            .SeriesCollection(3).Name = "九月持倉"
End With
 '設(shè)置折線格式
  
   ch.Chart.SeriesCollection(1).AxisGroup = 2     '很奇怪，不能將這幾句放到上一段去，可能是因?yàn)閚ewseries的關(guān)系吧
   ch.Chart.SeriesCollection(1).MarkerStyle = xlNone
   ch.Chart.SeriesCollection(2).AxisGroup = 1
   ch.Chart.SeriesCollection(2).MarkerStyle = xlNone
   ch.Chart.SeriesCollection(3).AxisGroup = 1
   ch.Chart.SeriesCollection(3).MarkerStyle = xlNone
  
  
'定義坐標(biāo)主軸、副軸、橫軸的格式
  
With ch.Chart.Axes(xlValue, xlPrimary)    '定義主y軸
             .MajorUnit = Int((WorksheetFunction.Max(rngp1.Value, rngp2.Value) * 1.5 - WorksheetFunction.Min(rngp1.Value, rngp2.Value)) / 100) * 100 / 10 '一開始我直接把最大值乘以1.2，最小值乘以0.8，可是遇到負(fù)數(shù)就麻煩了
             .MaximumScale = Int((WorksheetFunction.Max(rngp1.Value, rngp2.Value) * 1.5 + .MajorUnit) / 100) * 100
             .MinimumScale = Int((WorksheetFunction.Min(rngp1.Value, rngp2.Value) * -.MajorUnit) / 100) * 100
             .CrossesAt = .MinimumScale  '與y軸交叉于最小值
             .TickLabels.Font.Size = 8   'y軸字體大小
            
End With
              
With ch.Chart.Axes(xlValue, xlSecondary)  '定義副y軸
             .MajorUnit = (WorksheetFunction.Max(rngs.Value) - WorksheetFunction.Min(rngs.Value)) / 10
             .MaximumScale = Int((WorksheetFunction.Max(rngs.Value) + .MajorUnit) / 100) * 100
             .MinimumScale = Int((WorksheetFunction.Min(rngs.Value) - .MajorUnit) / 100) * 100
             .CrossesAt = .MinimumScale
             .TickLabels.Font.Size = 8
End With

With ch.Chart.Axes(xlCategory).TickLabels     '定義x軸即分類軸的字體和格式
        .Font.Size = 8
        .NumberFormatLocal = "yy-m-d"
End With

'設(shè)置標(biāo)題、圖例格式和繪圖區(qū)大小
With ch.Chart
             .HasTitle = True
             .ChartTitle.Text = ch.Name
             .ChartTitle.Font.Size = 12
             .ChartTitle.Font.Bold = True
             .ChartTitle.Left = ch.Width / 2.2
             .Legend.Font.Size = 8
             .PlotArea.Width = 580
             .PlotArea.Left = 10
             .PlotArea.Top = 10
             .PlotArea.Height = 290
             .ChartTitle.Top = .PlotArea.InsideTop
             .Legend.Left = .PlotArea.InsideLeft
             .Legend.Top = .PlotArea.InsideTop
End With

Set ch = Nothing

end sub