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

★気に入ったらシェアをお願いします!


charts

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

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

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

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

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

初心者でも分かる!エクセルVBAでグラフを操作し取り扱うための超基本の第一歩
今回はエクセルVBAでのグラフの取り扱いについてその超基本についてお伝えします。埋め込みグラフとグラフシートについて、またChartObjectとChartの違いなどについて解説をしています。

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

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

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

スポンサーリンク

お題の確認

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

エクセルVBAでグラフを作るためのお題シート

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

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

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

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

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

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

なお、操作対象となるシート名は「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オブジェクトとして取り扱うことができるようになります。

便利です。

先ほどの、東京の例では

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

Offsetメソッドで参照範囲を移動する

今回の例の場合

  • 見出し範囲
  • データ範囲

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

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

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

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

Offsetメソッドは

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

と書きます。

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

実行結果

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

2016-10-10-17h54_35

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

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

まとめ

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

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

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

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

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

どうぞお楽しみに!

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

エクセルで様々な集計や分析をした結果の表やグラフを、最終的にPowerPointに貼り付け…という作業は結構多いですよね。1つ2つなら良いですが、大量にあった場合は大変です。そんな時にはVBAで自動化をしてしまいましょう!
  1. エクセルVBAでPowerPointを操作するための準備と最も簡単なプログラム
  2. エクセルVBAでPowerPointのプレゼンテーションを開く方法・スライドを取得する方法
  3. エクセルVBAでPowerPointスライド上のシェイプ一覧を取得する
  4. エクセルVBAでPowerPointの特定のシェイプのインデックス番号を調べる方法
  5. エクセルVBAでPowerPointのテキストボックスに文字を入力する
  6. エクセルVBAでPowerPointでタイトルを変更しながらスライドを大量に生成する
  7. 初心者でも分かる!エクセルVBAでグラフを操作し取り扱うための超基本の第一歩
  8. エクセルVBAでグラフの元となるデータ範囲を次々と変更するプログラム
  9. 簡単!エクセルVBAでシート上のグラフをコピーしてPowerPointにペーストする
  10. エクセルVBAでグラフのデータ範囲を変更しながらPowerPointに連続して貼り付ける
  11. エクセルVBAでグラフタイトルを変更する方法とChartTitleについて
  12. エクセルVBAでグラフの軸の最大値・最小値や軸タイトルを設定する方法