How to set source data for chart with a named range in excel VBA

Question

I am making a chart in excel and wish to set the source data range to a named range. This is the second chart on the sheet (hence the ChartObjects(2)).


Sub PieChartCreation()

Dim shArray() As Variant
Dim cht As ChartObject, CurrentSheetName As String, ChartCount As Integer, CurrentSheet As Worksheet, riskrange As Range, datarange As Range, multirange As Range
Dim NYears As Integer

shArray = Array("TotalCholesterol", "HDL", "LDL", "TRIG", "SBP", "DBP", "BP", "Glucose", "A1C", "BMI", "Waist", "WHR", "PSA")

i = 0
NYears = Worksheets("Copy").Range("CT2").Value

For i = LBound(shArray, 1) To UBound(shArray, 1)

    Worksheets(shArray(i)).Activate

    Set CurrentSheet = Worksheets(shArray(i))

    CurrentSheetName = shArray(i)

    ChartCount = ActiveSheet.ChartObjects.Count

    Set riskrange = Range(CurrentSheet.Cells(1, NYears * 2 + 3), CurrentSheet.Cells(4, NYears * 2 + 3))
    Set datarange = Range(CurrentSheet.Cells(1, NYears * 3 + 3), CurrentSheet.Cells(4, NYears * 3 + 3))
    Set multirange = Union(riskrange, datarange)

    Set co = Sheets(shArray(i)).ChartObjects.Add(1, 1, 1, 1)
    ActiveSheet.ChartObjects(2).Activate
    co.Chart.SetSourceData Source:=Range("multirange")
    co.Chart.ChartType = xlPie
    co.Chart.HasTitle = True
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = CurrentSheetName
    Set cht = co.Chart.Parent
    With cht
    .Left = CurrentSheet.Cells(7, NYears * 2 + 11).Left
    .Top = CurrentSheet.Cells(7, NYears * 2 + 11).Top
    .Height = CurrentSheet.Range(Cells(7, NYears * 2 + 11), Cells(23, NYears * 4 + 11)).Height
    .Width = CurrentSheet.Range(Cells(7, NYears * 2 + 11), Cells(23, NYears * 4 + 11)).Width
    End With

Next i

End Sub

I get the following error on co.Chart.SetSourceData Source:=Range("multirange")

Run-time error 1004: Method Range of object '_Global' failed

I assume I just don't have the naming convention correct. How should I set this source data with a named range?

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-07-26T16:50:15

co.Chart.SetSourceData Source:=Range("multirange")

That's pulling the named range "multirange" from whatever the ActiveSheet is, and that's not what you want to be doing. multirange is a VBA object reference / local variable, not a string, not a named range. Just use it as-is:

co.Chart.SetSourceData Source:=multirange

That said, you have a number of implicit ActiveSheet references that will cause problems sooner or later. Make sure you explicitly qualify Range member calls with a proper Worksheet object, systematically.

How to set source data for chart with a named range in excel VBA

1 Answers