Excel VBAでグラフの範囲をグラフタイトル＋複数列（配列）で設定する

以前Excel VBAでグラフをタイトルで操作するコードを書きました（→記事）

今回は、バラバラに並んだ項目名から、そのグラフに必要な列を検索し、グラフの範囲指定を自動化します。

実務では、Alteryxでビッグデータから作った表の項目が、毎回違う場合でも、同じフォーマットに出力するために作りました。やっぱりまだExcelのグラフの方がフレキシブルで綺麗なので。

こんな表から、

定型フォーマットのグラフ一覧へ、データをセットするコードです。

1. ユーザー定義クラス graphInSheet

Public graphSet As Dictionary
Private targetSheet As Worksheet
 
Public Function setSheet(mySheet As Worksheet)
 
    Set targetSheet = mySheet
 
    With targetSheet
 
        Set graphSet = New Dictionary
 
        Dim chart
        For Each chart In targetSheet.ChartObjects
            graphSet.Add chart.chart.ChartTitle.Text, chart.Name
        Next
    
    End With
 
End Function
  
Public Function setSourceData(GraphTitle, setRange As Range)
 
    With targetSheet
 
        Dim currentChart As chart
        Set currentChart = .ChartObjects(graphSet(GraphTitle)).chart
               
        currentChart.setSourceData Source:=setRange
 
    End With
 
End Function
 
 
Public Function setCategoryMinMax(GraphTitle, MinScale, MaxScale)
 
    With targetSheet
 
        Dim currentChart As chart
        Set currentChart = .ChartObjects(graphSet(GraphTitle)).chart
        currentChart.Axes(xlCategory).MinimumScale = MinScale
        currentChart.Axes(xlCategory).MaximumScale = MaxScale
       
    End With
 
End Function

2. メインコード「RangeをUnionする」

Public Sub setDataToGraph()
       
    Dim payment, payments
    payments = Array("現金", "カード", "PayPay")
    
    '今回はグラフとデータを別にしましたが、同じシートでも可
    Dim graphs As New graphInSheet
    graphs.setSheet ThisWorkbook.Worksheets("graph")
 
    Dim dataSheet As Worksheet
    Set dataSheet = ThisWorkbook.Worksheets("data")
         
    Dim region
    For Each region In graphs.graphSet
                                
        Dim dataColumns As New Dictionary
        Dim setRange As Range, lastRow
                                
        With dataSheet
                                
            '横軸(項目に対して固定)を設定
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            Set setRange = .Range(.Cells(1, 1), .Cells(lastRow, 1))
                                
            '各グラフに必要な項目の列を収集
            dataColumns.RemoveAll
            For Each payment In payments
                dataColumns.Add .Rows(1).Find(region & "_" & payment).Column, Null
            Next
                              
            '列からRangeの集合を作成し、グラフにセット
            Dim setColumn
            For Each setColumn In dataColumns
                
                Set setRange = Union(setRange, .Range(.Cells(1, setColumn), _
                                                                   .Cells(lastRow, setColumn)))
            Next
        End With
        
        graphs.setSourceData region, setRange
    
    Next
       
End Sub

一番左の横軸となる列を範囲指定し、そのRangeへUnionで必要な列を追加していきます。できあがったRangeをChartObjectにsetSourceDataで入れればいいのですが、グラフのタイトルでなく、「Chart12」などとExcelで決められたオブジェクト名で指定するのが不便なため、ユーザー定義クラスでシート中の全グラフを保持しておきます。

ただし、グラフタイトルが被るとうまく動作しませんので、別の方法を考えなければいけません（結局Chart番号？？）。