EXCEL VBA(2003) での積上グラフの要素の合計表示について

質問

縦積上グラフの合計値をデータラベルの形で既存のグラフに表示するマクロを作成しました。
現状では行方向に県名（要素）、列方向に男／女人口（系列）を並べ、女人口の隣の列に各県の男女の合算人口を並べて”１”で参照させています（データラベルにしたいのでValueをTEXT値にしています）。
マクロでは、既存グラフの要素数を取得して、同数の要素（値は全てゼロ）をもつ配列を新系列に加え、上でTEXT化した合計値をデータラベルの.TEXTに設定するという方法をとっています。

現状の行列の配置の場合には下のマクロが期待通りの実行結果を返してくれますが、既存グラフのデータ範囲の設定で行／列の向きを逆にし、性別毎の合計値を①で参照させた場合には”２”のように要素数と”１”で取得したデータ数が一致しないという結果が返ります。

”３”付近で間違いをしている気もするのですが、どなたかご指摘いただけますと幸甚です。

Sub Test()

Dim newRange As Range
Dim newSC As Variant ' Σ系列の配列変数
Dim SCcnt As Integer ' SeriesCollectionの変数
Dim SC1Value As Variant ' SeriesCollection(1)の配列
Dim i As Integer
Dim A1 As String 'A1形式→R1C1形式の変換に使用
Dim R1 As String 'A1形式→R1C1形式の変換に使用
Dim R1withSheetname As String 'A1形式→R1C1形式の変換に使用

Set newRange = Application.InputBox(Prompt:="合計欄の参照を選択してください。", Type:=8)　'”１”
newSC = newRange

With ActiveChart
SCcnt = .SeriesCollection.Count
SC1Value = .SeriesCollection(1).Values '要素数を取得　'”３”

If UBound(newSC) <> UBound(SC1Value) Then

'”２”
MsgBox ("（注意）選択したデータの個数が系列1のデータ個数と不一致。" & vbCr & vbCr & _
"選択範囲のデータ個数:" & (UBound(newSC) - LBound(newSC)) & vbCr & vbCr & _
"系列1のデータ個数:" & UBound(SC1Value))
End If

.SeriesCollection.NewSeries
SCcnt = SCcnt + 1
With .SeriesCollection(SCcnt)
.Values = newSC
.Name = "Σ"
End With
.PlotArea.Select
.ApplyDataLabels AutoText:=True, ShowValue:=True

For i = 1 To UBound(newSC) '系列数を取得
'R1C1形式に変換
A1 = newRange(i).Address
R1 = Application.ConvertFormula(Formula:=A1, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1, ToAbsolute:=xlAbsolute)
R1withSheetname = "=" & ActiveSheet.Name & "!" & R1
With .SeriesCollection(SCcnt)
.DataLabels.Select
.Points(i).DataLabel.Select
With Selection
.Text = R1withSheetname
.Position = xlLabelPositionInsideBase
End With
End With
Next i
End With
End Sub

回答No.2

xls88 · ベストアンサー

＞（データラベルにしたいのでValueをTEXT値にしています）。
newSC = newRange
で、値化していることを言っているのですか？

＞マクロでは、既存グラフの要素数を取得して、
＞同数の要素（値は全てゼロ）をもつ配列を新系列に加え、
＞上でTEXT化した合計値をデータラベルの.TEXTに設定する
提示されたコードと一致していないと思います。
追加した系列（NewSeries）の値（Values）には、InputBoxで指定した合計値セルデータ（newSC）をセットされています。
ということで、（値は全てゼロ）にはならないですね。

＞現状の行列の配置の場合には下のマクロが期待通りの実行結果を返してくれますが、
＞既存グラフのデータ範囲の設定で行／列の向きを逆にし、
＞性別毎の合計値を①で参照させた場合には
＞”２”のように要素数と”１”で取得したデータ数が一致しない
＞という結果が返ります。
配列の次元数の問題ではないでしょうか。
UBoundの第2引数に次元番号を指定すればどうなるでしょうか。
例えば
If UBound(newSC, 2) <> UBound(SC1Value) Then
あるいは
If newRange.Count <> UBound(SC1Value) Then
としても良いかも知れません。

Excel VBAメモ：UBound関数と多次元配列
http://note.phyllo.net/?eid=539332

Selectしなくても可能です。
R1C1変換もしなくても可能です。

Dim newRange As Range
Dim dd As Variant
Dim n As Integer
Dim i As Integer

Set newRange = Application.InputBox(Prompt:="合計欄の参照を選択してください。", Type:=8) '”１”

With ActiveChart
n = .SeriesCollection(1).Points.Count '要素数を取得　'”３”
If n <> newRange.Count Then
MsgBox ("合計値データ個数が、系列1のデータ個数と不一致。")
Exit Sub
End If

'//0値ダミーデータ
For i = 1 To n
dd = dd & "," & 0
Next i
dd = Replace(dd, ",", "", 1, 1)
'//ダミー系列の追加とデータラベル表示
With .SeriesCollection.NewSeries
.Values = "{" & dd & "}"
.Name = "Σ"
.ApplyDataLabels
.DataLabels.Position = xlLabelPositionInsideBase
For i = 1 To n
With .Points(i).DataLabel
.Text = "=" & ActiveSheet.Name & "!" & newRange(i).Address
End With
Next i
End With
End With

お礼

ご回答ありがとうございます。
無事に解決致しました。多次元配列については未認識でしたので、今回新たに勉強させていただきました。

回答No.1

tosa0507

>データラベルにしたいのでValueをTEXT値にしています
?