【Excel-VBA活用集】グラフ作成に便利な例文

本記事ではわたしがよく使う例文を紹介していきます。

決められたグラフを多用する、たくさんのグラフを編集したい方向けです。

少しマニアックな２軸のグラフ作成や、軸の細かな設定も紹介します。

さらには色や書式も同時に変更するサンプル例を紹介します。

グラフ全体を変える

よく使うグラフは使い回そう
多彩な色を使った処理が一瞬でできて便利

Dim sample as String 
Dim RGBi(20) As Variant 

With ActiveChart.SeriesCollection(i)
　.Format.Line.ForeColor.RGB = RGBi(r)
　.Format.Fill.ForeColor.RGB = RGBi(r)
　.MarkerBackgroundColor = RGBi(r)
　.MarkerForegroundColor = RGBi(r)
　.Smooth = False .MarkerStyle = xlCircle
　.MarkerSize = 15 .Shadow = False
End With

★使う色は配列に登録しておくと便利
RGBi(1) = RGB(77, 77, 77)
RGBi(2) = RGB(0, 113, 188)
RGBi(3) = RGB(51, 153, 51)
RGBi(4) = RGB(224, 50, 83)
RGBi(5) = RGB(146, 7, 131)

グラフの軸の変更

文字の種類や大きさの変更はお手のもの

軸の最大、最小値などの数値も管理したい時に便利

With ActiveChart.ChartArea.Format.TextFrame2.
TextRange.Font
 .Size = 18
 .NameFarEast = "Arial Narrow"
 .Name = "Arial Narrow"
End With


軸の数値管理も使える

With ActiveSheet.ChartObjects(ChartName(1))
　.Chart.SeriesCollection(1)
　.name = ChartName(1) 
　.Values = jikux(1) 
　.XValues = jikuy(1) 
　.Format.Line.ForeColor.RGB = RGB(1)
　.MarkerStyle = nomaker  
　.Format.Line.Visible = msoTrue 
End With

メモリの最大値・最小値を指定

使いたい時に知っておくと便利

Dim x1MAX As Double 
Dim y1MAX As Double
Dim x1MIN As Double
Dim y1MIN As Double 

x1MAX = 8
y1MAX = 13
x1MIN = 6 
y1MIN = 12 

ActiveChart.Axes(xlValue).MinimumScale = y1MIN
ActiveChart.Axes(xlCategory).MinimumScale = x1MIN 
ActiveChart.Axes(xlCategory).MaximumScale = x1MAX 
ActiveChart.Axes(xlValue).MaximumScale = y1MAX 
ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale = x2MIN 
ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = x2MAX 
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = y2MIN
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = y2MAX 
ActiveChart.Axes(xlCategory, xlSecondary).Select Selection.MajorTickMark = xlInside Selection.MinorTickMark = xlOutside 
Selection.MinorTickMark = xlNone 

With Selection.Format.Line 
.Visible = msoTrue 
.ForeColor.ObjectThemeColor = msoThemeColorText1 
.ForeColor.TintAndShade = 0 
.Transparency = 0 
End With 

ActiveChart.Axes(xlValue, xlSecondary).Select 
Selection.MajorTickMark = xlInside 
Selection.MinorTickMark = xlOutside 
Selection.MinorTickMark = xlNone 

With Selection.Format.Line .Visible = msoTrue 
.ForeColor.ObjectThemeColor = msoThemeColorText1 
.ForeColor.TintAndShade = 0 
.Transparency = 0
End With

２軸にする

使いたい時に知っておくと便利

ActiveChart.SeriesCollection(iend).Select
ActiveChart.SeriesCollection(iend).AxisGroup = 2 
With ActiveChart 
.HasAxis(xlValue, xlSecondary) = True  
.HasAxis(xlCategory, xlSecondary) = True 
End With

◼︎最大値・最小値を指定する

Dim x2MAX As Double
Dim y2MAX As Double 
Dim x2MIN As Double 
Dim y2MIN As Double 

x1MAX = 8 
y1MAX = 13 
x1MIN = 6
y1MIN = 12

ActiveChart.Axes(xlCategory, xlSecondary).MinimumScale = x2MIN 
ActiveChart.Axes(xlCategory, xlSecondary).MaximumScale = x2MAX 
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = y2MIN 
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = y2MAX

◼︎２軸目のメモリを指定する

ActiveChart.Axes(xlValue, xlSecondary).Select
Selection.MajorTickMark = xlInside 
Selection.MinorTickMark = xlOutside 
Selection.MinorTickMark = xlNone 

With Selection.Format.Line 
.Visible = msoTrue 
.ForeColor.ObjectThemeColor = msoThemeColorText1 
.ForeColor.TintAndShade = 0 
.Transparency = 0 
End With

X軸・Y軸メモリを変更する

細かいところまで自動化したい人向け

○X軸のメモリ

ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).HasMajorGridlines = True ActiveChart.Axes(xlCategory).MajorGridlines.Select 

With Selection.Format.Line .Visible = msoTrue 
.DashStyle = msoLineLongDash 
.Weight = 0.25 
End With

○Y軸のメモリ

ActiveChart.Axes(xlValue).Select 
ActiveChart.Axes(xlValue).HasMajorGridlines = True 
ActiveChart.Axes(xlValue).MajorGridlines.Select
 
With Selection.Format.Line .Visible = msoTrue 
.DashStyle = msoLineLongDash 
.Weight = 0.25 
End With

グラフエリア・プロットエリアを変更する

グフフエリアの色指定・線種変更で使える

ActiveChart.ChartArea.Select'グラフシートの色の選択' 
Selection.Interior.ColorIndex = 2 '選択領域の色を白にする' 
ActiveChart.PlotArea.Select'プロットエリアの色の選択' 
Selection.Interior.ColorIndex = 2 '選択領域の色を白にする'
 
ActiveChart.ChartArea.Select 
ActiveSheet.Shapes("").Line.Visible = msoFalse 
ActiveChart.PlotArea.Select 

With Selection.Border   
.LineStyle = xlContinuous
EndWith

また、細かな説明は省いているので初心者の方はこちらを参考にしてくだい。

★オススメの参考書はこちら↓