Consulting

Results 1 to 5 of 5

Thread: Solved: Creating a Static Chart

  1. #1
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location

    Solved: Creating a Static Chart

    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!

    Johannes

  2. #2
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    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

    [VBA]
    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
    Else
    Values = cell.Value
    End If
    End If
    Next

    MsgBox Values
    End Sub

    [/VBA]
    The most difficult errors to resolve are the one's you know you didn't make.


  3. #3
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location

    Thumbs up

    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!

  4. #4
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    341
    Location
    Hi,

    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
                Next
            End With
        Next
    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
    Cheers
    Andy

  5. #5
    VBAX Regular
    Joined
    Aug 2004
    Posts
    22
    Location
    Andy,


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

    Totally grateful and in awe,
    Johannes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •