エクセルVBAでグラフの元となるデータ範囲を次々と変更するプログラム

photo credit: abbey*christine Pie Charts via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでPowerPointを操作する方法のシリーズを書いていたのですが、少しだけエクセルVBAでグラフを操作するという横道に逸れているところです。

とはいっても、グラフを操作した上でPowerPointに貼り付けていきたいので、きっと役に立つと思います。

さて、前回の記事はコチラ。

初心者でも分かる!エクセルVBAでグラフを操作し取り扱うための超基本の第一歩

今回はエクセルVBAでのグラフの取り扱いについてその超基本についてお伝えします。埋め込みグラフとグラフシートについて、またChartObjectとChartの違いなどについて解説をしています。

エクセルVBAでのグラフの取り扱いについての超基本についてお伝えしました。

今回はいよいよグラフ自体を操作していきます。

エクセルVBAでグラフのデータ範囲を指定する方法です。では、行ってみましょう！

お題の確認
グラフのデータ範囲を変更するプログラム
実行結果
まとめ
連載目次：エクセルVBAでグラフとPowerPointを操作する

お題の確認

今回のお題ですが、前回と引き続きこちらのシートを使います。

このシートですが、右上にグラフがポツンとありますよね。

現在はシートの２行目、北海道の人口の推移を元データとしたグラフになっています。

そのグラフの元となるデータ範囲を、青森、岩手、宮城…といったように自動で一行ずつ移動させていきたいと思います。

今回のプログラム、実行するとなかなか面白いですよ。

グラフのデータ範囲を変更するプログラム

グラフのデータ範囲を変更していくプログラムですが、このように組んでみました。

Sub グラフのデータ範囲の変更()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")

Dim cht As chart
Set cht = ws.ChartObjects("Chart 1").chart

Dim i As Long

i = 3
Do While ws.Cells(i, 1).Value <> ""

    With cht
        .SetSourceData Union(ws.Range("C1:U2"), ws.Range("C1:U1").Offset(i - 1, 0)) 'グラフの対象範囲を変更
        .Refresh 'グラフの表示を更新
    End With

    'グラフの描画のためにWindowsに処理を渡す
    DoEvents
    DoEvents

    i = i + 1
Loop

End Sub

なお、操作対象となるシート名は「Sheet2」、ChartObjectのオブジェクト名は「Chart 1」です。

グラフを操作する場合は、Chartを掴む必要があるので、それをしているのが7行目ですね。これでオブジェクト変数chtで目的のグラフを操作できるようにしています。

以降の部分を以下解説していきます。

SetSourceDataメソッドでグラフのデータ範囲を設定する

グラフのデータ範囲を設定する場合は、Chartに対するSetSourceDataメソッドを使います。

書き方としては

Chartオブジェクト.SetSourceData Rangeオブジェクト

です。

データ範囲ですが、普通にエクセルでグラフを作成するときに見出し範囲とデータ範囲をマウスでドラッグして選択しますよね？

例えば、東京の人口推移のグラフを作成しようと思ったら

このように、見出し範囲（B1:U2）をマウスドラッグで選択して、Ctrl キーを押しながら、データ範囲（B15:U15）をマウスでドラッグします。

VBAのSourceDataメソッドのRangeオブジェクトも、これと同じ選択範囲を指定してあげればOKです。

Unionメソッドで複数のRangeオブジェクトをまとめる

それでVBAで複数の範囲を指定する場合、Unionメソッドを使います。

書き方ですが

Union(Rangeオブジェクト1,Rangeオブジェクト2,…)

このように複数の範囲をカンマ区切りで指定してあげればOKです。これで離れたセル範囲も同一のRangeオブジェクトとして取り扱うことができるようになります。

便利です。

先ほどの、東京の例では

cht.SetSourceData Union(ws.Range("C1:U2"), ws.Range("C15:U15"))

としてあげることで、グラフのデータ範囲を指定することができるわけです。

Offsetプロパティで参照範囲を移動する

今回の例の場合

見出し範囲
データ範囲

この二つの範囲をUnionでまとめたRangeオブジェクトを指定します。

見出し範囲は常にB1:U2で固定で良いのですが、データ範囲はB2:U2、B3:U3、B4:U4、…と移動をしていきます。

このような場合、データ範囲のRangeオブジェクトの指定はどうするのが良いでしょうか？

ちょっとオシャレな方法としてRangeオブジェクトの参照範囲を移動させるOffsetプロパティを使う方法があります。

Offsetプロパティは

Rangeオブジェクト.Offset(行方向の移動数,列方向の移動数)

と書きます。

これで、元のオブジェクトから参照範囲を指定した行数、列数だけ移動することができます。もちろんマイナスの値も指定できますし、移動の必要がないときは0を指定します。

今回は

ws.Range("C3:U3").Offset(i - 3, 0)

としていますから、最初の北海道の位置から、i-3行分を移動した範囲となります。

iとi-3の数の推移をリストしてみると

i=3のときの移動行数：i-3=0
i=4のときの移動行数：i-3=1
i=5のときの移動行数：i-3=2
…
i=49のときの移動行数：i-3=46

となります。沖縄県のグラフのときは、北海道の範囲から46行分移動した範囲となります。

Refreshメソッドで表示を更新する

16行目ですが、ここでRefreshメソッドを使ってデータ範囲を変更した後のグラフの表示を最新に更新しています。

Chartオブジェクト.Refresh

DoEventsを二回入れる理由

グラフの描画をする際ですが、DoEventsを2個並べます。

DoEventsはVBAからWindowsに制御を渡す命令です。ある程度の時間がかかる処理の場合、ループが終わるまで制御が独占されてしまい、例えば画面の再描画なども処理する余地を与えません。

再描画しないとグラフの変化が見られないので、DoEventsを入れているというわけです。そしてグラフの再描画の場合は、なぜかDoEventsが二つ必要ということです。

実行結果

では実行してみましょう！

GIFアニメなので若干飛んでしまっていて恐縮なのですが、次々とグラフが変化していきますね。

グラフの対象範囲を示す青い枠も一行ずつ移動しているのがわかると思います。

まとめ

エクセルVBAでグラフのデータ範囲を指定する方法についてお伝えしました。

グラフのデータ範囲はChartに対するSetSourceDataメソッドで設定することができます。

また、その範囲の指定の際に、複数の範囲をひとつのRangeオブジェクトにまとめるUnionメソッド、Rangeオブジェクトの参照範囲を移動するOffsetプロパティなどが便利に使えました。

さて、次回ですがいよいよグラフをPowerPointに貼り付けていきたいと思います。

簡単！エクセルVBAでシート上のグラフをコピーしてPowerPointにペーストする

エクセルVBAでPowerPointを操作する方法についてお伝えしています。今回はエクセルVBAでエクセル上のグラフをコピーしてPowerPointにペーストをする方法です。とっても簡単ですよ。

どうぞお楽しみに！

連載目次：エクセルVBAでグラフとPowerPointを操作する

エクセルで様々な集計や分析をした結果の表やグラフを、最終的にPowerPointに貼り付け…という作業は結構多いですよね。1つ2つなら良いですが、大量にあった場合は大変です。そんな時にはVBAで自動化をしてしまいましょう！