EXCELのグラフの軸の書式設定でハマる

 オブジェクトブラウザーは VBA の羅針盤である.しかし時に役に立たないことがある.今回はグラフの軸の書式設定でハマった経緯を備忘録として記しておく.

マクロの記録から

 VBA の挙動を調べるにはマクロの記録から入るのが普通である.使われているオブジェクトを特定し,オブジェクトブラウザーからプロパティとメソッドを調べてオブジェクト間の繋がりを丹念に紐解いていく.面倒でもこの作業は欠かせない.

ユーザーインターフェース

 Y 軸を選択した状態で右クリックして「軸の書式設定…」を選ぶ.

Y軸を選択した状態で右クリックして「軸の書式設定」を選ぶ
Y軸を選択した状態で右クリックして「軸の書式設定」を選ぶ

軸の書式設定

 「軸のオプション」パネルが開く.ここはデータの本質にかかわる作業となる.下図に Axis オブジェクトのプロパティを記しておく.ここでは軸の最小値,最大値,単位,横軸との交点,表示単位,対数目盛の表示,軸の反転について設定する.

「軸の書式設定」の「軸のオプション」で最小値,最大値,単位,横軸との交点,表示単位,対数目盛の表示,軸の反転について設定する
「軸の書式設定」の「軸のオプション」で最小値,最大値,単位,横軸との交点,表示単位,対数目盛の表示,軸の反転について設定する

軸のフォントの設定

 Y 軸を選択した状態で右クリックし「フォント…」を選ぶ.

Y軸を選択した状態で右クリックし「フォント...」を選ぶ
Y軸を選択した状態で右クリックし「フォント…」を選ぶ

 「フォント」ダイアログが開く.色が白色であることを確認するだけなので特に設定はしない.

「フォント」ダイアログ
「フォント」ダイアログ

最初のコードが動かない!

 下のコードがマクロの記録で得られたものである.重要なオブジェクト間の繋がりは 3 行目と 4 行目,さらに 12 行目にありそうだ.

 実際には 4 行目から 11 行目では軸のフォントの色を変更し,12 行目から 15 行目では軸のフォントを変更している.

 しかし,このコードをそのまま実行しても実行時エラーが返ってくる.

実行時エラー'-2147467259(80004005)' 'TextFrame2' メソッドは失敗しました.'ChartFormat' オブジェクト
実行時エラー’-2147467259(80004005)’ ‘TextFrame2′ メソッドは失敗しました.’ChartFormat’ オブジェクト

 デバッグ画面に切り替わり,エラーの出たコードがハイライトされている.

エラーの出たコードがハイライトされている
エラーの出たコードがハイライトされている

問題の切り分けにはオブジェクトを一つづつ切り分ける

 実行時エラーは大抵の場合,オブジェクトの取得がうまく行っていない時に起こりやすい.そこで,TypeName 関数を使ってどこで取得に失敗しているのか切り分ける作業に入る.

 最後のオブジェクトから始める人と,最初のオブジェクトから始める人がいる.どちらでも構わないが,俺は最初のオブジェクトから始める方だ.

Select と Selection は消す

.Select と Selection は消す
.Select と Selection は消す

 マクロの記録に特有の記述だ.頭に With をつけて,ここはさっさと消してしまう.これは形式的作業だ.

Select と Selection を消し,With 句で1行のコードに書き換える
Select と Selection を消し,With 句で1行のコードに書き換える

TypeName 関数でオブジェクトを取得できているか調べる

 上記コードのオブジェクト数は 7 個ある.そこで TypeName 関数を 7 個並べる.

 次に最初のオブジェクトから始めて,一つずつオブジェクトを増やしていく.次のようになる.

結果

 7 行目の TextFrame2 オブジェクトの取得に失敗する.

ChartFormat オブジェクトまでは取得できているが TextFrame2 オブジェクトの取得に失敗している
ChartFormat オブジェクトまでは取得できているが TextFrame2 オブジェクトの取得に失敗している

ChartFormat オブジェクトと TextFrame2 オブジェクトが繋がらない!

 オブジェクトブラウザーからは確かに繋がっており,コンパイルエラーも発生していないのに,実行してみるとエラーとなる.何かがおかしい.

 何度か試行錯誤してみたが,どうもこの経路は行き止まりのようで埒があかない.

 そもそも VBA でオブジェクトを取得するのに唯一の経路しか存在しないということはまれで,普通はいくつかの複数の経路が存在する.他の経路から辿れないか?こういう時は検索してみる.

Google 検索

 検索キーワードはもちろん “ChartFormat” “TextFrame2” の AND 検索である.ダブルクオーテーションで囲うのは完全一致を意図している.どうやら他にも意図せざるエラーで悩んでいる人がいるらしいことが分かる.

Excelで作成したグラフの縦軸,横軸の目盛り(数値)のフォントを変えるマクロがうまくいかない

ExcelVBAでグラフのサイズやフォント種類を変更

Chart オブジェクトと ChartFormat オブジェクトの間にいた伏兵,それは ChartArea オブジェクト

 見落としていたオブジェクトである.オブジェクトブラウザーからは見つけられなかった.これを間に入れることで実行時エラーが解消された.

 オブジェクトは

 と繋がる.

 オブジェクト名から想像できるように,この設定はグラフ全体に及ぶ.軸を特定していないからである.

TickLabels オブジェクト

 軸を特定して書式設定したい場合には TickLabels オブジェクトを取得する.

と繋がる.

テストコード

 オブジェクトを順につないでいく.確認のために,取得するたびにオブジェクトの種類をイミディエイトウィンドウに出力している.宣言している変数がすべて Object 型であることに注意されたい.

テスト結果

 間違いなくオブジェクトがすべて取得できていることが分かる.

『EXCEL VBAできる大事典』で発見

 EXCEL VBAできる大事典 の「グラフの軸を設定するには (p678)」に記述があった.Chart オブジェクトと Axis オブジェクトとの間に ChartFormat オブジェクトがないのが気になる.

テストコードその2

 気になるのでテストしてみる.

 どうやら取得できているようだ.

再びオブジェクトブラウザーに戻る

TickLabels オブジェクト

 TickLabels はグラフ軸の目盛りにアクセスするためのオブジェクトである.下記はオブジェクトブラウザーで調べたメンバーの一覧である.

CLASS MEMBER Type of Return
TickLabels Alignment Long
  Application Application
  Creator XlCreator
  Delete Function
  Depth Long
  Font Font
  Format ChartFormat
  MultiLevel Boolean
  Name String
  NumberFormat String
  NumberFormatLinked Boolean
  NumberFormatLocal Variant
  Offset Long
  Orientation XlTickLabelOrientation
  Parent Object
  ReadingOrder Long
  Select Function

Axis オブジェクト

 グラフの軸を操作するには Axis オブジェクトを取得する.

CLASS MEMBER Type of Return
Axis AxisBetweenCategories Boolean
  AxisGroup XlAxisGroup
  AxisTitle AxisTitle
  BaseUnit XlTimeUnit
  BaseUnitIsAuto Boolean
  Border Border
  CategoryNames Variant
  CategorySortOrder XlCategorySortOrder
  CategoryType XlCategoryType
  Creator XlCreator
  Crosses XlAxisCrosses
  CrossesAt Double
  Delete Function
  DisplayUnit XlDisplayUnit
  DisplayUnitCustom Double
  DisplayUnitLabel DisplayUnitLabel
  Format ChartFormat
  GetProperty Function
  HasDisplayUnitLabel Boolean
  HasMajorGridlines Boolean
  HasMinorGridlines Boolean
  HasTitle Boolean
  Height Double
  Left Double
  LogBase Double
  MajorGridlines Gridlines
  MajorTickMark XlTickMark
  MajorUnit Double
  MajorUnitIsAuto Boolean
  MajorUnitScale XlTimeUnit
  MaximumScale Double
  MaximumScaleIsAuto Boolean
  MinimumScale Double
  MinimumScaleIsAuto Boolean
  MinorGridlines Gridlines
  MinorTickMark XlTickMark
  MinorUnit Double
  MinorUnitIsAuto Boolean
  MinorUnitScale XlTimeUnit
  Parent Object
  ReversePlotOrder Boolean
  ScaleType XlScaleType
  Select Function
  SetProperty Sub
  TickLabelPosition XlTickLabelPosition
  TickLabels TickLabels
  TickLabelSpacing Long
  TickLabelSpacingIsAuto Boolean
  TickMarkSpacing Long
  Top Double
  Type XlAxisType
  Width Double

軸の交点は Axis.Crosses プロパティで設定する

 マクロの記録だけでは気がつかない罠の一つである.オブジェクトブラウザーを丹念に見直して気がついた.

 グラフによっては X 軸を上端に寄せたいとか Y 軸を左端に寄せたいとかいう要望もあるだろう.その時はこのプロパティを設定するのである.

MEMBER VALUE Description
xlAxisCrossesAutomatic -4105 交差する点は自動
xlAxisCrossesCustom -4114 CrossesAtプロパティの値で交差
xlAxisCrossesMaximum 2 最大値で交差
xlAxisCrossesMinimum 4 最小値で交差

対数軸は Axis.ScaleType プロパティで設定する

 軸を線形で表示するか対数で表示するかはこのプロパティで設定する.

MEMBER VALUE Description
xlScaleLinear -4132 Linear
xlScaleLogarithmic -4133 Logarithmic

Font オブジェクトと Font2 オブジェクト

 オブジェクトブラウザーでプロパティを調べてみると,両者の違いが何となく分かってくる.Font オブジェクトのプロパティはほとんどが Variant 型であるのに対して,Font2 オブジェクトのプロパティは型が決まっている.

 恐らく初期に実装した Font オブジェクトの仕様を変更できずに,新しく Font2 オブジェクトを作ることにしたのだろう.プロパティの数だけでも Font2 オブジェクトが 36 個に対して Font オブジェクトは 18 個しかない.

 フォントについては後日別記事で取り上げることにする.

テストコードその3

 これまで見てきたオブジェクトのメンバーを出力している.

 イミディエイトウィンドウには下記のように出力される.出力結果は各自の環境で異なる.Y 軸は対数表記にしてあるため .LogBase プロパティがエラーにならない.

Chart.SetElement メソッドを使いこなせ

 Microsoft Excel 2019 VBA and Macros (Business Skills) の p 314 以降に記述があるが,Chart オブジェクトの .SetElement メソッドを使うと,列挙型を指定することでグラフのオブジェクトを指定せずとも一発で設定できる.

 例えば Y 軸を対数表記にしたい場合は,下記のように記述するのが普通だ.

 ところが,Chart.SetElement メソッドを使うと Axis オブジェクトを取得する必要がない.

 列挙型を知っていると,こういうチートが使える.MsoChartElementType という言葉をオブジェクトブラウザーで検索してみてほしい.設定できるオブジェクトは以下のようだ.

  • Axis
  • AxisTitle
  • DataTable
  • ErrorBar
  • Gridline
  • Legend
  • Line
  • PlotArea
  • Trendline
  • Up/DownBars

One Reply to “EXCELのグラフの軸の書式設定でハマる”

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください