グラフの種類を取得する自作関数
グラフの種類を取得するにはChartTypeというプロパティを使う。
これを使うと集合縦棒(xlColumnClustered)は54、積み上げ縦棒(xlColumnStacked)は55といったグラフの詳細を表す数値が返される。
そういう詳細ではなく、縦棒とか横棒、折れ線といったグラフの種類（グループ）を取得するにはどうすればいいのか？いろいろ検索してみたが答えが見つからなかったので、自分で関数を作ってみた。
Function CheckGraphType(ChartTypeValue As Integer)
Select Case ChartTypeValue Case -4100, 51, 52, 53, 54, 55, 56 '縦棒グループ CheckGraphType = 1 Case 57, 58, 59, 60, 61, 62 '横棒グループ CheckGraphType = 2 Case -4101, 4, 63, 64, 65, 66, 67 '折れ線グループ CheckGraphType = 3 Case -4102, 5, 68, 69, 70, 71 '円グループ CheckGraphType = 4 Case -4169, 72, 73, 74, 75 '散布図グループ CheckGraphType = 5 Case -4098, 1, 76, 77, 78, 79 '面グループ CheckGraphType = 6 Case -4120, 80 'ドーナツグループ CheckGraphType = 7 Case -4151, 81, 82 'レーダーグループ CheckGraphType = 8 Case 83, 84, 85, 86 '等高線グループ CheckGraphType = 9 Case 15, 87 'バブルグループ CheckGraphType = 10 Case 88, 89, 90, 91 '株価グループ CheckGraphType = 11 Case 92, 93, 94, 95, 96, 97, 98 '円柱グループ CheckGraphType = 12 Case 99, 100, 101, 102, 103, 104, 105 '円錐グループ CheckGraphType = 13 Case 106, 107, 108, 109, 110, 111, 112 'ピラミッドグループ CheckGraphType = 14 Case -4111 'ユーザー定義等 CheckGraphType = 15 Case Else CheckGraphType = 0 End Select
End Function
何故、グラフの詳細ではなくグラフの種類を取得したいのか？
それはChartTypeが縦棒グループのグラフのときはXXXという処理、折れ線グループのグラフのときはxxxという処理というようにプログラムを分岐させたいと思ったから。例えばグラフに色をつける場合、縦棒や横棒はInterior.ColorIndexを使うが、折れ線だとBorder.ColorIndexを使う。基本的に同じ種類のグラフは設定方法が同じ（はず）なので、グラフの種類を特定して分岐すればコードがすっきりするはず・・・と思ったのだが、どうでしょう？
ChartTypeの値のリストはMSDN Libraryを参照。
Microsoft Excel Constants [Excel 2003 VBA Language Reference]
ピボットグラフの色を固定する（その１）
ピボットテーブルから作成したグラフは、その後グラフの色を変更しても、ピボットを変更したり系列を一部非表示にするなどすると、グラフが標準の設定にリセットされてしまう。変更した書式をユーザー定義として登録するか標準の書式に設定する方法もあるが、ファイルを共有して使っている場合、それだと問題がある。
そこで、ワークシートのPivotTableUpdateというイベントを利用し、ピボットが更新されてもグラフの色をそのままにするコードを作ってみた。
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim r As Object, r1 As Object
'シートにグラフがあるかどうかをチェック If ActiveSheet.ChartObjects.Count > 0 Then For Each r In ActiveSheet.ChartObjects 'グラフの種類が縦棒グループか横棒グループの場合の設定 If CheckGraphType(r.Chart.ChartType) = 1 Or CheckGraphType(r.Chart.ChartType) = 2 Then ChartObjects(r.Name).Activate For Each r1 In ActiveChart.SeriesCollection '系列が1つ以上ある場合、一番目の系列を赤にする If ActiveChart.SeriesCollection.Count >= 1 Then ActiveChart.SeriesCollection(1) _ .Interior.ColorIndex = 3 '系列が2つ以上ある場合、最後の系列を青にする If ActiveChart.SeriesCollection.Count >= 2 Then ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count) _ .Interior.ColorIndex = 5 End If End If Next 'グラフの種類が折れ線グループの場合 ElseIf CheckGraphType(r.Chart.ChartType) = 3 Then ChartObjects(r.Name).Activate For Each r1 In ActiveChart.SeriesCollection If ActiveChart.SeriesCollection.Count >= 1 Then ActiveChart.SeriesCollection(1).Border.ColorIndex = 3 If ActiveChart.SeriesCollection.Count >= 2 Then ActiveChart.SeriesCollection(ActiveChart.SeriesCollection.Count) _ .Border.ColorIndex = 5 End If End If Next End If Next End If
End Sub
上記のサンプルは、シート上にあるグラフの種類をチェックし、グラフの種類が縦棒グラフ、横棒グラフまたは折れ線グラフの時、一番目の系列を赤に、一番最後の系列を青にするというもの。縦棒グラフ・横棒グラフと折れ線グラフでコードを分けているのは、色の設定が前者はInterior.ColorIndexなのに対し後者がBorder.ColorIndexだから。コード内では色の設定しかしていないので、必要に応じて背景とか書式などの設定を追加すること。
コード内のCheckGraphTypeは選択しているグラフの種類を取得する自作関数。
ピボットグラフの色を固定する（その２）
その１で紹介したサンプルコードではグラフの系列の番号を指定して色を設定した。今回は系列名を指定して色を設定する方法。ほんの数行違うだけで、基本はほとんど同じ。
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim r As Object, r1 As Object
If ActiveSheet.ChartObjects.Count > 0 Then For Each r In ActiveSheet.ChartObjects 'グラフの種類が縦棒グループか横棒グループの場合の設定 If CheckGraphType(r.Chart.ChartType) = 1 Or CheckGraphType(r.Chart.ChartType) = 2 Then ChartObjects(r.Name).Activate For Each r1 In ActiveChart.SeriesCollection '系列名が東京またはデスクトップの場合の設定 If r1.Name = "東京" Or r1.Name = "デスクトップ" Then ActiveChart.SeriesCollection(r1.Name) _ .Interior.ColorIndex = 3 '系列名が大阪またはラップトップの場合の設定 ElseIf r1.Name = "大阪" Or r1.Name = "ラップトップ" Then ActiveChart.SeriesCollection(r1.Name) _ .Interior.ColorIndex = 5 '系列名が福岡または携帯電話の場合の設定 ElseIf r1.Name = "福岡" Or r1.Name = "携帯電話" Then ActiveChart.SeriesCollection(r1.Name) _ .Interior.ColorIndex = 26 End If Next 'グラフの種類が折れ線グループの場合 ElseIf CheckGraphType(r.Chart.ChartType) = 3 Then ChartObjects(r.Name).Activate For Each r1 In ActiveChart.SeriesCollection If r1.Name = "東京" Or r1.Name = "デスクトップ" Then ActiveChart.SeriesCollection(r1.Name) _ .Border.ColorIndex = 3 ElseIf r1.Name = "大阪" Or r1.Name = "ラップトップ" Then ActiveChart.SeriesCollection(r1.Name) _ .Border.ColorIndex = 5 ElseIf r1.Name = "福岡" Or r1.Name = "携帯電話" Then ActiveChart.SeriesCollection(r1.Name) _ .Border.ColorIndex = 26 End If Next End If Next End If
End Sub
上記のサンプルは系列名が東京かデスクトップの時はグラフの色を赤に、大阪かラップトップの時は青に、福岡か携帯電話の時は紫にというコード。
VBAではなく一般機能で設定したい場合はEXCEL/一般機能/グラフをどうぞ。