Type Mismatch error when setting source data for a chart equal to an array

Question

I need to populate an array with values from a range, then I want to use this array as the source data for a chart. I've tried setting the array as variant and as long and neither seem to work. Every time I try to run the code I get a type mismatch error at the line with .SetSourceData:=PlotRangeBar. Here's what I have so far:

Dim XRangeBar As Range
Dim PlotRangeBar() As Variant
Dim PlotRange As Range

Set XRangeBar = ActiveWorkbook.Sheets(2).Range("B" & DataStart & ":B" & DataEnd)
i = 0
For Row = DataStart To DataEnd
    If Cells(Row, UsedColTimesheet).FormulaR1C1 <> "0" And Cells(Row, UsedColTimesheet) <> vbNullString Then
        ReDim Preserve PlotRangeBar(i)
        PlotRangeBar(i) = Cells(Row, UsedColTimesheet).Value
        i = i + 1
    End If
Next

ActiveWorkbook.Sheets(Sheets.Count).Select
ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .ChartType = xlColumnStacked
        .SetSourceData Source:=PlotRangeBar 'Error occurs here
        .SeriesCollection.NewSeries
        .SeriesCollection(1).XValues = XRangeBar
        .SetElement (msoElementChartTitleCenteredOverlay)
        .ApplyLayout (1)
        .ChartTitle.Text = ResourceName & " - Hours per project"
        .Legend.Delete
        .ChartStyle = 18
        .ProtectSelection = True
    End With

I don't understand where the type mismatch comes from. I would very much apreciate any insight, thanks.

Jane · Accepted Answer · 2015-07-09 21:48:00Z

2

SetSourceData accepts a Range as the data type for the source parameter, not an array. This is confirmed in the MSDN documentation at https://msdn.microsoft.com/en-us/library/office/ff841196.aspx).

You'd need to include the data in a worksheet range, and use that as the source.

answered Jul 9, 2015 at 21:48

Jane

8514 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Type Mismatch error when setting source data for a chart equal to an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related