タイトルの通りですが、エクセルでマクロを用いて、グラフの系列の参照元であるリンク数式を置換する方法を探しています。
エクセルでグラフを作成した時に"元のデータ"を見ると、"系列"タブの中に"名前"、"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ベストアンサー
- 回答日時:
>>どうしても置換したいと言う場合ですが、調べた限りでは、元の情報は持っていないようです。
(あるのかも知れませんが)>>これは、a=2の場合、a=1+1で指定したのか、a=4-2で指定したのかわからないようなものです。
>そうなのですか?となると元情報の置換は出来ないということになってしまうのでしょうか……。
と自分で書いておいて、ふと考えて、excelのシート上で変更できるんだから、どっかで情報保存してるはずじゃないかと、もう一度調べてみました。
で、
ActiveChart.SeriesCollection(n).Formula
に格納されていました。
http://msdn2.microsoft.com/ja-jp/library/microso …
「オブジェクトの数式を、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
- 回答日時:
まず、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で指定したのかわからないようなものです。
そうなのですか?となると元情報の置換は出来ないということになってしまうのでしょうか……。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA 教えてください。 VBA初心者です。 詳しい方がいましたら教えてください。 下記 3 2023/04/25 11:22
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) エクセルのマクロについて教えてください。 7 2023/07/04 09:18
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) 数式の置換がうまく行かない 6 2022/05/04 15:51
- Visual Basic(VBA) シートをコピーする下記記述でダイアログを用いた記述がわかりません?( A = Dir(ThisWor 4 2022/08/22 12:26
- Visual Basic(VBA) excel2021で実行できないマクロ。どこを直したらいいのか 2 2022/03/28 03:40
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
GoogleAppsScript文字列置換の...
-
テキストボックスの文字列を置...
-
正規表現 特定の文字列を含む行...
-
各項目がダブルクォーテーショ...
-
csvデータ ダブルクォーテ...
-
csvデータのダブルクォーテーシ...
-
正規表現でURLを抽出したいと思...
-
python 大文字小文字を区別しな...
-
スペースで区切られた氏名から...
-
Excel・ユーザーフォームの情報...
-
C言語でテキストファイルの内容...
-
C#で空白行を削除する方法
-
\\(円)記号を置換したい
-
正規表現 空白(スペース)を削...
-
EXCEL警告「置換対象のデータが...
-
住所編集のマクロを組んでいて...
-
PowerShellでテキストの空白行...
-
【至急!!!!!】ランダム桁数を正...
-
VBA 置換文字がみつからない時
-
xmlファイル内の文字列置換
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
各項目がダブルクォーテーショ...
-
VBA 置換文字がみつからない時
-
正規表現で、特定の文字列を含...
-
スペースで区切られた氏名から...
-
csvデータのダブルクォーテーシ...
-
csvデータ ダブルクォーテ...
-
GoogleAppsScript文字列置換の...
-
C言語でテキストファイルの内容...
-
正規表現 特定の文字列を含む行...
-
テキストボックスの文字列を置...
-
EXCEL警告「置換対象のデータが...
-
TeraPadエディターの操作方法に...
-
xmlファイル内の文字列置換
-
python 大文字小文字を区別しな...
-
C#で空白行を削除する方法
-
テキストファイル中の文字列の置換
-
Excel VBA 教えてください。 VB...
-
ハングルを日本語に置換
-
EXCEL VBA でCTRL+Fのダイア...
-
PowerShellでテキストの空白行...
おすすめ情報