「エリア需給実績30分平均値版グラフ」の更新をVBAによりワンアクション化する試行錯誤
1. 目的
前回の試作品でデータ更新するには、
1) スライサーで「年月」と「日」を選んで、
2) ツール - すべて更新
と、少なくとも 2 つのアクションが必要だった。
これを VBA によりワンアクション化してみる。
以下の 3 つのユーザー操作、
スライサー「年月」の変更
スライサー「日」の変更
ボタン「最新日更新」の押下
のそれぞれに対して、クエリー更新して目的の年月日のグラフを描画させるなどの自動処理。
2. 前回「試作品」のクエリー依存関係
復習の意味で、クエリー依存関係を確認する。
左列・・・元の Web ページ URL（最上行の中列 URL）から、データのリンク URL リストを作るまで。
ここから年月選択のピボットテーブルとスライサーを作っている。中列・・・左列とは直接の依存はなく、ワークシート関数を介して選択された月のデータリンク URL（最上行の右列 URL）を読み込んで、当月30分刻みカレンダーと合体させ、グラフ集計のためのピボットテーブルを作成している。
さらに、グラフ表示に呼応して、グラフの下に表示する、値を検索するための月次・日次クエリーテーブルも別途作成。右列・・・読み込んだ月のデータから分岐して、最終年月日時間帯を取得するクエリー。
「値を検索するための月次・日次テーブル」とは、グラフ集計用のピボットテーブルとは別にしている。理由は、
1) グラフを見ながら30分単位の細かい数値をフィルターなどで検索したい
2) マクロでピボットテーブルの変更イベントを使うので、数値検索とは切り分けたい
ため。
3. 課題
単純に「すべての更新」相当の、
ActiveWorkbook.RefreshAll
としただけでは期待した動作はしないので、必要な処理を分解して動作順に整理してみた。
3 つのユーザー操作に対して、以下の 9 つのプロシージャのイメージ。
プロシージャ：chgFirstD の「1日指定」は、異なる年月を選択したとき、例えば 2 月に既存で選択中の「31 日」がないなどで「日」スライサーやグラフの表示がおかしくなるため、強制的に「1 日」を選択させる。
プロシージャ：chgLastYM の「最終月指定」は「最新日更新」の前処理で、グラフで過去月を表示している状態から今日の最新グラフにしたいとき、まず読み込むデータファイルを今月のものに読み替える必要があるため。
グラフでデータリンク URL リストの最新月を表示している状態からであれば、この動作はスルーさせる。（判断方法は 4. で後述）プロシージャ：chgLastD の「最終日指定」は、久しぶりにこの Book を開いて「最新日更新」ボタンを押したとき、公開されているデータの最新日をグラフに表示させたいため。
進行中のその日の最新データを読み込む時もこのボタンを押す。読み込んだデータの最終年月日時間帯はワークシート名前定義で取得済みなので、ここから VBA 関数で「日」だけ取り出す。
3 つの操作のいずれも、画面抑止／解除と Book の上書き保存の動作を入れる。
4. 名前定義を 1 つ追加
「最新日更新」操作の前処理で、年月スライサーで選択中の値とは別に、公開されているデータファイルの最新年月がいつなのかを名前定義で取得しておく必要がある。
これを sheet ： 2_DL_URL_List に R_LastYM （"yyyy/mm" の文字列形式）として追加しておく。
取得する値は、「新しい順」= 1 の時の「年月」を INDEX・MATCH 関数で。なお、年月スライサーで選択中の値は既存の定義名 R_YM に日付型データの "yyyy/m" 形式で表示しているが、VBA の中でこれを Format 関数で "yyyy/mm" の文字列形式に変換して、R_LastYM と比較する。
（5. プロシージャ：chgLastYM の If Then End If の部分）
5. 標準モジュールにセットする 7 つのプロシージャ
最後のプロシージャ：Uptodate は、「最新日更新」ボタンにマクロとして紐付けるもの。
他の 8 つのプロシージャは、ピボットテーブルのスライサーを変更した時と共通する処理。
6. 月次集計ピボットテーブルのシートでイベント処理するシートモジュール
試作品の年月スライサーの元のピボットテーブルは、
Sheet3 (3_PT_YM)
にあるので、このシートモジュールに記述。
スライサーの選択値変更により、元のピボットテーブルが変更になるので、
Worksheet.PivotTableChangeSync イベント を使う。
7. 日次集計ピボットテープのシートでイベント処理するシートモジュール
試作品の日スライサーの元のピボットテーブルは、
Sheet6 (3_PT_Daily)
にあるので、このシートモジュールに記述。
同じイベントを使う。
8. これで様子見
最終的な日次グラフのワークシートは以下のように。
これで月日変更、日変更、最新日更新、これらの連続操作、日付またぎなどで上手く動くか、しばらく様子を見る。
今回、マクロの記録やネットからの拾いもののつなぎ合わせで、結果的にどうにか動くようにはなったが、今後の課題として、スライサー（ピボットテーブルのフィルター）の値を変更するために使った 2 種類の記述、
例1) プロシージャ：chgFirstD 内での、
ThisWorkbook.SlicerCaches("スライサー_日").VisibleSlicerItemsList = Array("[Q_SUM_Monthly].[日].&[1]")
例2) プロシージャ：chgLastYM 内での、
pt3.PivotFields("年月").CurrentPage = LastYM
をどう使い分けたら良いのか、整理が必要。
この記事が気に入ったらサポートをしてみませんか？