Resolving Runtime 424 Error; ActiveChart.SetSourceData

Question

Good afternoon,

Beginner working with a legacy macro written in 2002. Macro simply compiles data from different workbooks and creates a new file with a chart. Upgrading PC from Excel 2003 to present has caused a handful of bugs that mostly have been resolved. One of the last remaining problems is the sub that builds the charts. The initial error (1004) occurred at the line

ActiveChart.SetSourceData Source:=Sheets("Data").Range(_
PlotCells), PlotBy:=XlColumns

After some research I found info here...https://social.msdn.microsoft.com/Forums/en-US/ce290e29-634b-4383-9bb5-fddda8db3974/excel-macro-not-able-to-setsourcedata-for-chart?forum=isvvba that led me to change that line to

ActiveChart.SetSourceData Source:=PlotCells, PlotBy:=xlColumns

The 1004 error no longer occurs but now its been replaced by a 424 error. Unsure what is the cause as I believe I dimensioned everything properly and passed data from one sub to the next. PlotCells contains a value when debugging. Full Sub posted below.

Sub Build_Chart(ByVal File_Path As String, ByVal NumFreq As String, ByVal NumTemp As String, ByRef FreqV() As String)
    '
    ' Macro6 Macro
    ' Macro recorded 1/11/02 by Edward P. Smith
    '
    ' Making the Plot

        
    Dim X_Column, FCell, PlotCells, ii, tt, Freq, Cell, W_Book
    
    
    X_Column = "J"
    FCell = NumFreq + 3
    PlotCells = X_Column & FCell & ","
        
    Workbooks.Open Filename:=File_Path
    W_Book = ActiveWorkbook.Name
        
    'Add the Chart
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
        
    For ii = 1 To NumFreq
        'Get the values to Plot
    
        For tt = 1 To NumTemp - 2
            If Not tt = NumTemp - 2 Then
                PlotCells = PlotCells & X_Column & (FCell + tt * (NumFreq + 1)) & ","
            Else
                PlotCells = PlotCells & X_Column & (FCell + tt * (NumFreq + 1))
            End If
        Next tt
        If ii = 1 Then
            ActiveChart.SetSourceData Source:=PlotCells, PlotBy:=xlColumns
                ActiveChart.Location Where:=xlLocationAsNewSheet
        Else
            Sheets("Data").Select
            Range(PlotCells).Select
            Selection.Copy
            Sheets("Chart1").Select
            ActiveChart.PlotArea.Select
            ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=False, _
                CategoryLabels:=False, Replace:=False, NewSeries:=True
        End If
        
        FCell = FCell + 1
        PlotCells = X_Column & FCell & ","
    Next ii
        
    ' Title the Chart and Axis
    
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "PPM Change"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Temperature"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "PPM"
    End With
    
    ' Name the plot series
    
    For ii = 1 To NumFreq
        ActiveChart.SeriesCollection(ii).Name = "=""" & MyRound(FreqV(ii - 1), 2) & " GHz"""
    Next ii
      
    ' Set the X-Axis values to the temperatures
    For ii = 1 To NumFreq
        ActiveChart.SeriesCollection(ii).XValues = _
            "=Data!R" & ((NumFreq + 1) * NumTemp) + 4 & "C1:R" & (((NumFreq + 1) * NumTemp) + 3) + (NumTemp - 1) & "C1"
    Next ii
        
    ' Set the Scale to our default values
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = -1000
        .MaximumScale = 2000
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlCustom
        .CrossesAt = -1000
        .ReversePlotOrder = False
        .ScaleType = xlLinear
    End With
    
    ActiveChart.Axes(xlCategory).Select
    
    With ActiveChart.Axes(xlCategory)
        .MinimumScaleIsAuto = True
        .MaximumScaleIsAuto = True
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlCustom
        .CrossesAt = -1000
        .ReversePlotOrder = False
        .ScaleType = xlLinear
    End With

    ActiveChart.PlotArea.Select
    
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Interior.ColorIndex = xlNone

    Windows(W_Book).Activate
    ActiveWorkbook.Save
    
    'MyDelay 20
    
    ActiveWindow.Close


End Sub

Any help appreciated. Thank you.

Answer 1

The initial 1004 "Method 'Range' of object '_Worksheet' failed" Error was probably caused by the length of PlotCells exceeding the maximum number of characters(255) that a Range will accept.

The 424 "Object required" Error is being thrown because Source is expecting a Range object not a string.

If Len(PlotCells) > 255 then you will have to use Union() to build your range.