- ©«¤l
- 161
- ¥DÃD
- 26
- ºëµØ
- 0
- ¿n¤À
- 187
- ÂI¦W
- 0
- §@·~¨t²Î
- xp
- ³nÅ骩¥»
- office 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- TW
- µù¥U®É¶¡
- 2011-1-2
- ³Ì«áµn¿ý
- 2022-2-16
|
ÁÂÁÂ stillfish00 ¤j¤j
¤p§Ì¹ïvba¹Ïªíªº«ü¥O/__\
¹Ï¦n¹³¨S§@¥Î¡A¤£ª¾«ü¥O¤U¦b¨º
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 ©w¸q¬°¹Ïªí©Ò¦ûªº¦C°ª
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 '¬ù¨ú¤T¦~¸ê®Æ
totalRows = Sheets("data").Range("A" & Rows.Count).End(xlUp).Row ' ¶Ç¦^ B Äæ©Ò¨Ï¥ÎÀx¦s®æ¤§³Ì«á¤@®æ¤§¦C¸¹
totalRows2 = Sheets("data").Range("I" & Rows.Count).End(xlUp).Row ' ¶Ç¦^ I Äæ©Ò¨Ï¥ÎÀx¦s®æ¤§³Ì«á¤@®æ¤§¦C¸¹
' '¥ý§â¦C°ª§ä¥X¨Ó, ¤~¦nºâ¥X¹Ïn¦û´X¦C, ¨âӹϤ~¦n³s¦b¤@°_
' 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) ' ²L¬vºñ¦â
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 ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(6)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 160) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(7)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 150) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(8)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 140) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(9)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 130) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(10)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 120) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(11)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 110) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(12)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 100) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
With .SeriesCollection(13)
.Name = "=data!$J$" & toindexRows
.ChartType = xlLine
With .Format.Line
.Visible = msoTrue ' ¥D¤O¬É¤J
' .ForeColor.RGB = RGB(105, 205, 170) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- Âźñ¦â
.ForeColor.RGB = RGB(32, 178, 180) ' ¥D¤O¬É¤J«ü¼ÆÅã¥Ü¤§ÃC¦â --- ®ü¬vºñ¦â
.Transparency = 0
.Weight = 1 '½u±ø¼e«×
End With
End With
' .Axes(xlValue).TickLabels.NumberFormatLocal = "0_ "
With .Axes(xlCategory) ' X®y¼Ð¶b (®É¶¡¶b)
' .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 ' ±N쥻³]©w¤§°ª«×½Õ¦Ü¾A«×¦ì¸m
' .ChartArea.Width = cWidth
'
' text = "ªÑ²¼¹Ï K ½u¡B¥D¤O¡B´²¤á¡B»P¦¨¥æ¶q"
'
' .SetElement (msoElementChartTitleCenteredOverlay)
' .ChartTitle.text = text
' .ChartTitle.Format.TextFrame2.TextRange.Font.Size = 14
' .Legend.Position = xlCorner ' ±N¹Ïªí¹Ï¥Ü±q¹Ïªí³Ì¤U¤è½Õ¾ã¨ì¹Ïªí¤§¥k¤W¨¤¦ì¸m
End With
chartname = Trim(Replace(ActiveChart.Name, ActiveSheet.Name, ""))
ActiveSheet.Shapes(chartname).Left = Cells(xRow, yCol).Left ' ³]©w¦¹¹Ïªí¹ê»ÚÂ\©ñªº X¡BY ®y¼Ð¦ì¸m¡C
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 |
|