View Full Version : Solved: Creating a Static Chart

09-01-2004, 09:22 AM
Hi, every month I am distributing a set of reports containing several charts. The reports that I distribute are copies where I have converted formulaes to values. In order to create "dead" charts I go through the charts, click each chart series, press F2 and then F9 to convert the range references to arrays.

The alternative is to convert the chart to a picture, but I don't want to to that as I believe it increases the size of the workbook, and this alternative is not really much less timeconsuming and tedious than the other one!

So, how can I use VBA code to loop through each graph, and each series within the graphs and convert the range references to arrays?

I can always manage to write the code that loops through the charts and series, but the tricky thing that I cannot see any solution for is how to do the actual convertion from range refernces to arrays.

Any ideas will be greatly appreciated!


09-01-2004, 10:30 AM
I'm not sure how to integrate this into your code, but it will take a range object and translate it into a comma seperated string. I think you should be able to use it for what you want. Let me know

Private Sub CommandButton1_Click()
Dim Test As Range, cell As Range
Dim Values As String
Set Test = Range("a1:a10")
For Each cell In Test
If cell.Value <> "" Then
If Values <> "" Then
Values = Values & "," & cell.Value
Values = cell.Value
End If
End If

MsgBox Values
End Sub

09-01-2004, 02:00 PM
Thank you, I did not have time to try this out yet, but it looks like something that could work. Ideally, I do not want to need to know what the source range is when writing the code, but in one of John Walchenbach's books I have found a description of how to parse the series function to find a chart's source range, so the two of you together may just have solved my problem!

Can't wait to try it out!

Andy Pope
09-02-2004, 02:01 AM

This will convert the cell references to array values.

Sub MakeDeadChart()
Dim intSeries As Integer
Dim objChart As ChartObject

For Each objChart In ActiveSheet.ChartObjects
With objChart.Chart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
.XValues = .XValues
.Values = .Values
.Name = .Name
End With
End With
End Sub

Depending on the amount of data in your charts the process may bomb, as there is a limit the the length of the resulting formula

09-02-2004, 10:09 AM

It works! It is elegant! Even brilliant! And so simple! And runs so fast!

Totally grateful and in awe,