Unlink chart from sheet

[Gerry Verschuuren]

How can I replace ranges in a chart's SERIES formula with their current
values? I know how to do this in Excel, but how would I do this in VBA? Thanks

 

[EricG]

If you know how to do it in Excel, have you tried recording a macro to see
what the VBA would look like to do the same thing? That would be my first
step.

HTH,

Eric

 

[Peter T]

You can store your chart data as arrays in the relevant sections of the
series formula, providing each section has no more than an absolute maximum
of 255 characters. Eg this array of three values has 14 characters

{1.23,2,3.456}

Programmatically it might be as simple as doing this -

Sub test()
Dim cht As Chart
Dim sr As Series
Set cht = ActiveChart
For Each sr In cht.SeriesCollection
sr.Name = sr.Name ' if linked to cell
sr.Values = sr.Values
sr.XValues = sr.XValues ' only do if the formula includes x-values
Next
End Sub

Of course you might also need to do similar with titles. There's a different
approach if the each set of data will amount to more than 255, see thread in
this ng starting 13-May-2010, subject
".Seriescolection(n).formula - size limitation?"

Regards,
Peter T