EXCELマクロを用いてグラフの系列の参照元を置換

質問

タイトルの通りですが、エクセルでマクロを用いて、グラフの系列の参照元であるリンク数式を置換する方法を探しています。

エクセルでグラフを作成した時に"元のデータ"を見ると、"系列"タブの中に"名前"、"Xの値"、"Yの値"を指定するテキストボックスがありますよね？
あの中に書いてある文字列を置換したいんです。

以下の手法で作ってみたのですが、
『実行時エラー '13':
型が一致しません。』
というエラーが出てしまいます。

Sub Macro()
Dim BeforeValue
Dim AfterValue
BeforeValue = "ここに置換元文字列を入れます"
AfterValue = "ここに置換後文字列を入れます"

For Each Value In ActiveChart.SeriesCollection
With Value
.XValues = Replace(.XValues, BeforeValue, AfterValue)
.Values = Replace(.Values, BeforeValue, AfterValue)
.Name = Replace(.Name, "BeforeValue", "AfterValue")
End With
Next Value
End Sub

原因は.XValuesや.Nameがリンク数式を評価した後の値を配列で保持しているためだと思います。

そこで.XValuesや.Nameが保持しているデータが入っていたセルのアドレスを探すか、
あるいは.XValuesが評価している、元のリンク数式を保持しているプロパティ(か何か)を探せば文字列として置換できるのではないかと考えているのですがいかがでしょうか？

"元のデータ"を開いた時にもちろんリンク数式が表示されるので、どこかにリンク数式が文字列として保持されているはずだと思うのですが…。

このプロパティについて何かご存知の方、あるいは違う方法をご存知の方いらっしゃいませんでしょうか？
もしかしたら全然的外れの事をしているかもしれません。マクロを触り始めて日が浅いもので…。
その辺りの初心者的ミスも含めて間違い等、指摘して頂ける方でも結構です。

動作環境は
OS:Win2000
EXCEL2003
VB6.5
です。

何かお分かりになる方、よろしくお願い致します。

回答No.2

fumufumu_2006 · ベストアンサー

>>どうしても置換したいと言う場合ですが、調べた限りでは、元の情報は持っていないようです。(あるのかも知れませんが)
>>これは、a=2の場合、a=1+1で指定したのか、a=4-2で指定したのかわからないようなものです。
>そうなのですか？となると元情報の置換は出来ないということになってしまうのでしょうか……。
と自分で書いておいて、ふと考えて、excelのシート上で変更できるんだから、どっかで情報保存してるはずじゃないかと、もう一度調べてみました。

で、
ActiveChart.SeriesCollection(n).Formula
に格納されていました。
http://msdn2.microsoft.com/ja-jp/library/microsoft.office.interop.excel.series.formula(VS.80).aspx
「オブジェクトの数式を、A1 形式で設定します。値の取得および設定が可能です」
とありました。

nは、そのチャートのグラフ数（1からActiveChart.SeriesCollection.count）です。

ここをReplaceしてみてはどうでしょうか？

お礼

出来ました(^▽^)！
下の方法で系列名、Xの値、Yの値をまとめて置換できるのを確認しました！

For Each Value In ActiveChart.SeriesCollection
With Value
If (InStr( .Formula, BeforeValue)) Then
.Formula = Replace( .Formula, BeforeValue, AfterValue)
End If
End With
Next Value

やはりFor Eachにしてあるのは、必要十分なだけ系列にアクセスするようにです(系列番号が抜けている場合もありますので)。
置換元文字列が含まれている場合だけ処理するように、一応If文をはさんであります。

ただ、Formulaの中には
=SERIES(系列名,Xの値,Yの値,系列番号)
のように格納されているようですので、置換する際は少し注意が必要なようです(特に","を含んだ置換やXの値だけ置換したい場合)。
また、要素が1つも無い系列(参照先が空白セルだったり)がある場合はエラーになってしまうようです。

その辺り、まだ少し詰めなくてはならない部分もありますが、置換するだけならこのコードで十分出来ますので、この質問はここで締め切りとさせていただきます。
fumufumu_2006さん、ありがとうございました！

回答No.1

fumufumu_2006

まず、BeforeValue,AfterValueがどんな値かわからないのですが、
Replace(.Name, "BeforeValue", "AfterValue")
は、BeforeValue,AfterValueの値に関係なく、"BeforeValue"と言う文字を "AfterValue"と言う文字に置換するだけです。

次に、エラーが出る.XValueの所で、
MsgBox VarType(.XValues)
としてみてもらえば、8204を表示すると思います。
これは、8204=&h200Cで、配列 8192(&h2000)とVariant 12(&hC)で、variantの配列だとわかります。
と言う訳で、文字列変換の、Replace(.XValues, BeforeValue, AfterValue)と言う式はおかしい訳です。
ちなみに、VarType(.Name)=8(文字列)です。

話は戻って、XValues,Valuesは、Rangeオブジェクトか値の配列を設定してあげればいいので、
.XValues=Range("A2:A10")
.Values = Range("B2:B10")
とかしてあげればいいんじゃないかと思います。

Nameも.Name=Range("B1")とか、.Name="B列です"という風に設定すればいいだけだと思います。

と言う訳で、置換するのではなく、新たに設定してやればいいだけじゃないかと思います。
それと、For Each など使わずに、1つ目のグラフを変更するなら、
With ActiveChart.SeriesCollection(1)
.XValues = Range("A2:A10")
.Values = Range("B2:B10")
.Name = Range("B1")
End With
としてあげればいいと思います。

どうしても置換したいと言う場合ですが、調べた限りでは、元の情報は持っていないようです。(あるのかも知れませんが)
これは、a=2の場合、a=1+1で指定したのか、a=4-2で指定したのかわからないようなものです。

お礼

お礼が遅れて申し訳ありません。
BeforeValueは検索する文字列、AfterValueは置換後の文字列を格納する変数です。
仰るとおり、
.Name = Replace(.Name, "BeforeValue", "AfterValue")
とすると変数としてではなく文字列として評価されてしまいますね。
途中で気付いて.XValuesと.Valuesの方は直したんですが.Nameの所だけ直し忘れていたようです。すいません。

For Eachを使ったのは、系列の数が一定でないので汎用性を持たせるためです。
こちらの環境で、.XValuesや.Valuesを新たに設定する方法ならどうやらうまく作動するみたいです。
ですが、グラフの数が多くて1つずつ修正するのが面倒なために置換プログラムを考えていたので、この方法だと結局グラフ毎にプログラムを書き直す必要があり、あまり意味がなくなってしまいます。

>どうしても置換したいと言う場合ですが、調べた限りでは、元の情報は持っていないようです。(あるのかも知れませんが)
>これは、a=2の場合、a=1+1で指定したのか、a=4-2で指定したのかわからないようなものです。
そうなのですか？となると元情報の置換は出来ないということになってしまうのでしょうか……。