- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
5#
發表於 2013-6-8 19:41
| 只看該作者
回復 4# lin_6219
試試看！- Sub KChartWithVolume() ' K 線圖 與 成交量圖 放在同一圖表
- Dim nRow As Integer, ChtObj As ChartObject
- Dim i As Integer, j As Integer
- Dim myMax, myMin, GapNr As Integer
-
- On Error Resume Next
-
- Worksheets("主畫面").ChartObjects.Delete
- nRow = Worksheets("繪圖資料").Range("A65536").End(xlUp).Row
-
- With Worksheets("繪圖資料") ' 設定主座標軸最大及最小值
- myMax = Application.Max(.Range("C2:C" & CStr(nRow)))
- myMin = Application.Min(.Range("D2:D" & CStr(nRow)))
- myMin = myMin - (myMax - myMin)
- End With
-
- ' Set ChtObj = Worksheets("主畫面").ChartObjects.Add(1, 1, 450, 250)
- Set ChtObj = Worksheets("主畫面").ChartObjects.Add(1, 1, 490, 280)
-
- With ChtObj.Chart
- ' .SetSourceData Worksheets("繪圖資料").Range("A2:E" & CStr(nRow))
- .SetSourceData Source:=Range("繪圖資料!$A$2:繪圖資料!$E$" & CStr(nRow))
- .ChartType = xlStockOHLC
- .HasTitle = True
- .ChartTitle.Characters.Text = "K線與成交量圖"
-
- With .ChartGroups(1)
- .HasUpDownBars = True
- .UpBars.Interior.ColorIndex = 3
- .DownBars.Interior.ColorIndex = 1
- .GapWidth = 10
- End With
-
- .SeriesCollection(1).Name = "" ' 開盤價
- .SeriesCollection(2).Name = "" ' 最高價
- .SeriesCollection(3).Name = "" ' 最低價
- .SeriesCollection(4).Name = "" ' 收盤價 (成交價)
-
- With .Axes(xlValue)
- .MaximumScale = Round(myMax, 2)
- .MinimumScale = Round(myMin, 2)
- ' .MaximumScale = myMax
- ' .MinimumScale = myMin
- .MajorUnit = 0.5 ' 圖表左側數列之間距值設定
- End With
-
- ' .SeriesCollection.Add Worksheets("繪圖資料").Range("G1:G" & CStr(nRow))
- .SeriesCollection.Add Source:=Range("繪圖資料!$G$1:繪圖資料!$G$" & CStr(nRow))
- With .SeriesCollection(5)
- .AxisGroup = 1
- ' .ChartType = xlXYScatterLinesNoMarkers
- .ChartType = xlLines
- .Name = "=繪圖資料!$G$1" ' 移動平均線
- .Border.ColorIndex = 7
- End With
-
- ' .SeriesCollection.NewSeries ' 新增成交量數列
- .SeriesCollection.Add Source:=Range("繪圖資料!$F$1:繪圖資料!$F$" & CStr(nRow))
- With .SeriesCollection(6)
- .AxisGroup = 2 ' 設為副座標軸
- ' .Values = Worksheets("繪圖資料").Range("F2:F" & CStr(nRow))
- .ChartType = xlColumnClustered
- .Name = "成交量"
- .Interior.ColorIndex = 17
- End With
-
- With Worksheets("繪圖資料") ' 計算副座標軸最大及最小值
- myMax = Application.Max(.Range("F2:F" & CStr(nRow)))
- myMax = myMax * 2
- myMin = 0.01
- End With
-
- With .Axes(xlValue, xlSecondary) ' 設定副座標軸最大及最小值
- .MaximumScale = Round(myMax, 0)
- .MinimumScale = Round(myMin, 0)
- End With
-
- With .PlotArea ' 調整繪圖區域大小與位置
- .Top = .Top - 2
- .Height = .Height + 10
- .Width = .Width + 75
- End With
-
- .PlotArea.Select ' 將圖表的繪圖區格線灰黑顏色修改成淡青色、以及表格實線改以虛線表示
- .Axes(xlValue).MajorGridlines.Select
- With Selection.Format.Line
- .Visible = msoTrue
- .ForeColor.ObjectThemeColor = msoThemeColorAccent1
- .ForeColor.TintAndShade = 0
- .ForeColor.Brightness = 0.8000000119
- .Transparency = 0
- .Weight = 0.25
- .DashStyle = msoLineSysDash
- End With
-
- With .Legend
- .Position = xlLegendPositionTop
- .Top = .Top - 8
- .Border.ColorIndex = 57
- .Border.Weight = xlThin
- .Border.LineStyle = xlContinuous
- .Interior.ColorIndex = xlNone
- End With
- End With
- Worksheets("主畫面").[A1].Select
- End Sub
|
|
))
