Unable to set formula property of series

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi ,
    I went through a lot of threads in this forum, although there were a couple relating to the same problem am facing, they didnt solve my error .. so here's my question again ..
    When am trying to delink charts , i get an error stating "Unable to set the formula property of series class".
    The code is as follows :


    I get the error at the line "ChtSeries.Formula ". I get the error when the

    Code
    yArray var = "268.6 ,216.4 ,289.2 ,15.5 ,462.0 ,214.1 ,183.7,303.6 ,413.6, 353.9,314.6,403.7,	665.0,374.1,500.6,	583.5,552.6,550.8,	586.3 "


    but if I had to recude the yArray var from "268.6 - 500.6", it works fine .. not sure if its got anything to do with the length of the array... Any help is highly appreciated. Thank You !

    Re: Unable to set formula property of series


    There is a limit on how long a string of values can be passed to a series formula. Your best bet is to store the data in a worksheet and then use that range for the series.

    Re: Unable to set formula property of series


    Hi Derk,


    Thx for your reply :exclamat: .. However, am not entirely convinced. If possible please try to have a look at the xl attachment. It has 3 series. The lenght of the first series is around 59 ... Am able to convert this properly w/o any errors, Am getting the error in the 2nd & 3rd series ... The lenght of the 3rd series is around 56, (lesser than the 1st series) yet am getting this error .... that being the reason I say am not entirely convinced with your reply ... Any one can solve this ??

    Re: Unable to set formula property of series


    Hi,


    Try this modification[vba]Sub DelinkChartData()

    Dim iCtr As Integer, iChars As Integer, SH As Shape, iPlotOrder As Integer
    Dim nPts As Long, iPts As Long 'Holds the total no of points in the chart
    Dim xArray, yArray, sChtName As String, sSrsName As String
    Dim xVals, yVals
    Dim ChtSeries As Series ' var used to loop thru the series collection
    Dim ws As Worksheet
    Dim sChartType As String, iCtr1 As Integer

    For Each ws In ActiveWorkbook.Sheets
    If Left(ws.Name, 3) = "Sch" Then
    ws.Activate
    For Each SH In ws.Shapes
    SH.Select
    If SH.Type = msoChart Then
    sChtName = ActiveChart.Name
    For Each ChtSeries In ActiveChart.SeriesCollection
    nPts = ChtSeries.Points.Count
    xArray = ""
    yArray = ""
    xVals = ChtSeries.XValues
    yVals = ChtSeries.Values
    sSrsName = ChtSeries.Name
    iPlotOrder = ChtSeries.PlotOrder
    For iPts = 1 To nPts
    If IsNumeric(xVals(iPts)) Then
    ' SHORTEN NOS IN X ARRAY (REMOVING EXCESS DIGITS)
    iChars = WorksheetFunction.Max(InStr(CStr(xVals(iPts)), "."), 5)
    'xArray = xArray & Round(Left(CStr(xVals(iPts)), iChars), 0) & ","
    xArray = xArray & Left(CStr(xVals(iPts)), iChars) & ","
    Else
    'PUTTING QUOTES AROUND STRING VALUES
    xArray = xArray & """" & xVals(iPts) & ""","
    End If
    'SAME AS X (ABOVE)
    iChars = WorksheetFunction.Max(InStr(CStr(yVals(iPts)), "."), 5)
    'On Error Resume Next
    'yArray = yArray & Round(Left(CStr(yVals(iPts)), iChars), 0) & ","
    'On Error GoTo 0

    ''' handle missing data - replace blanks and #N/A with #N/A
    If IsEmpty(yVals(iPts)) Or WorksheetFunction.IsNA(yVals(iPts)) Then
    yArray = yArray & "#N/A,"
    Else
    ' NEED TO ROUND NUMBERS ELSE THROWS ERROR
    yArray = yArray & Round(Left(CStr(yVals(iPts)), iChars), 0) & ","
    End If
    Next
    'REMOVE FINAL COMMA
    xArray = Left(xArray, Len(xArray) - 1)
    yArray = Left(yArray, Len(yArray) - 1)


    With ChtSeries
    .Values = yArray
    .XValues = xArray
    .Name = sSrsName
    .PlotOrder = iPlotOrder
    End With

    '' ChtSeries.Formula = "=SERIES(""" & sSrsName & """,{" & xArray & "},{" & yArray & "}," & CStr(iPlotOrder) & ")"

    Next
    End If
    Next SH

    End If

    Next

    End Sub[/vba]


    For the 3rd series, which is a line, if you replace the #N/A text in the Chart Data sheet with =NA() you will not have to worry about the line dropping to zero when there is no data. And having to change the colour of the line half way along.

    [h4]Cheers
    Andy    [/h4]

    Re: Unable to set formula property of series


    Hi Andy,


    Thx a lot, the code now works like a charm.. I shld have tried out that mod myself :rolleyes: .. But thank you again. :) & thx for the tip on the 3rd series.
    But can you let me know what was causing the error? I mean why was that occurring? Was it because of the reason that Derk had pointed out?

    Re: Unable to set formula property of series


    I can not state with any certainity what the problem is.


    You last series had a length of +255 which might be a problem BUT I think series 2 was even longer.
    It might be a problem with the #N/A's but again I think series 1 had those.
    So maybe it is a combination of the 2.

    [h4]Cheers
    Andy    [/h4]

    Re: Unable to set formula property of series


    Hi ..
    Now that the chart code works fine thx to the mod by Andy Pope , am stuck with something else related to this ... more to do with the formatting of the numbers.
    If i have the numbers formatted eg :- 50,000 to be shown as 50 in 1 series...when i use the above code to format the offline chart .. it changes the values to 50,000 whereas i want the number format to be the same .. in this case 50.
    ANy idea how this can be done?? thank you ...

    Re: Unable to set formula property of series


    Ok .. solved this .. just had to explicitly format each series & make sure the "linked to source" checkbox is unticked.

    Re: Unable to set formula property of series


    Hi Again Excel Guru's ...


    Thanks for your help for fixing the previous problem @ Andy ... I seem to have been stuck again .. In the attachment (chart1) you will notice, that Series 4 throws an error stating "Unable to get the points property" ... It's because this series has no values I guess ... but then the other series dont have values either but they seem to be getting converted to static values ....Really strange !


    Please help !! I just cant understand what's going wrong !

    Re: Unable to set formula property of series


    It's a combination of the empty cells and the chart type.
    Column charts appear to be more forgiving than line charts.


    Try this mod to the code[vba] For Each ChtSeries In ActiveChart.SeriesCollection
    ' remember chart type
    lngChtType = ChtSeries.ChartType
    ' make it a column whilst we delink data
    ChtSeries.ChartType = xlColumnClustered
    nPts = ChtSeries.Points.Count
    '
    ' snipped code
    '


    ' reset chart type
    ChtSeries.ChartType = lngChtType

    Next[/vba]

    [h4]Cheers
    Andy    [/h4]

    Re: Unable to set formula property of series


    Thx again Andy .. Like you said, it's the line chart type that seems to be creating the problem ...I replaced xlColumnClustered with xlColumnStacked .. because the charts werent being converted to their original chart type when xlColumnClustered is used ... Again, I dont really know why,may be you have an answer. ... (happens when there are numbers in the series ) ... Thanks again ! You ve been extremly helpful..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login