Lock chart data

[tactps]

I have a chart that I am creating in VBA. Once it has been created, I need to keep the data (ie cut the link to the source data) so that the graph doesn't change when I add a new graph by the same macro.

Is there a way in VBA to set the source data, and then keep the values plotted?

 

[MartinK]

Hello,
yes there is:

1) Manual way: click on the series, go to formula bar, press F9 - the dynamic data will be changed to matrix (arrays)

2) Manual (can be automated) - export chart as GIF.

3) VBA way:
This code does the same as 1).
You have to tweak it if your series ranges are not adjacent. Works well for me for simple charts on rectangular areas (max three list separators in the Series formula).

Martin

<pre><SPAN style="color:#00007F">Sub</SPAN> FreezeChart()
<SPAN style="color:#00007F">Dim</SPAN> myChart <SPAN style="color:#00007F">As</SPAN> Chart
<SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Set</SPAN> myChart = ActiveWorkbook.Sheets(1).ChartObjects(1).Chart
Debug.Print (myChart.SeriesCollection.Count)
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> myChart.SeriesCollection.Count
<SPAN style="color:#00007F">Call</SPAN> FreezeValues(myChart, i, "Y")
<SPAN style="color:#00007F">Call</SPAN> FreezeValues(myChart, i, "X")
<SPAN style="color:#00007F">Call</SPAN> FreezeValues(myChart, i, "N")
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> FreezeValues(myChart <SPAN style="color:#00007F">As</SPAN> Chart, Series <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, What <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
<SPAN style="color:#007F00">'What: Y for Y values, X for X values, N for SeriesName</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Sf <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> CommaCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> Commas() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> ListSep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> * 1
<SPAN style="color:#00007F">Dim</SPAN> Temp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">With</SPAN> myChart
Sf = myChart.SeriesCollection(Series).FormulaLocal
CommaCnt = 0
ListSep = Application.International(xlListSeparator)
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Len(Sf)
<SPAN style="color:#00007F">If</SPAN> Mid(Sf, i, 1) = ListSep <SPAN style="color:#00007F">Then</SPAN>
CommaCnt = CommaCnt + 1
<SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> Commas(CommaCnt)
Commas(CommaCnt) = i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> What
<SPAN style="color:#00007F">Case</SPAN> "Y"
Temp = Mid(Sf, Commas(2) + 1, Commas(3) - Commas(2) - 1)
y = Range(Temp)
.SeriesCollection(Series).Values = y
<SPAN style="color:#00007F">Case</SPAN> "X"
Temp = Mid(Sf, Commas(1) + 1, Commas(2) - Commas(1) - 1)
y = Range(Temp)
.SeriesCollection(Series).XValues = y
<SPAN style="color:#00007F">Case</SPAN> "N"
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Commas) > 2 <SPAN style="color:#00007F">Then</SPAN>
Temp = Mid(Sf, InStr(1, Sf, "!") + 1, Commas(1) - 1 - InStr(1, Sf, "!"))
.SeriesCollection(Series).Name = ActiveSheet.Range(Temp).Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><//PRE>

 

[tactps]

Holy cow! Thanks for the information, but I'll need some time to digest.

I may create VBA to save each graphs as a separate file with the data sheet before processing the next.

Either way, your help is invaluable.

Thanks