お世話になります。現在ワークシート上に2軸の折線グラフを10~20程(ブックにより違います)作成しています。

そのグラフの参照元をVBAにより変更したいのですが、どうすればよいのでしょうか?
参照列が固定していれば良いのですが、随時変更されますのでさらに難解になってます。

具体例ですが、初期の参照元が
 “グラフ1” 系列1= B50:B70
        系列2= F50:F70
“グラフ2” 系列1= D50:D70
       系列2= S50:S70
とグラフが続いています。そして 
    セルA1=50 → 51
    セルB1=70 → 71
と、セルを変化させる事により参照位置を変更させたいのです。
また、不定期ですが現在“グラフ1”では
  B列、F列 を参照していますが
  AA列、BD列 など変更されま。
またグラフの数も不特定です。
共通して変更されるのは 50~70などの位置のみです。 

VBAで変更したいのですが、どうすれば宜しいでしょうか?

回答の条件
  • 1人5回まで
  • 登録:2007/12/02 21:18:54
  • 終了:2007/12/06 21:52:11

ベストアンサー

id:airplant No.4

airplant回答回数220ベストアンサー獲得回数492007/12/04 03:08:57

ポイント100pt

要望のことができるようにマクロを作ってみました。

全シートにあるグラフのカラムはそのままで、行のみを[A1]から[B1]の内容に変更した系列にします(マクロ実行時のシートのA1、B1)。

マクロを動かすと、どばっと一気にグラフが変わります。

Option Explicit

Const REfromPat As String = "\$[0-9]+\:"
Const REToPat As String = "\$[0-9]+\,"

Sub ChangeRef()
    
    Dim wS As Worksheet
    Dim chaObj As ChartObject
    Dim oSer As Series
    Dim sFormula As String
    Dim iFrom As Integer, iTo As Integer
    
    'FromとToの行を覚える
    iFrom = Range("A1").Value
    iTo = Range("B1").Value
    
    'Sheet毎、chart毎、系列毎にFromとToの記載行を文字列置換する
    For Each wS In Worksheets
        For Each chaObj In wS.ChartObjects
            For Each oSer In chaObj.Chart.SeriesCollection
                sFormula = ConvByRe(oSer.Formula, REfromPat, "$" & iFrom & ":")
                oSer.Formula = ConvByRe(sFormula, REToPat, "$" & iTo & ",")
            Next
        Next
    Next
End Sub

'正規表現で文字列を変更する
Function ConvByRe(strSrc As String, REFrom As String, RETo As String) As String
    
    Dim oRe As Variant
    Dim lnI As Long
    Dim strRep As String
                            
    Set oRe = CreateObject("VBScript.RegExp")
    With oRe
        .IgnoreCase = True          '大小文字無視
        .Global = True              '文字列全体が対象
        .Pattern = REFrom           '検索パターン
        If .test(strSrc) Then       'マッチしたら文字列変更
            strRep = RETo
            ConvByRe = .Replace(strSrc, strRep)
            Set oRe = Nothing
            Exit Function
        End If
    End With
    ConvByRe = strSrc
    Set oRe = Nothing

End Function

なお、系列には名前や項目軸ラベルは指定していないことを仮定しています。系列選択時に下記のような内容が出ることを想定しています。

=SERIES(,,Sheet1!$B$50:$B$70,1)

id:dadan_dan

airplantさん ありがとうございました。

マクロを実行してみて!!です。

言われた通り一気に変わりました。

それよりも此処まで手を掛けて頂いてありがとうございました。

見たことのないマクロが沢山ありすぐには理解できないですが、

少しずつ勉強して行きます。時間を取らせてしまいすみませんでした。

あらがとうございました。

2007/12/04 15:17:20

その他の回答(4件)

id:thrillseeker No.1

thrillseeker回答回数328ベストアンサー獲得回数372007/12/02 21:57:05

ポイント5pt

VBAだと参照先が変わる度にマクロを編集し直す必要が

あると思うので、それほど便利にならないのでは?


それよりも、随時変更されるデータにグラフの参照先を

直接設定するのでは無く、グラフ参照専用の列を別途

作成し、その列が実際のデータを参照するようにしては

どうですか?


例:

“グラフ1”:系列1= AB1:AB21,系列2=AF1:AF21

列AB1:AB21:列B50:B70を参照

列AF1:AF21:列F50:F70を参照

グラフの変更→列AB,AF の参照先を変更

id:dadan_dan

回答ありがとうございます。すでにこの方法は実施済みの方法なのですが、個人また

社内なら問題はないですが、ファイルを相手先へ送信するのですが、送信容量や

見た目を考慮すると、やはりVBAが良いと思うのですが如何でしょうか?

参照列の変更はVBAを使うと余計に複雑になりそうですね。すみません今気づきました。

数値の変更のみではどうなのでしょうか?

2007/12/02 22:54:35
id:Dark1984B No.2

黒ひよこ回答回数17ベストアンサー獲得回数02007/12/03 12:44:50

ポイント2pt

使う行が固定なら割とやりやすいかと。

どのデータが関連付けられてるかVBAに自動認識させたいなら、

すぐ思いつくのが下記の2つですね。

①系列1と系列2は必ず隣の列にする

 例 グラフ1 系列1=B50:B70

        系列2=C50:C70

   グラフ2 系列1=S50:S70

        系列2=T50:T70

①同じ系列に共通の見出し付ける

 例 グラフ1 B49="グラフ1",系列1=B50:B70

        F49="グラフ1",系列2=F50:F70

   グラフ2 D49="グラフ2",系列1=D50:D70

        S49="グラフ1",系列2=S50:S70

id:dadan_dan

Dark1984Bさんありがとうございます。

最初の“系列を隣列”にする案ですが、

グラフを作成する時にも負担が少ないのでその方が助かるのですが、

他の処理の手順上並べるのは難しいです。(_ _)

次の“共通の見出しを付ける”案ですが、

“見出し”を付けるという事をしたことがないですが、

残念ながら行は固定が出来ませんが、ここからどの様に

VBAを展開させるのでしょうか?

2007/12/03 13:25:01
id:Dark1984B No.3

黒ひよこ回答回数17ベストアンサー獲得回数02007/12/04 03:38:50

ポイント3pt

すいません②の例が一部間違っていました。

 S49="グラフ1"→正しくはS49="グラフ2"


VBAへの展開ですが、見出し行を作って、

見出しのセルの値が同じ2つの列でグラフ作るとすればいくかと。

まぁ、あくまで行が固定なら通じるやり方です。


あと複数のブックで使いたいみたいですが、

ブックごとの関連付けられてるデータ位置が同じで、

ブックによってはグラフを作らないのであれば、

グラフに使うデータのセルが空欄なら、そこのグラフは作らないとした方が楽だと思います

id:dadan_dan

Dark1984B さんありがとうございます。

こちらの都合ですみませんが、行が変更しないと最大の効果が発揮できないのです。

ありがとうございました。

2007/12/04 15:22:25
id:airplant No.4

airplant回答回数220ベストアンサー獲得回数492007/12/04 03:08:57ここでベストアンサー

ポイント100pt

要望のことができるようにマクロを作ってみました。

全シートにあるグラフのカラムはそのままで、行のみを[A1]から[B1]の内容に変更した系列にします(マクロ実行時のシートのA1、B1)。

マクロを動かすと、どばっと一気にグラフが変わります。

Option Explicit

Const REfromPat As String = "\$[0-9]+\:"
Const REToPat As String = "\$[0-9]+\,"

Sub ChangeRef()
    
    Dim wS As Worksheet
    Dim chaObj As ChartObject
    Dim oSer As Series
    Dim sFormula As String
    Dim iFrom As Integer, iTo As Integer
    
    'FromとToの行を覚える
    iFrom = Range("A1").Value
    iTo = Range("B1").Value
    
    'Sheet毎、chart毎、系列毎にFromとToの記載行を文字列置換する
    For Each wS In Worksheets
        For Each chaObj In wS.ChartObjects
            For Each oSer In chaObj.Chart.SeriesCollection
                sFormula = ConvByRe(oSer.Formula, REfromPat, "$" & iFrom & ":")
                oSer.Formula = ConvByRe(sFormula, REToPat, "$" & iTo & ",")
            Next
        Next
    Next
End Sub

'正規表現で文字列を変更する
Function ConvByRe(strSrc As String, REFrom As String, RETo As String) As String
    
    Dim oRe As Variant
    Dim lnI As Long
    Dim strRep As String
                            
    Set oRe = CreateObject("VBScript.RegExp")
    With oRe
        .IgnoreCase = True          '大小文字無視
        .Global = True              '文字列全体が対象
        .Pattern = REFrom           '検索パターン
        If .test(strSrc) Then       'マッチしたら文字列変更
            strRep = RETo
            ConvByRe = .Replace(strSrc, strRep)
            Set oRe = Nothing
            Exit Function
        End If
    End With
    ConvByRe = strSrc
    Set oRe = Nothing

End Function

なお、系列には名前や項目軸ラベルは指定していないことを仮定しています。系列選択時に下記のような内容が出ることを想定しています。

=SERIES(,,Sheet1!$B$50:$B$70,1)

id:dadan_dan

airplantさん ありがとうございました。

マクロを実行してみて!!です。

言われた通り一気に変わりました。

それよりも此処まで手を掛けて頂いてありがとうございました。

見たことのないマクロが沢山ありすぐには理解できないですが、

少しずつ勉強して行きます。時間を取らせてしまいすみませんでした。

あらがとうございました。

2007/12/04 15:17:20
id:SALINGER No.5

SALINGER回答回数3454ベストアンサー獲得回数9692007/12/03 21:22:45

ポイント50pt

ChartObjectから参照情報を取得するのは難しそうなので、グラフの系列名を参照している列のアルファベットにしてしまいます。

(系列名を使っている場合は頭につけるとか工夫すれば実現できると思います。)

系列名の変更は、グラフの右クリックから元のデータ→系列→名前で設定できます。

例えば、系列名1をB。系列名2をFにします。表示させたくない場合はクリアにします。

そして、次のコードをワークシートのchangeイベントに書き込みます。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 1 Or Target.Column > 2 Then Exit Sub
    Dim r As String
    Dim c1, c2 As String
    Dim ch As ChartObject
    Dim minRow As Long
    Dim maxRow As Long
    
    minRow = 50     '最小行を設定
    maxRow = 100    '最大行を設定
    
    If Range("A1").Value >= minRow And Range("A1").Value <= maxRow _
        And Range("B1").Value >= minRow And Range("B1").Value <= maxRow Then
        For Each ch In ActiveSheet.ChartObjects
            ch.Activate
            With ActiveChart
                c1 = .SeriesCollection(1).Name
                c2 = .SeriesCollection(2).Name
                r = c1 & Range("A1").Value & ":" & c1 & Range("B1").Value & "," _
                    & c2 & Range("A1").Value & ":" & c2 & Range("B1").Value
                .SetSourceData Source:=ActiveSheet.Range(r), PlotBy:=xlColumns
                .SeriesCollection(1).Name = c1
                .SeriesCollection(2).Name = c2
            End With
        Next
    Else
        MsgBox "正しい範囲を入力してください"
    End If
End Sub

このコードはA1セルとB1セルに50と70のように範囲を書き込むことで、ワークシート上の全てのグラフをその範囲に変更するコードです。

個々のグラフの参照列を変更するには、上記の系列名を変更し、一度A1とB1を変更することで変更できます。

id:dadan_dan

SALINGER さんありがとうございます。

ここまで手の掛かるマクロとは思ってもみませんでした。

ありいがとうございます。

一つ問題点が発生しまして、教えて頂きたいのですが、“実行時エラー1004”が表示され

.SetSourceData Source:=ActiveSheet.Range(r), PlotBy:=xlColumns

上記↑がエラーとなってしまいました。どの様な原因が考えられるのでしょうか?

2007/12/04 15:08:30

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません