Lock chart data

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

MartinK

Active Member
Joined
Oct 30, 2003
Messages
384
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: :eek:
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). :biggrin:

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

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top