Excelのグラフの位置・サイズをVBAで調整したときに微妙なズレが生じた時の解決方法

土曜日 , 20, 9月 2014 1 Comment

Excelは非常に便利なツールです。
便利すぎて表計算に使う人よりも画像貼り付けツールや帳票出力ツールとして使っている人が多いんじゃないかと思います。
さて、そんなExcelですが 帳票出力ツールとして活用し、「ワークシート」を「ページ」に見立てて出力を行っていくと、ときどき困った現象が発生してドはまりします。
今回ドはまりしたのは、グラフを出力したときに位置や大きさがワークシートごとにまちまちになってしまうと言う現象です。

サンプル画像では、同じように座標指定を行ったグラフが微妙にズレた状態を示しています。
設定上は、グラフの開始位置がP列にビシッと揃うようにしているはずなのですが、上のグラフはP列の開始位置にビシッと揃っていますが下のグラフでは横方向に数ドットずれています。
グラフのズレ

この程度のズレは、個人的にはまったく気にしない範囲ですw
しかし、非常に気にする人もいらっしゃるので、VBAからグラフのTopプロパティ、Leftプロパティ、 Heightプロパティ、Widthプロパティへダイレクトに値を設定することで解決を試みてみます。

    Dim ObjXlWorksheet As Excel.Worksheet
    Dim ObjChartObject As excel.ChartObject

    ' ワークシートの設定処理など...

    Set ObjChartObject = ObjXlWorksheet.ChartObjects(ChartIndex)
    
    ObjChartObject.Width = 幅
    ObjChartObject.Height = 高さ
    ObjChartObject.Left = 開始位置X
    ObjChartObject.Top = 開始位置Y

処理結果を見ると、効いたり効かなかったりで解決できません。
ChartObject、Chart、ChartArea、PlotArea等々のそれっぽいオブジェクトの位置・大きさに関わるプロパティをいじってみましたが、同様の結果です。 

いろいろと試した結果、最終的に効いたのは、プロパティを操作する前に対象となるグラフを含むワークシートをアクティブシートにしてまうという方法でした。

    Dim ObjXlWorksheet As Excel.Worksheet
    Dim ObjChartObject As excel.ChartObject

    ' ワークシートの設定処理など...

    ObjXlWorksheet.Activate    ' ここでアクティブシート化
    DoEvents

    Set ObjChartObject = ObjXlWorksheet.ChartObjects(ChartIndex)
    
    ObjChartObject.Width = 幅
    ObjChartObject.Height = 高さ
    ObjChartObject.Left = 開始位置X
    ObjChartObject.Top = 開始位置Y

とりあえず”おやくそく”として入れたDoEventsに哀愁を感じますが、結果はバッチリ揃いました。
動作を観察していると、グラフそのものの位置・大きさプロパティを設定した以外に、内包するPlotArea、Legend、ChartTitleなど関連オブジェクトのプロパティを設定したタイミングでもグラフの再描画(位置・大きさの再計算)が行われるようです。アクティブ状態と非アクティブ状態ではこのあたりの挙動が異なるように見受けられます。MSDNなどでこれを制御するプロパティがないか回避策がないか探してみたのですが、どうもなさそうです。

しかし、このアホみたいな解決方法に気づくまで費やした時間はいったい何だったのでしょう…orz
Excelは奥が深いですね。 

Tags:,
One thought on “ : Excelのグラフの位置・サイズをVBAで調整したときに微妙なズレが生じた時の解決方法”
  • omitsu30 より:

    グラフを描画した後に
    一度、「切り取り」して
    改めて正確に貼り付けたいセルを選択して
    張り付けると
    「ビシッと」揃いますよ。
    .ScreenUpdating = False
    にしないと時間がかかりますが…

  • Please give us your valuable comment

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

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