EXCELグラフから作成コードを - ダンボ 08/05/30-17:59 No.12410
http://hanatyan.sakura.ne.jp/vb60bbs/wforum.cgi?mode=allread&no=12410&page=0
からの続質問です。（EXCEL2000 VBAです）

グラフに限らず、行・桁サイズ／各種コントロールの位置なども多数シートで統一する
必要が出てきたので、るしぇ さんのご提案どおりModelシートを隠しておいて各シート
に反映（更新）させるマクロを作っています。

Private Sub CopyGRF(WS As Worksheet)
    Dim ModelGRF As ChartObject, OLDGRF As ChartObject, NEWGRF As ChartObject
    Set ModelGRF = ThisWorkbook.Worksheets("Model").ChartObjects(1)
    Set OLDGRF = WS.ChartObjects(1)
    Application.ScreenUpdating = False
    OLDGRF.Delete
    ModelGRF.Copy
    WS.Paste
    Set NEWGRF = WS.ChartObjects(1)
    NEWGRF.Left = ModelGRF.Left
    NEWGRF.Top = ModelGRF.Top
    NEWGRF.Chart.SetSourceData Source:=WS.Range("C4:H12"), PlotBy:=xlColumns
    Application.ScreenUpdating = True
End Sub

これで一応期待する動作にはなるのですが、SetSourceData のRange("C4:H12")が直接値
で嫌です（拡張性が無い）
ModelGRF.Chartのデータ範囲を返してくれるメソッド／プロパティは無いでしょうか？
それが分かれば、
    NEWGRF.Chart.SetSourceData Source:=WS.Range(SRC.Address), PlotBy:=xlColumns
のようにModelのグラフだけ直せば良くて、CopyGRFを直す必要が無くなりますので。

> ModelGRF.Chartのデータ範囲を返してくれるメソッド／プロパティは無いでしょうか？

少なくても、どこに書いている表(開始位置)がグラフのデータかを統一しないと
シート上に いくつも表があれば、どれが使用したいグラフのデータかを特定する
方法はないのでは。

表の開始位置が決まっていれば、そのセルを含むアクティブセル領域を
ActiveCell.CurrentRegion で取得することができるかと思うのですが。
又は、開始位置を引数で渡してデータの入力範囲を取得するようにしておけば
コード自体は変更しなくてもよくなるかと。

> Range("C4:H12")が直接値で嫌です（拡張性が無い）

データ範囲を直接指定ではなくて、
データ範囲を記録したセルを指定とかはどうでしょう？

　セルA1：開始位置R
　セルB1：開始位置C
　セルC1：終端位置R
　セルD1：終端位置C
　（開始位置が固定なら終端位置のみで可）

といった感じでデータ値を書き込むついでに範囲を記述しておいて
セルの値からデータの範囲を特定するとか。

花ちゃん さん、琴 さん、どうも有り難うございます。

> 少なくても、どこに書いている表(開始位置)がグラフのデータかを統一しないと
> シート上に いくつも表があれば、どれが使用したいグラフのデータかを特定する
> 方法はないのでは。

これは誤解なのかも。"Model"シートには一つの表とそれをデータ範囲とするChartObjectが
ひとつあります。このChartを別な"WS"ワークシートにコピーします。単純にコピーだけでは
データ範囲は元の"Model"シートを指しているので、"WS"ワークシートの表に指し替えたい訳
です。
"Model"シートと"WS"ワークシートは全く同じセル配置を持っているので、データ範囲の
「シート名」部分だけを変更すればよいです。

表が一つだけという前提を固辞すれば、花ちゃんさんのCurrentRegion利用も悪くは無いかと
思いましたが、ちょっと複雑なグラフの指し替えをマクロ記録で見ると、

ActiveChart.SetSourceData Source:=Sheets("WS").Range("C4:C12,M4:O12"), PlotBy:=xlColumns
でした。この場合はCurrentRegionじゃ無理ですね。
（これは一つの表と言いつつ論理的には2つの表を使っていますねぇ）

> これは誤解なのかも。"Model"シートには一つの表とそれをデータ範囲とするChartObjectが
> ひとつあります。このChartを別な"WS"ワークシートにコピーします。単純にコピーだけでは
> データ範囲は元の"Model"シートを指しているので、"WS"ワークシートの表に指し替えたい訳
> です。
> "Model"シートと"WS"ワークシートは全く同じセル配置を持っているので、データ範囲の
> 「シート名」部分だけを変更すればよいです。

想定している処理の流れが
１．ModelシートとWSワークシートにデータを書き込む
　↓
２．ModelシートにあるChartObjectをWSワークシートにコピーする
　↓
３．コピーしたChartObjectのデータ参照元をWSワークシートに書き換える

と読み取れましたが、
１．Modelシートにデータを書き込む
　↓
２．Modelシートの複製シートを作成し、それをWSワークシートとする

というのはどうでしょうか？

琴さん、どうも有り難うございます。

> １．ModelシートとWSワークシートにデータを書き込む
実データはWSワークシートにだけであり、Modelシートには表枠とテスト用の固定ダミーデータだけですけどね。

> １．Modelシートにデータを書き込む
> ２．Modelシートの複製シートを作成し、それをWSワークシートとする
良いアイデアだと思います。ただ、WSワークシート（実は30シートくらい）が全く同じ内容
ならよいんですが、微妙に異なるところもあります。表題、ボタンのCaptionや各ワークシートのマクロなど。
Chartのコピーを楽にするか、その他の部分をどうするかも勘案して、琴さんのご提案を考えて見ます。

> これで一応期待する動作にはなるのですが、SetSourceData のRange("C4:H12")が直接値
> で嫌です（拡張性が無い）
> ModelGRF.Chartのデータ範囲を返してくれるメソッド／プロパティは無いでしょうか？
> それが分かれば、

こんばんは。
こんなの見つけました。

Sub Macro2()
    Dim i As Integer
    Dim cha As Chart
    
    Set cha = Sheet1.ChartObjects(1).Chart
    With cha.SeriesCollection
        For i = 1 To .Count
        Debug.Print "系列" & i & "のソースデータ範囲は　⇒" _
            & vbCrLf & .Item(i).Formula
        Next i
    End With
  
End Sub

Renard さん、どうも有り難うございます。

>     With cha.SeriesCollection
このプロパティの存在は早くから知ってたんですが「系列を表す」ものであって、
SetSourceDataの「データ範囲」とはちょっと違うね、と除外していました。
ご返事書くためにマクロ記録で検証してみましたところ、SeriesCollectionを
すべてWSシートに指し替えれば、何ら問題ないことが分かりました。

「系列と名前をすべて定義すればデータ範囲は自ずから決まる」ということ？

SetSourceDataでは、系列を指定できませんので、
「SetSourceDataのSource ⊂ SeriesCollection」という感じ？

じゃ、何のためにSetSourceDataメソッドがあるんだ？
とつらつら推測するに、

大仮説「グラフWizardを作成するために後で付け加えた(EXCELの内部使用)」
根拠1．上記のようにグラフでは「系列・名前定義があれば」データ範囲は不要な筈。
根拠2．グラフWizardの最初のステップがデータ範囲の指定。簡単なグラフであればグラフWizardが系列を自動定義する。
根拠3．これだけ探してもSetSourceDataのSourceプロパティ／メソッドが見つからない。
根拠4．「データ範囲」の指し替えと「系列・名前のデータ範囲」の変更をマクロ記録してみると
　　　前者はA1形式、後者はR1C1形式で記録される。→時期が違うんじゃない？

大仮説のことはいざ知らず、下記コードで汎用的な処理（複数シートの複数グラフの複数系列）
ができましたので報告しておきます。

Private Sub CopyGRF(WS As Worksheet)
    Dim ModelSheet As Worksheet, NOC As Integer, i As Integer
    Set ModelSheet = ThisWorkbook.Worksheets("Model")
    NOC = ModelSheet.ChartObjects.Count
    If NOC < 1 Then Exit Sub

    Application.ScreenUpdating = False
    For i = NOC To 1 Step -1
        Call CopyGRF1(ModelSheet, WS, i)
    Next i
    Application.ScreenUpdating = True
End Sub

Private Sub CopyGRF1(MD As Worksheet, WS As Worksheet, i As Integer)
    Dim MDChart As ChartObject, WSChart As ChartObject
    WS.ChartObjects(i).Delete
    Set MDChart = MD.ChartObjects(i)
    MDChart.Copy
    WS.Paste
    Set WSChart = WS.ChartObjects(i)
    WSChart.Left = MDChart.Left
    WSChart.Top = MDChart.Top
    
    Dim k As Integer
    With WSChart.Chart.SeriesCollection
        For k = 1 To .Count
            .Item(k).Formula = Replace(.Item(k).Formula, MD.Name, WS.Name)
        Next k
    End With
End Sub