Prev: Calling Excel shortcuts from VBA
Next: Setting a reference to the Ribbon
From: Gerry Verschuuren on 20 May 2010 09:52
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
From: EricG on 20 May 2010 11:16
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


"Gerry Verschuuren" wrote:

> 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
From: Peter T on 20 May 2010 11:55
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


"Gerry Verschuuren" <GerryVerschuuren(a)discussions.microsoft.com> wrote in
message news:D0F084F0-1224-40FF-955F-A0C586E2E84E(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: Calling Excel shortcuts from VBA
Next: Setting a reference to the Ribbon