VBAサンプル集
グラフで特定の横軸の色を変更し基準線を引くマクロ

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
公開日:2013-09-03 最終更新日:2019-11-02

グラフで特定の横軸の色を変更し基準線を引くマクロ


特定の横軸のみ色を変更する、つまり、基準値や下限・上限等に線を引きたい場合のマクロVBAになります。
手動でやろえとすると結構面倒なので、いざやろうとするとなかなか出来なかったりします。


グラフの元データに線を引きたい数値の系列をシートに追加しておくと、もう少し楽にできますが、
ここでは、シートには手を加えずに基準線を引いています。

手動でやる方法については、以下に掲載しています。

グラフで特定の横軸の色を変更し基準線を引く
特定の横軸のみ色を変更する、つまり、基準値や下限・上限等に線を引く場合の手順です 基準値や、下限・上限等に線を引きたい場合になります。結構面倒なので、いざやろうとすると、なかなか出来なかったりしますので、自分自身の覚書を兼ねて掲載しておきます。

今回は、これをマクロVBAで行います。


マクロ VBA グラフ 基準線

あらかじめ棒グラフがあるものとして、そこに赤の規準線(ここでは平均値)を引くマクロVBAになります。
上記サンプルでは2系列となっていますが、系列数は問いません、自動で行います。

グラフで特定の横軸の色を変更する(基準線を引く)VBA

Sub sample()
  Dim TitleHas As Boolean
  Dim TitleFormula As String
  Dim 基準値 As Double
  Dim vTemp, v1, v2
  Dim i As Long
  
  'シートの先頭グラフ
  Dim obj As Chart
  Set obj = ActiveSheet.ChartObjects(1).Chart
  With obj
    '基準値決定:平均値を求める
    For i = 1 To .SeriesCollection.Count
      'シート名に,カンマは含んでいない前提です
      vTemp = Split(.SeriesCollection(i).Formula, ",")
      vTemp = vTemp(UBound(vTemp) - 1)
      v1 = v1 + WorksheetFunction.Sum(Range(vTemp))
      v2 = v2 + WorksheetFunction.Count(Range(vTemp))
    Next
    If v2 <> 0 Then 基準値 = v1 / v2
    
    '系列追加でタイトルが消える事への対応
    TitleHas = .HasTitle
    If TitleHas Then TitleFormula = .ChartTitle.Formula
    
    '基準線を新規系列として追加
    With .SeriesCollection.NewSeries
      .Name = "=""規準""" '特に必要はない
      '.Values = "={基準値,基準値}"
      .Values = "={" & Join(Array(基準値, 基準値), ",") & "}"
      .ChartType = xlLine '折れ線
      .AxisGroup = 2 '第2軸
      .Format.Line.ForeColor.RGB = vbRed 'RGB(255, 0, 0)
    End With
    
    '系列追加で消えたタイトルの復元
    .HasTitle = TitleHas
    If TitleHas Then .ChartTitle.Formula = TitleFormula
    
    '第2縦軸のScaleを第1縦軸に合わせる
    .Axes(xlValue, 2).MinimumScale = .Axes(xlValue, 1).MinimumScale
    .Axes(xlValue, 2).MaximumScale = .Axes(xlValue, 1).MaximumScale
    .Axes(xlValue, 2).Delete '第2軸を消す
    
    'グラフ内左右余白を消す:基準線を第2横軸に設定
    .HasAxis(xlCategory, 2) = True '第2横軸表示
    .Axes(xlCategory, 2).AxisBetweenCategories = False '軸位置を目盛
    .Axes(xlCategory, 2).TickLabelPosition = xlNone '目盛ラベルなし
    
    '基準線の凡例を消す
    On Error Resume Next '凡例が無いときの対応
    .Legend.LegendEntries(.SeriesCollection.Count).Delete
  End With
End Sub

基準線を引くマクロVBAの解説

以下、基準線を引くマクロVBAの中から、要点・注意点のみ解説します。

ActiveSheet.ChartObjects(1)
シートの先頭グラフになります。
複数のグラフがある場合は適宜変更してください。

基準値決定:平均値を求める
グラフの元データから平均値を求めています。
簡易的に,カンマで区切ってデータ範囲を取得していますので、
あまり無いとは思いますが、'シート名に,カンマを含んでいると正しく取得できません。
また、クラフのデータ範囲として固定数値を直接指定しているとエラーとなります。

.SeriesCollection(i).Formulaの値は、
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$13,Sheet1!$B$2:$B$13,1)
このような文字列になりますので、カンマ区切りで後ろから2番目がデータ範囲になります。
この文字列(Sheet1!$B$2:$B$13)を取り出して、Rangeの引数に入れてRangeオブジェクトにしています。

あくまで、基準値を求める一つのサンプルになります。
あらかじめ決められた基準値なら、変数(基準値)にその値を入れてください。
シート上で数式により計算していれば、そのセル値を変数(基準値)に入れてください。

基準線を新規系列として追加
.SeriesCollection.NewSeries
これで系列を追加しています。

Valuesには、
.Values = "={基準値,基準値}"
このように設定しますので、この文字列を作成してValuesに入れています。

そして、折れ線に設定し、第2軸に設定しています。
色の指定は、vb定数でもRGB関数でも有効な指定なら何でも構いません。

系列追加でタイトルが消える事への対応と復元
系列を追加すると、タイトルが消えてしまいますので、
系列追加前に情報を退避しておき、系列追加後に戻しています。
タイトルには、数式を設定できますので、Formulaを使っています。

マクロ VBA グラフ 基準線

第2縦軸のScaleを第1縦軸に合わせる
基準線を第2縦軸に設定するので、第2縦軸の最大値と最小値を第1縦軸と同じにしています。
そして、第2縦軸の表示は必要ないので消しています。

, 2)や, 1)は、縦軸の数値です。
第1縦軸は、xlPrimary=1
第2縦軸は、xlSecondary=2
上記VBAでは、掲載コードの見やすさを考慮して数値リテラルで指定しています。

グラフ内左右余白を消す:基準線を第2横軸に設定
基準線を横幅いっぱいに広げるために、
・第2横軸に設定
・軸位置を目盛
・目盛ラベルなし

基準線の凡例を消す
凡例に追加した基準線の系列が表示されてしまうので、これを消しています。
もちろん、凡例に表示されていた方が良ければ消さずに残してください。

ほぼ全て自動になっていますので、大抵の場合は修正せずに使えると思います。
手動で一つずつ設定するよりは間違いなく早いはずです。
特に複数グラフで設定したい場合には有効だと思いますので、VBAでできる事だけは覚えておくと良いでしょう。



同じテーマ「マクロVBAサンプル集」の記事

図を確認しながら消していく(Shape)
オートシェイプを他ブックの同じ位置に貼り付ける(Shapes,DrawingObjects)
全シートの画面キャプチャを取得する(keybd_event)
写真の取込方法について(Pictures.Insert,Shapes.AddPicture)
写真をサムネイルに変換して取り込む(Shapes.AddPicture)
円グラフの色設定(Chart,SeriesCollection)
棒グラフ・折れ線グラフのサンプルマクロ
人口ピラミッドのグラフをマクロで作成
グラフで特定の横軸の色を変更し基準線を引くマクロ
グラフのデータ範囲を自動拡張するマクロ
画像のトリミング(PictureFormat,Crop)


新着記事NEW ・・・新着記事一覧を見る

ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ