VBA：練習畫折線圖(根據集保庫存資料)

畫持股變化折線圖：

 

怎畫的入門篇語法：MSDN

code：

	Sub 持股對照圖(SHEET_NAME, RANGE_ADDRESS, DATA1, DATA2, xlColumnClustered_option, XLINE_WORD, DELTEE)
	Sheets(SHEET_NAME).Activate
	If DELTEE = 1 And Sheets(SHEET_NAME).ChartObjects.Count > 0 Then
	Sheets(SHEET_NAME).ChartObjects.Delete
	End If
	A = ActiveSheet.Range(RANGE_ADDRESS).Left
	b = ActiveSheet.Range(RANGE_ADDRESS).Top
	Set myChart = ActiveSheet.ChartObjects.Add(A, b, 800, 324)
	With myChart.Chart
	.ChartType = xlLine
	If DATA2 <> "" Then
	.SetSourceData SOURCE:=Range(DATA1 & "," & DATA2)
	Else
	.SetSourceData SOURCE:=Range(DATA1)
	End If
	For i = 1 To .SeriesCollection.Count Step 1
	If .SeriesCollection(i).Name <> XLINE_WORD Then
	A1 = myChart.Chart.SeriesCollection(i).Values
	With .SeriesCollection(i).Format.Line
	.Visible = msoTrue
	.Weight = 3
	End With
	If xlColumnClustered_option = 1 Then
	.SeriesCollection(i).ChartType = xlColumnClustered
	With .SeriesCollection(i).Format.Line
	.Visible = msoTrue
	.Weight = 1.5
	End With
	End If
	.SeriesCollection(i).Interior.ColorIndex = 23 '(0, 153, 255)
	.SeriesCollection(i).Border.ColorIndex = 23 ' RGB(0, 153, 255)
	Else
	A2 = myChart.Chart.SeriesCollection(i).Values
	.SeriesCollection(i).AxisGroup = 2
	.SeriesCollection(i).Interior.ColorIndex = 3
	.SeriesCollection(i).Border.ColorIndex = 3
	With .SeriesCollection(i).Format.Line
	.Visible = msoTrue
	.Weight = 3.25
	End With
	MIN_S = Application.Min(A2)
	.Axes(xlValue, xlSecondary).Select
	.Axes(xlValue, xlSecondary).MinimumScale = 0
	.Axes(xlValue, xlSecondary).MinimumScale = MIN_S
	.Axes(xlValue, xlSecondary).TickLabels.Font.Size = 15
	End If
	Next i
	.Axes(xlValue).TickLabels.Font.Size = 15
	MIN_V = Application.Min(A1)
	.Axes(xlValue).MinimumScale = 0
	.Axes(xlValue).MinimumScale = MIN_V '* 0.95
	.Legend.Left = 3.511
	.Legend.Top = 1.644
	.Legend.Width = 800
	.Legend.Format.TextFrame2.TextRange.Font.Size = 25
	w = myChart.Width
	h = myChart.Heigh
	.PlotArea.Width = w * 0.98
	.PlotArea.Height = h * 0.98
	End With
	End Sub

view raw gistfile1.txt hosted with ❤ by GitHub

2.整理原始資料：如下。

圖1.

這畫圖副程式，如上CODE內容，表頭長這樣：

持股對照圖(SHEET_NAME, RANGE_ADDRESS, DATA1, DATA2, xlColumnClustered_option, XLINE_WORD, DELTEE)

有7個變數要設定。

SHEET_NAME：資料位置的工作表

RANGE_ADDRESS：圖要放的儲存格位置

DATA1：第一組資料位置

DATA2：第二組資料位置

xlColumnClustered_option：畫圖設定，目前都設定0為主

XLINE_WORD：第二組資料的標題設定

DELTEE：是否把前回CHART物件刪除。

小編的資料，有特別整理出集保庫存分析，與每周收盤的股價，演練的資料為100張以下持股資料，小編的原始資料放在AL與AT這兩個儲存格，以及想把圖放在W14的儲存格位置，b_count_a這個變數是資料總筆數設定，小編是抓50筆所以"AL1:AL" & b_count_a的執行結果為"AL1:AL50"這樣歐，整體副程式呼叫設定如下：

Call 持股對照圖("集保庫存分析", "w14", "AL1:AL" & b_count_a, "AT1:AT" & b_count_a, 1, "當周最後收盤價", 0)

執行結果如下：

圖2.