【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

グラフの軸の変更

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

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

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

★汎用で使えるグラフ用のテンプレも紹介しています↓