- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
4#
發表於 2012-5-7 14:40
| 只看該作者
本帖最後由 c_c_lai 於 2012-5-7 14:42 編輯
回復 3# white5168 - Option Explicit
- Sub drawCharts()
- Dim totalRows As Single
- Dim xRow, yCol, cHeight, cWidth As Integer
- Dim text As String
- Dim chartname As String
- Dim sRowHeight As Single
-
- xRow = 3
- yCol = 1
- cHeight = 31 'CHeight 定義為圖表所佔的列高
- cWidth = 874
-
- Sheets("工作表1").Select
-
- ActiveSheet.ChartObjects.Delete
-
- totalRows = Sheets("工作表1").Range("B" & Rows.Count).End(xlUp).Row ' 傳回 B 欄所使用儲存格之最後一格之列號
- '先把列高找出來, 才好算出圖要佔幾列, 兩個圖才好連在一起
- sRowHeight = Sheets("工作表1").Rows(3).RowHeight
-
- ActiveSheet.Shapes.AddChart.Select
- With ActiveChart
- .SetSourceData Source:=Range("工作表1!$B$1:工作表1!$B$" & totalRows & ", 工作表1!$C$1:工作表1!$F$" & totalRows)
- .ChartType = xlStockOHLC
- With .ChartGroups(1)
- .AxisGroup = 1
- ' .UpBars.Format.Fill.ForeColor.RGB = RGB(255, 0, 0) ' 紅色
- .UpBars.Format.Fill.ForeColor.RGB = RGB(255, 69, 0) ' 橘紅色
- ' .DownBars.Format.Fill.ForeColor.RGB = RGB(0, 32, 96) ' 深藍色
- .DownBars.Format.Fill.ForeColor.RGB = RGB(0, 250, 170) ' 淺洋綠色
- End With
-
- .SeriesCollection.Add Source:=Range("工作表1!$G$2:工作表1!$I$" & totalRows)
-
- With .SeriesCollection(5)
- .AxisGroup = 2
- .Name = "=工作表1!$G$1"
- .ChartType = xlColumnClustered
-
- With .Format.Line
- .Visible = msoTrue ' 成交量
- .ForeColor.RGB = RGB(147, 112, 219) ' 成交量指數顯示之顏色 --- 紫色
- .Transparency = 0
- End With
- End With
-
- With .SeriesCollection(6)
- .Name = "=工作表1!$H$1"
- .ChartType = xlLine
- With .Format.Line
- .Visible = msoTrue ' 主力界入
- ' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
- .ForeColor.RGB = RGB(32, 178, 170) ' 主力界入指數顯示之顏色 --- 海洋綠色
- .Transparency = 0
- End With
- End With
-
- With .SeriesCollection(7)
- .Name = "=工作表1!$I$1"
- .ChartType = xlLine
- With .Format.Line
- .Visible = msoTrue ' 散戶方向
- .ForeColor.RGB = RGB(65, 105, 225) ' 散戶方向指數顯示之顏色 --- 天藍色
- .Transparency = 0
- End With
- End With
-
- .Axes(xlValue).TickLabels.NumberFormatLocal = "0_ "
-
- With .Axes(xlCategory) ' X座標軸 (時間軸)
- .CategoryType = xlCategoryScale
- .TickLabels.NumberFormatLocal = "hh:mm"
- .MajorTickMark = xlNone
- .Border.Weight = xlHairline
- .Border.LineStyle = xlNone
- .TickLabelPosition = xlLow
- .TickLabels.Font.Size = 10
- End With
-
- .ChartArea.Height = cHeight * sRowHeight ' 將原本設定之高度調至適度位置
- .ChartArea.Width = cWidth
-
- text = "股票圖 K 線、主力、散戶、與成交量"
-
- .SetElement (msoElementChartTitleCenteredOverlay)
- .ChartTitle.text = text
- .ChartTitle.Format.TextFrame2.TextRange.Font.Size = 14
- .Legend.Position = xlCorner ' 將圖表圖示從圖表最下方調整到圖表之右上角位置
- End With
-
- chartname = Trim(Replace(ActiveChart.Name, ActiveSheet.Name, ""))
- ActiveSheet.Shapes(chartname).Left = Cells(xRow, yCol).Left ' 設定此圖表實際擺放的 X、Y 座標位置。
- ActiveSheet.Shapes(chartname).Top = Cells(xRow, yCol).Top
-
- Range("A1").Select
- End Sub
|
|
