在Excel中對資料進行分析時， 使用圖表可直觀地查看分析結果。 Excel提供了上百種圖表類型， 通過VBA代碼可以控制圖表的各個方面。
目錄
1 創建圖表工作表
2 創建嵌入圖表
3 用Location方法轉換圖表類型
4 用ChartTitle物件獲取圖表標題資訊
5 用SeriesCollection方法獲取圖表系列資訊
6 用SetSourceData方法調整圖表的資料來源
7 用CopyPicture方法將圖表保存為圖片
8 嵌入圖表也能與使用者交互
1 創建圖表工作表有如下工作表， 資料對應儲存格區域為：A1:E8
成績表姓名語文數學綜合總分張金山659790252雷偉839583261李軍676894229任小霞919385269白松849090264林小平778794258
以下代碼將在工作表選項卡區新建一張chart標籤，
Sub 創建圖表()
Dim cht As Chart
Set cht = Charts.Add '創建圖表物件
With cht
.SetSourceData Source:=Sheets("成績表").Range("A2:D8"), PlotBy:=xlRows
'指定資料來源
.ChartType = xlColumnClustered
.HasTitle = True '添加標題
.ChartTitle.Text = "成績分析圖"
End With
End Sub
圖表工作表物件為Chart物件， 而嵌入到工作表中的圖表物件為ChartObject物件。
Sub 創建嵌入圖表()
Dim cht As ChartObject
On Error Resume Next
ActiveSheet.ChartObjects.Delete '刪除工作表中已有的嵌入圖表
On Error GoTo 0
With Range("G2:L15")
Set cht = ActiveSheet.ChartObjects.Add( _
.Left, .Top, .Width, .Height) '創建新的嵌入圖表
End With
With cht
.Name = "Results" '設置嵌入圖表的名稱
With .Chart '指定資料來源
.SetSourceData Source:=Sheets("成績表").Range("A2:D8"), PlotBy:=xlRows
.ChartType = xlColumnClustered
.SetElement msoElementChartTitleCenteredOverlay '設置圖表標題
.ChartTitle.Text = "成績分析圖"
End With
End With
End Sub
Sub 嵌入圖表轉換為圖表工作表()
Dim cht As ChartObject
On Error Resume Next
Set cht = ActiveSheet.ChartObjects(1)
If cht Is Nothing Then Exit Sub
cht.Chart.Location xlLocationAsNewSheet, "成績分析圖"
End Sub
Sub 圖表工作表轉為嵌入圖表()
Dim cht As Chart, chto As ChartObject
On Error Resume Next
Set cht = Charts("成績分析圖")
If cht Is Nothing Then Exit Sub
cht.Location xlLocationAsObject, ActiveSheet.Name
Set chto = ActiveSheet.ChartObjects(1)
With Range("G2:L15")
chto.Top = .Top
chto.Left = .Left
chto.Width = .Width
chto.Height = .Height
End With
End Sub
4 用ChartTitle物件獲取圖表標題資訊Sub 圖表標題資訊()
Dim chto As ChartObject, cht As Chart
Dim i As Integer, str1 As String
i = 1
For Each chto In ActiveSheet.ChartObjects
Set cht = chto.Chart
If cht.HasTitle Then
With cht.ChartTitle
str1 = "第" & i & "個嵌入圖表的標題資訊：" & vbNewLine & vbNewLine & _
"名稱：" & .Name & vbNewLine & _
"文字：" & .Text & vbNewLine & _
"字體：" & .Font.Name & vbNewLine & _
"字型大小：" & .Font.Size & vbNewLine & _
"顏色：" & .Font.ColorIndex
End With
Else
str1 = "第" & i & "個嵌入圖表沒有標題！"
End If
MsgBox str1
i = i + 1
Next
End Sub
Sub 系列資訊()
Dim chto As ChartObject, cht As Chart, rng As Range
Dim i As Integer, j As Integer, w As Integer
Dim str1 As String, str2 As String, str3 As String
Dim arr1, arr2
arr1 = Array("系列", "X軸資料來源", "Y軸資料來源")
i = 1
For Each chto In ActiveSheet.ChartObjects
Set cht = chto.Chart
str1 = ""
For w = 1 To cht.SeriesCollection.Count
str2 = cht.SeriesCollection(w).Formula '獲取指定系列的公式
str2 = Mid(str2, Len("=SERIES(") + 1) '將公式字串中前面的字元去掉
str2 = Left(str2, Len(str2) - 3) '去掉後面的多餘字元
arr2 = Split(str2, ",") '將系列各部分分解到陣列中
For j = 0 To UBound(arr2) - 1 '獲取各部分的具體值
Set rng = Nothing
On Error Resume Next
Set rng = Application.Evaluate(arr2(j)) '獲取儲存格區域
On Error GoTo 0
If Not rng Is Nothing Then
If j = 0 Then
str3 = rng.Value '系列名稱字串
Else
str3 = rng.Address '資料來源儲存格區域位址
End If
str1 = str1 & arr1(j) & w & ": " & str3 & vbCrLf
End If
Next j
str1 = str1 & vbNewLine
Next w
MsgBox str1 '顯示一個嵌入圖表的系列資訊
i = i + 1
Next
End Sub
Sub 調整圖表資料來源()
Dim myCell As Range
If ActiveChart Is Nothing Then
MsgBox "請選擇需要調整資料來源的圖表！"
Exit Sub
End If
'選擇需要製作圖表的區域
Set myCell = Application.InputBox(prompt:="請選擇調整的資料來源區域。 ", Type:=8)
ActiveChart.SetSourceData Source:=myCell
End Sub
7 用CopyPicture方法將圖表保存為圖片Sub 保存為圖片()
If ActiveChart Is Nothing Then
MsgBox "請選擇需要設置格式的圖表！"
Exit Sub
End If
ActiveChart.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
ActiveWindow.Visible = False
Range("I1").Select
ActiveSheet.Paste
End Sub
8 嵌入圖表也能與使用者交互8.1 類別模組代碼：
Public WithEvents myChartClass As Chart
Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim str1 As String
Select Case ElementID
Case xlChartArea: str1 = "圖表區"
Case xlChartTitle: str1 = "圖表標題"
Case xlPlotArea: str1 = "繪圖區"
Case xlLegend: str1 = "圖例"
Case xlLegendEntry: str1 = "圖例項"
Case xlLegendKey: str1 = "圖例標示"
Case xlAxis: str1 = "坐標軸"
Case xlAxisTitle: str1 = "坐標軸標題"
Case xlMajorGrstr1lines: str1 = "主要格線"
Case xlMinorGrstr1lines: str1 = "次要格線"
Case xlDataLabel: str1 = "資料標籤"
Case xlDataTable: str1 = "資料表"
Case xlDropLines: str1 = "垂直線"
Case xlErrorBars: str1 = "誤差線"
Case xlHiLoLines: str1 = "高低點連線"
Case xlSeries: str1 = "系列"
Case xlSeriesLines: str1 = "系列線"
Case xlShape: str1 = "圖形"
Case xlFloor: str1 = "基底"
Case xlWalls: str1 = "背景牆"
Case xlNothing: str1 = "Nothing"
Case Else:: str1 = "未識別物件"
End Select
MsgBox "你選擇的是： " & str1
End Sub
8.2 模組2代碼：
Dim MyChart As New myChartClass
Sub EnableChartClass()
Set MyChart.myChartClass = Worksheets("成績表").ChartObjects(1).Chart
End Sub
Sub DisableChartClass()
Set MyChart = Nothing
End Sub
8.3 對話模式
I 執行EnableChartClass過程；
II 在嵌入式圖表的子物件上按一下， 即可彈出對話方塊， 顯示子物件名稱。
－End－