note63

事情

ExcelVBAでグラフを描画する

VBA

卒研でExcel使うときにグラフ描画をできるだけ自動化したかったのでVBAに触りました． 作成したのは左右に軸を持つ散布図です．

Option Explicit
Sub draw_graph()
    Dim i As Integer
    Dim xl_pos, yl1_pos, yl2_pos As Integer
    Dim y_start, y_end, y_temp As Integer
    Dim gpos_x, gpos_y, g_width, g_height As Integer
    Dim chart_title, graph_name As String
    Dim chart_obj As ChartObject
    Dim chart As chart

    ' グラフのデータの開始位置と終わり
    y_start = 1
    y_end = y_start

    ' グラフのサイズ
    g_width = 1000
    g_height = 600

    ' グラフの描画位置
    gpos_x = 600
    gpos_y = 20

    ' 各系列の座標
    xl_pos = 2   
    yl1_pos = 5 
    yl2_pos = 3 

   ' グラフのタイトルと名前の設定
   chart_title = "title"
   graph_name = chart_title

   ' グラフデータの開始位置をずらす
   y_start = y_end + 1

   ' 同じグラフがあったら削除する
   If ActiveSheet.ChartObjects.Count > 0 Then
       For i = 1 To ActiveSheet.ChartObjects.Count
           ' グラフ名が一致するか
           If ActiveSheet.ChartObjects(i).Name = graph_name Then
               ActiveSheet.ChartObjects(i).Delete
               Exit For
           End If
       Next i
   End If

    ' データ範囲を決定
   y_temp = y_start
   Do While Cells(y_temp, 1).Value > 0
       y_temp = y_temp + 1
   Loop
   y_end = y_temp - 1

   ' グラフの挿入
   Set chart_obj = ActiveSheet.ChartObjects.Add( _
       gpos_x, gpos_y, g_width, g_height _
   )
   chart_obj.Name = graph_name
   Set chart = chart_obj.chart

   ' グラフの設定
   With chart
       .ChartType = xlXYScatterLines                   ' 散布図
       .HasTitle = True
       .ChartTitle.Characters.Text = chart_title
       .SeriesCollection.NewSeries                       ' 系列の生成（左縦軸
       .SeriesCollection.NewSeries                       ' 系列の生成（右縦軸
       .Legend.Font.Size = 16                              ' 系列のフォントサイズ
       With .Axes(xlCategory, xlPrimary)
           .HasTitle = True
           .MaximumScale = 100                           ' 横軸の最大値
           .TickLabels.Font.Size = 16
           .AxisTitle.Characters.Text = Cells(1, xl_pos)
           .AxisTitle.Characters.Font.Size = 18
       End With
       With .Axes(xlValue, xlPrimary)
           .HasTitle = True
           .TickLabels.Font.Size = 16                     ' 軸の数値のフォントサイズ
           .AxisTitle.Orientation = 0                      ' 軸タイトルの角度（Default: 90)
           .AxisTitle.Top = 0
           .AxisTitle.Left = 50
           .AxisTitle.Characters.Text = Cells(1, yl1_pos)
           .AxisTitle.Characters.Font.Size = 18
       End With
   End With

   ' 左縦軸の系列設定
   With chart.SeriesCollection(1)
       ' 横軸の値の設定
       .XValues = Range( _
           Cells(y_start, xl_pos), _
           Cells(y_end, xl_pos) _
       )
       ' 縦軸の値の設定
       .Values = Range( _
           Cells(y_start, yl1_pos), _
           Cells(y_end, yl1_pos) _
       )
       .Name = Cells(1, yl1_pos)
       .MarkerStyle = xlMarkerStyleSquare
       .MarkerSize = 7
   End With

   ' 右縦軸の系列設定
   With chart.SeriesCollection(2)
       ' 横軸の値の設定
       .XValues = Range( _
           Cells(y_start, xl_pos), _
           Cells(y_end, xl_pos) _
       )
       ' 縦軸の値の設定
       .Values = Range( _
           Cells(y_start, yl2_pos), _
           Cells(y_end, yl2_pos) _
       )
       .Name = Cells(1, yl2_pos)
       .MarkerStyle = xlMarkerStyleCircle
       .MarkerSize = 7
       ' 右軸にする
       .AxisGroup = xlSecondary
   End With

   ' 右縦軸の設定（ここで設定しないとなぜかエラー）
   With chart.Axes(xlValue, xlSecondary)
       .HasTitle = True
       .MaximumScale = 80
       .TickLabels.Font.Size = 16
       .AxisTitle.Orientation = 0
       .AxisTitle.Top = 0
       .AxisTitle.Left = 800
       .AxisTitle.Characters.Text = Cells(1, yl2_pos)
       .AxisTitle.Characters.Font.Size = 18
   End With
End Sub

これが実際のコードです． 上から順に説明していきます．

Option Explicit

これは変数宣言しないとダメだよというオプションです． VBAはRubyやPythonのように変数宣言なしで使うことができます． ただ僕は初めて触ったのもあってわかりやすくするために宣言必須にしました．

Sub draw_graph()
End Sub

これがプログラムの開始から終わりを表します． C言語でいうところのint main(void){}のようなものだと思えばいいと思います．

Dim i As Integer
Dim xl_pos, yl1_pos, yl2_pos As Integer
Dim y_start, y_end, y_temp As Integer
Dim gpos_x, gpos_y, g_width, g_height As Integer
Dim chart_title, graph_name As String
Dim chart_obj As ChartObject
Dim chart As chart

このへんが変数宣言になります． 僕はやってないですが普通に初期化もできます． DimがJavaScriptでいうところのvarで，JavaScriptと違うのが型の指定が必要になることです．AS IntegerでInteger型の変数という意味になります． ちなみに初期化は

Dim str AS String = "文字列"

とASのあとに初期値を書く必要があり，複数を１行にまとめて変数宣言はできない（はず）です．

If ActiveSheet.ChartObjects.Count > 0 Then
    For i = 1 To ActiveSheet.ChartObjects.Count
        ' グラフ名が一致するか
        If ActiveSheet.ChartObjects(i).Name = graph_name Then
            ActiveSheet.ChartObjects(i).Delete
            Exit For
        End If
    Next i
End If

ここで再度スクリプトを実行した時に前回作成したグラフがあれば削除するようにしています．
ActiveSheetの初期値はスクリプト実行時に開いているシートになります．ChartObjectsがSheet内の表の配列みたいなもので，正確にはChartObjectオブジェクトの集合を管理しているオブジェクトです（このへんがすごくわかりづらい）
ChartObjects(i)とすることでChartObjectsオブジェクト内のi番目のChartObjectオブジェクトを参照することができます．

For i = 1 To 8
Next

ちなみにFor文はこう．
Next iをNextにしても同じ挙動をします．
また，Exit Forがいわゆるbreak文にあたります．

y_temp = y_start
Do While Cells(y_temp, 1).Value > 0
y_temp = y_temp + 1
Loop
y_end = y_temp - 1

ここでデータの範囲を決定しています．
Cellsは行番号と列番号を渡すとその座標の値であったりCellオブジェクトであったりを空気を読んで返します．

Do While
Loop

このようにWhile文を書きます． For文と違いLoopで閉じるあたりは全然慣れないですね．

Set chart_obj = ActiveSheet.ChartObjects.Add( _
gpos_x, gpos_y, g_width, g_height _
)
chart_obj.Name = graph_name
Set chart = chart_obj.chart

ここでグラフをシート内に追加しています．
考え方としてはSheetのもつChartObjectsオブジェクトにChartObjectオブジェクトを追加するという感じです．座標と高さを初期値として渡します．

また，ここでChartオブジェクトを取得しています． このChartObjectオブジェクトとChartオブジェクトが分かれていることの意味がいまいちよくわかりません.

With chart
    .ChartType = xlXYScatterLines                     ' グラフのタイプを散布図に
    .HasTitle = True   
    .ChartTitle.Characters.Text = chart_title 'タイトル指定
    .SeriesCollection.NewSeries                       ' 系列の生成（左縦軸
    .SeriesCollection.NewSeries                       ' 系列の生成（右縦軸
    .Legend.Font.Size = 16                            ' 右に出る凡例のフォントサイズ

    ' 横軸の設定
    With .Axes(xlCategory, xlPrimary) 
        .HasTitle = True
        .MaximumScale = 100                           ' 横軸の最大値指定
        .TickLabels.Font.Size = 16                    ' メモリのフォントサイズ
        .AxisTitle.Characters.Text = Cells(1, xl_pos) ' 横軸のタイトル設定
        .AxisTitle.Characters.Font.Size = 18 
    End With

    ' 左縦軸の設定
    With .Axes(xlValue, xlPrimary)
        .HasTitle = True
        .TickLabels.Font.Size = 16                     
        .AxisTitle.Orientation = 0                    ' 軸タイトルの角度（Default: 90)
        .AxisTitle.Top = 0                            ' 軸のタイトルの表示位置のマージン
        .AxisTitle.Left = 50                          ' 軸のタイトルの表示位置のマージン
        .AxisTitle.Characters.Text = Cells(1, yl1_pos)
        .AxisTitle.Characters.Font.Size = 18
    End With
End With

ここでグラフに対する設定をしています．
With文を使うことでオブジェクトへの処理をひとまとめに記述することができます．
上の例で言うとchartに対する処理は

chart.ChartType = xlXYScatterLines
chart.HasTitle = true

と言った処理をひとまとめに記述しています．

また，AxesはChartオブジェクトの指定したAxisオブジェクト（軸）を返します．
多分ですが一番目に.Axesを実行された際に横軸が参照されます．
それ以降はxlPrimary,xlSecondaryを指定して左右の軸を参照できます．
ここで右軸に対する処理を最後に書いていますが，なぜかこうしないとエラーが出ます．

With chart.SeriesCollection(2)
    ' 横軸の値の設定
    .XValues = Range( _
        Cells(y_start, xl_pos), _
        Cells(y_end, xl_pos) _
    )
    ' 縦軸の値の設定
    .Values = Range( _
        Cells(y_start, yl2_pos), _
        Cells(y_end, yl2_pos) _
    )
    .Name = Cells(1, yl2_pos)
    .MarkerStyle = xlMarkerStyleCircle ' グラフに描画されるマーカーのタイプを指定
    .MarkerSize = 7                    ' マーカーのサイズを指定
    .AxisGroup = xlSecondary           ' 右軸にする
End With

ここで系列についての設定をしています．

だいたいこんなかんじで正直な話苦行でしたｗ
以下のリンクにもありますが，まず正当なリファレンスがないということが大きな障害となりました．
またMSが用意しているリファレンス（のようなもの？）もいろいろと不満だらけな感じだったので辛かったです．

あとあと気づいたけどGoogleAppsScriptで同じことできたんじゃ...

参考