【ExcelVBA】SeriesCollectionのXValuesについて

質問

はじめまして。
VBAのSeriesCollectionのXValuesについて教えて下さい。

Excel2000のVBAを使用して棒グラフを描こうとしています。
グラフの元データは、セルを使用せずにVBA内での配列を使ってデータを設定しています。
下記プログラムの星印箇所で「SeriesクラスのXValuesプロパティを設定できません」というエラーが発生し、うまく作成できません。
（Y軸に個数、X軸に１週間ごとの日付を表示する単純なプログラムです）

私の環境（WinXP，Excel2000）では、上記プログラムの定数「W_要素数」の値を20から19に減らすとうまく動作するのですが、その仕組みもわかりません。

希望として、下記の条件を満たしたいと考えています。
(1)可能であれば20よりももっと大きい100くらいの要素を指定したい。
(2)セルにデータを書き込み、セルの範囲指定を行う方法はとりたくない。
（XValuesに配列のデータを指定したい）

ご存じの方、ご教示願えますでしょうか？

------以下プログラム------

Sub test()
Dim i As Long
Dim W_シート名 As String
Dim W_X軸() As Long
Dim W_日付() As Date
Dim W_週ごと As String
Const W_要素数 As Long = 20

W_シート名 = ThisWorkbook.Worksheets(1).Name
Charts.Add.Location Where:=xlLocationAsObject, Name:=W_シート名
ReDim W_X軸(W_要素数)
ReDim W_日付(W_要素数)
For i = 0 To W_要素数
W_日付(i) = DateAdd("ww", i, CDate(Format$(Now, "yyyy/mm/dd")))
W_X軸(i) = i + 1
Next i
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection(1)
.ChartType = xlColumnClustered
.Values = W_X軸
★.XValues = W_日付
End With
End Sub

回答No.3

redfox63 · ベストアンサー

あくまで推測ですが ・・・
Seriesオブジェクトの Formula, FormulaLocal, FormulaR1C1, FormulaR1C1Localで格納できる文字列の制限に引っかかっているのかもしれません

系列のデータやラベル文字などをこのFormulaに格納しているようです
セル指定の場合は $A$1:$A$30 といったセル番地
配列指定の場合は {0,1,2,3,4,5} といった配列式
が与えられるようです
したがって ValuesやXValuesのに与える配列データの内容によって追加できる項目数が制限されるようです

W_要素数=38とした場合
.XValues = W_日付
の行を
.XValues = W_日付　　' ここでブレーク
dim ss as string
ss = " " & .Formula　' スペースを追加しておく
.XValues = Array( 0,1)　'でウオッチなどで .Formulaを変化を監視
.Formula = ss　' でエラーになる
といった具合でデバッグしてみましょう

項目数が多くなるようなら非表示のシートを用意するなどしてセル番地指定のほうがいいかもしれません

この障害を逆手にとって W_日付のデータを間引いてしまうといった手法でご希望がかなうかもしれません
　　For i = 0 To W_要素数
　　　　If i Mod 5 = 0 Then
　　　　　　W_日付(i) = CLng(DateAdd("ww", i, Now))
　　　　End If
　　　　W_X軸(i) = i + 1
　　Next i
といった具合で 5項目ごとにラベルを作るとか ・・・

お礼

redfox63様
度々のご回答ありがとうございます。

W_要素数に39を設定し、「ActiveChart.SeriesCollection(1).Formula」をウォッチ式に入れて確認したところ
下記の値がFomulaには下記の値がセットされていました。
=SERIES(,,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40},1)
SERIESオブジェクトの第３引数にセットされている値がW_X軸のようですので、W_X軸は値をセットできていますが、
redfox63様ご指摘のように、W_日付をセットするところでエラーになっていることを確認できました。

配列方式だと余分なシートを作らないでいいかなと安易に考えていましたが、
アドバイス頂いたように、非表示のシートを利用した方法を考えてみます。
＃上記とは別に、W_日付のデータを間引く方法も試してみますね。

みなさんのアドバイスのおかげで１歩前進できました。
ありがとうございました。

回答No.4

redfox63

5項目ごとの設定をする場合
ActiveChart.Axes(xlCategory).TickLabelSpacing = 5
の設定をしたほうがいいようです

回答No.2

okormazd

以下

Sub test()
Dim i As Long
Dim W_シート名 As String
Dim W_X軸() As Long
Dim W_日付() As Long　　　'型を変えた
Dim W_週ごと As String
Const W_要素数 As Long = 30

W_シート名 = ThisWorkbook.Worksheets(1).Name
Charts.Add.Location Where:=xlLocationAsObject, Name:=W_シート名
ReDim W_X軸(W_要素数)
ReDim W_日付(W_要素数)
For i = 0 To W_要素数
W_日付(i) = DateAdd("ww", i, Now)　　'単純に加算するだけ
W_X軸(i) = i + 1
Next i
ActiveChart.SeriesCollection.NewSeries
With ActiveChart.SeriesCollection(1)
.ChartType = xlColumnClustered
.Values = W_X軸
.XValues = W_日付
ActiveChart.Axes(xlCategory).TickLabels.NumberFormatLocal = "yyyy/m/d"　　'ラベルの書式を日付に
End With
End Sub

お礼

okormazd様
回答ありがとうございます。

W_日付の型がDate型だとマズイということがわかりました。
ただ、やはりXValuesには0から38までの39要素しか入らないようです。

回答No.1

redfox63

Date型で与えているからエラーになっているのかも …

Double型の配列でデータを与えて 軸の書式設定を日付にしてみてはいかがでしょう
Dim W_日付() as Double
と宣言して

W_日付(i) = CDbl(DateAdd("ww", i, Format$(Now, "yyyy/mm/dd")))
といった具合にして

End Subの前に
　　With ActiveChart.Axes(xlCategory).TickLabels
　　　　.NumberFormatLocal = "yyyy/m/d"
　　　　.Alignment = xlCenter
　　　　.Offset = 100
　　　　.ReadingOrder = xlContext
　　　.Orientation = xlDownward
　　End With
を追加してみてください
# 表示方向が上下逆ならOrientationなどを変更してください
# 全角スペースを使用していますので適宜置換してください

お礼

redfox63様
早速の回答ありがとうございます。

ご指摘の通り、Double型の宣言に変えて実行したらグラフを作成できました。
ありがとうございます！
「実際の値はDouble型で、X軸項目のラベル表示を変える」という発想がありませんでした。
惜しいのは、これでもXValuesには38までしか値が入らないんですね。
そもそもXValuesにはそんなに値を入れるものではないのでしょうか。。。