麻辣家族討論版版 - Powered by Discuz! Board

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

複製代碼