- 帖子
- 161
- 主題
- 26
- 精華
- 0
- 積分
- 187
- 點名
- 0
- 作業系統
- xp
- 軟體版本
- office 2010
- 閱讀權限
- 20
- 性別
- 男
- 來自
- TW
- 註冊時間
- 2011-1-2
- 最後登錄
- 2025-10-9
|
謝謝 stillfish00 大大
小弟對vba圖表的指令/__\
圖好像沒作用，不知指令下在那
Option Explicit
Sub drawCharts()
Dim toindexRows As Single, totalRows As Single, totalRows2 As Single
Dim xRow, yCol, cHeight, cWidth As Integer
Dim text As String
Dim chartname As String
Dim sRowHeight As Single
Dim VIMin As Single, VIMax As Single
xRow = 3
yCol = 1
cHeight = 31 'CHeight 定義為圖表所佔的列高
cWidth = 874
With Sheets("data")
VIMin = Int(Application.WorksheetFunction.Min(.Range(.[I5], .[I5].End(xlDown))) * 0.88 / 1000) * 1000
VIMax = Int(Application.WorksheetFunction.Max(.Range(.[I5], .[I5].End(xlDown))) * 1.12 / 1000) * 1000 + 1000
End With
Sheets("chart").Select
ActiveSheet.ChartObjects.Delete
toindexRows = Sheets("data").Range("I" & Rows.Count).End(xlUp).Row - 1100 '約取三年資料
totalRows = Sheets("data").Range("A" & Rows.Count).End(xlUp).Row ' 傳回 B 欄所使用儲存格之最後一格之列號
totalRows2 = Sheets("data").Range("I" & Rows.Count).End(xlUp).Row ' 傳回 I 欄所使用儲存格之最後一格之列號
' '先把列高找出來, 才好算出圖要佔幾列, 兩個圖才好連在一起
' sRowHeight = Sheets("data").Rows(3).RowHeight
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
.SetSourceData Source:=Range("data!$A$" & toindexRows & ":data!$A$" & totalRows2 & ", data!$B$" & toindexRows & ":data!$E$" & totalRows2)
Debug.Print 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("data!$J$" & toindexRows & ":data!$R$" & totalRows2)
' .SeriesCollection.Add Source:=Range("data!$J$" & toindexRows & ":data!$R$" & totalRows)
Debug.Print totalRows
With .SeriesCollection(5)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 170) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(6)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 160) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(7)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 150) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(8)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 140) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(9)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 130) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(10)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 120) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(11)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 110) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(12)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 100) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
End With
End With
With .SeriesCollection(13)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' 主力界入
' .ForeColor.RGB = RGB(105, 205, 170) ' 主力界入指數顯示之顏色 --- 藍綠色
.ForeColor.RGB = RGB(32, 178, 180) ' 主力界入指數顯示之顏色 --- 海洋綠色
.Transparency = 0
.Weight = 1 '線條寬度
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
.MaximumScale = VIMax
.MinimumScale = VIMin
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
'Debug.Print Sheets("data").Range("A" & Rows.Count).End(xlUp).Row
'Debug.Print Sheets("data").Range("I" & Rows.Count).End(xlUp).Row
Debug.Print toindexRows
End Sub |
|
發表於 2013-10-5 00:49
| 
