エクセルVBA グラフが描けない

下記プロシージャでグラフを書こうとしてますが、動作が安定しません。
マクロは別ワークブックに書き、データを収めたワークブックを開きC列とD列で散布図を描きます。埋め込みで描こうとしてます。

Sub test()

Dim xdata, ydata, DSname As String
Dim maxRow As Long

maxRow=Range("A6500").End(xlUp).Row
DSname=ActiveSheet.Name
xdata="C1:C" & maxRow
ydata="D1:D" & maxRow
Charts.Add

With ActiveChart
.ChartType=xlXYScatter
.SeriesCollection(1).XValues=Worksheets(DSname).Range(xdata) 'この行で動作不安定
.SeriesCollection(1).Values=Worksheets(DSname).Range(ydata)
.Location Where:=xlLocationAsObject, Name:=DSname
End With

With ActiveChart
.HasTitle=False
'以降は質問と関係ないので省略
End With
End Sub
注：コピー＆ペーストでなく手打ちなので、スペルミスがあるかもしれません。

動作不安定と書いた行でストップします。
エラーメッセージ
実行時エラー '1004' 'SeriesCollection'メソッドは失敗しました

常にエラーになるのではなく、正常終了することもあります。不安定と書いたのはこのためです。
１　マクロ実行時に空欄のセルが選択されている場合：エラー
２　マクロ実行時に空欄でないセルが選択されている場合：正常終了

どうすれば安定するでしょうか？

No.1 回答者： mitarashi 回答日時： 2012/04/07 01:29

セルが空かどうかが影響してくる理由は分かりませんが、

.SeriesCollection.NewSeries
を付け加えると良い様です。ご参考まで。

Sub test()
Dim xdata, ydata, DSname As String
Dim maxRow As Long

maxRow = Range("A6500").End(xlUp).Row
DSname = ActiveSheet.Name
xdata = "C1:C" & maxRow
ydata = "D1:D" & maxRow
Charts.Add

With ActiveChart
.ChartType = xlXYScatter
'下記を加えると良い様です。
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Worksheets(DSname).Range(xdata) 'この行で動作不安定
.SeriesCollection(1).Values = Worksheets(DSname).Range(ydata)
.Location Where:=xlLocationAsObject, Name:=DSname
End With

End Sub

この回答へのお礼

ありがとうございます。
SeriesCollection.NewSeries　マクロの自動記録時にはありましたが、不要かと思って削除した覚えがあります。確認します。

お礼日時：2012/04/07 11:02

No.2 ベストアンサー 回答者： okormazd 回答日時： 2012/04/07 01:30

「マクロ実行時に空欄のセルが選択されている」→系列(Series)に操作すべきデータがない→データがない系列の操作はできない→エラーになる

たとえば、x-yでグラフデータとしては1セットなので、デフォルトではy=1がセットされている(={1})。これを削除する(新しい空のデータ系列にする)とx軸のセットをしようとしてもエラーになる、手動でやってもエラーになりますよね。
ということでしょう。

回避策
Chartの操作をする前に事前になにか1つでもデータを入れるコードを追加する。たとえば0を邪魔にならないところに入れるか、すぐ上書きされてしまうところに入れる。
というようなことか。

この回答への補足

色々試しましたが、charts.add時にどのセルが選択されてるかが大きく影響するようです。
他回答のSetSourceDataも試しましたが今度はセル空欄時に正常終了します。

回避として、どこかのセル値を変数に代入した後空欄にして、そのままグラフを描いた後セル値を元に戻すことにしました。こんなエラー、どこのURLにも書いてません。

補足日時：2012/04/09 19:31

この回答へのお礼

ありがとうございます。
グラフを描く際にはxdataで列指定するのに、指定以前の初期値に影響されるということでしょうか。
意外でした。

お礼日時：2012/04/07 11:17

No.3 回答者： xls88 回答日時： 2012/04/07 07:40

SetSourceDataでもいいかもしれません。

maxRow=Range("A6500").End(xlUp).Row
DSname=ActiveSheet.Name
xdata="C1:C" & maxRow
ydata="D1:D" & maxRow
Charts.Add

With ActiveChart
.ChartType=xlXYScatter
.SetSourceData Source:=Worksheets(DSname).Range(xdata, ydata), PlotBy:=xlColumns
.Location Where:=xlLocationAsObject, Name:=DSname
End With

あとご存知かもしれませんが
Charts.Add
でグラフシートが作成され、次に
.Location Where:=xlLocationAsObject, Name:=DSname
でワークシート上に移動しています。
これは無駄な作業になります。
.ChartObjects.Add(.Left, .Top, .Width, .Height)
とすれば最初からワークシート上に作成することが出来ます。
≪参考≫
グラフ操作関連のテクニック
http://moug.net/tech/exvba/0021.htm

この回答への補足

.Range(xdata, ydata)
セル範囲の指定ですが、並んだC列D列ならばこの方式で可能ですが、例えばX軸：C列、ｙ軸：E列などの場合はどうやって指定するのでしょうか？

補足日時：2012/04/08 19:39

この回答へのお礼

ありがとうございます。
自動マクロを参考にしたため、Charts.addを使用してます。
URLも参考にゆっくり考えてみます。

お礼日時：2012/04/07 11:54

No.4 回答者： mitarashi 回答日時： 2012/04/07 15:05

#1です。

あくまで想像ですが、
.SeriesCollection.NewSeries
で新しいSeriesCollectionオブジェクトが生成され、これがインデックス1で参照できる訳ですが、
SeriesCollectionオブジェクトが無い状態でいきなり
.SeriesCollection(1).XValues =hoge
を実行すると、本来オブジェクトが無いというエラーになって然るべきですが、
エクセルが気を利かせて選択されているセルから生成してくれようとし、
選択セルが空だとエラーになるのではないでしょうか。
ご参考まで。