【VBA】グラフのデータ範囲を個別に設定する方法！SeriesCollectionのエラーを解決

【こんな人に読んでほしい】

✅～Chart.SeriesCollection().Formula = “=SERIESを使って、エラーに悩まされている人。
（実行エラー1004：”アプリケーション定義またはオブジェクトのエラーです”）

✅グラフのデータ範囲の指定方法を知らない人

✅X軸、Y軸、系列(凡例)のデータ範囲をそれぞれ設定したい。

✅どっかから持ってきた既存グラフを、データ範囲だけ編集して使いたい。

VBAでグラフのSeriesCollectionを操作すると「実行時エラー1004」が発生することがあります。

原因の多くは

・グラフが選択されていない

・SeriesCollectionの指定ミス

です。

この記事では、エラー1004が出る原因と解決方法を紹介します。

【状況整理】グラフ作成の平和なパターンとややこしいパターン

↓平和なパターン　データ範囲がくっついている

この場合だと、下記のみでデータ範囲を指定できます。

↓ややこしいパターン（データ範囲が離れている）

この場合、SetSourceData では対応が難しいです。

ご安心ください、今から、データの選択方法をお伝えします☆

【データ範囲設定】「SERIES関数」を理解する

ここでは、グラフのデータ範囲を X軸・Y軸・系列それぞれで設定する方法 を紹介します。
各系列に対して、順番にデータを設定していく流れになります。

SeriesCollection()は系列を示します。

カッコ内は何個目の系列かを指定しますので、SeriesCollection(1)とすることで、
一つ目の系列についての設定することができます。
系列、X軸、Y軸のデータ範囲については、下記のように設定します。

SeriesCollection(1).Formula = “=SERIES(系列のデータ範囲, X軸のデータ範囲, Y軸のデータ範囲, 1)”

例）検索流入のデータ範囲設定

～.Chart.SeriesCollection(1).Formula ="=SERIES(Sheet1！$C$3,Sheet1!$B$6:$B$11, Sheet1!$C$6:$C$11, 1)" 

ただし、このままではエラーになる可能性も・・・

【解決策】Range().Address(External:=True)で、~chart.SeriesCollection.Formulaのエラーを封印♡

せっかく、本調子がでてきたのに、このままでは幻滅ですよね…
そこで、データ範囲の後ろに、(External:=True)を入力します。

• • Range.Address(External:=True) は
    「[Book1]Sheet1!$A$1」 のような文字列を返す

Addressメソッドの引数として指定することで、このエラーを回避できます。

例）検索流入のデータ範囲設定

'1つ目の系列（SeriesCollection(1)）のデータ範囲を直接設定する
'「=SERIES(系列名, X軸範囲, Y軸範囲, プロット順序)」の形式で指定します

With ThisWorkbook.Sheets(1)
    .ChartObjects(1).Chart.SeriesCollection(1).Formula = _
        "=SERIES(" & .Range("C3").Address(External:=True) & "," & _
                     .Range("B6:B11").Address(External:=True) & "," & _
                     .Range("C6:C11").Address(External:=True) & ",1)"
End With

【原因】SeriesCollection.Formulaのエラー(1004)

なぜ、こんな事になってしまうのでしょうか・・・

実は、=SERIES(系列名, X範囲, Y範囲, 順序)は

Excel 内部で 系列名は常に “そのグラフが属するシート” を基準に解釈するため、

Sheet1!$C$3　のようにシート名を明示されると逆に混乱してしまうのです。

Excel は内部でこう変換しようとします：　

=SERIES(Sheet1!Sheet1!$C$3, …)→ シート名が二重になって壊れる。

【最終版】グラフのデータ範囲を自動設定するサンプルコード

Sub グラフ作成()

    Dim 系列 As Range '凡例のデータ範囲
    Dim X軸 As Range   'X軸のデータ範囲
    Dim Y軸 As Range   'Y軸のデータ範囲
    
    Dim 既存グラフ As Chart 'グラフ
    Set 既存グラフ = ThisWorkbook.Sheets(1).ChartObjects(1).Chart
    
    Dim i As Long

    '系列を順番に設定
    For i = 1 To 既存グラフ.SeriesCollection.Count
    
        With ThisWorkbook.Sheets(1)
            Set 系列 = .Cells(3, 2 + i)  'i個目の系列名
            Set X軸 = .Range("B6:B11")
            Set Y軸 = .Range(.Cells(6, 2 + i), .Cells(11, 2 + i)) '系列iのデータ範囲
        End With
        
        With 既存グラフ.SeriesCollection(i)
            .Formula = "=SERIES(" & 系列.Address(External:=True) & "," & _
                                X軸.Address(External:=True) & "," & _
                                Y軸.Address(External:=True) & "," & i & ")"
        End With
    Next

End Sub