Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    More bubbles (Excel 2000)

    I've a Bubble Chart to which I wish to add an extra 80 series. I recorded a macro adding one, deleted the series I just created and ran it again to re-create it, but received the error message 'Unable to set the Values property of the series class'?! Am I missing something? Thanks in advance, Andy.

    Sub Macro1()
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartType = xlBubble
    ActiveChart.ChartType = xlBubble
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(21).XValues = "='Customer prioritization 1'!R32C4"
    ActiveChart.SeriesCollection(21).Values = "='Customer prioritization 1'!R32C5" 'error created at this line
    ActiveChart.SeriesCollection(21).Name = "='Customer prioritization 1'!R32C2"
    ActiveChart.SeriesCollection(21).BubbleSizes = "='Customer prioritization 1'!R32C3"
    ActiveChart.ChartType = xlBubble
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More bubbles (Excel 2000)

    Try this:

    With ActiveChart.SeriesCollection.NewSeries
    .XValues = Worksheets("Customer prioritization 1").Range("D33")
    .Values = Worksheets("Customer prioritization 1").Range("E33")
    .Name = Worksheets("Customer prioritization 1").Range("B33")
    .BubbleSizes = "='Customer prioritization 1'!R33C3"
    End With

    Note that XValues, Values and Name are set to a range, and BubbleSizes is set to a string in R1C1 format. You could also use

    .BubbleSizes = "=" & Worksheets("Customer prioritization 1").Range("C33").Address(External:=True, ReferenceStyle:=xlR1C1)

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More bubbles (Excel 2000)

    Tried this but the same error occurs. I've attached the file in zip format. It has the macro, but you may wish to not enable the macro - it is the same as that indicated by Hans.
    I can run the macro to Add a new series, set its XValues and Bubble Size, but it collapses when it attempts to add Values and Name. This is bizarre behaviour?! Help!!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: More bubbles (Excel 2000)

    The macro fails because it tries to add a bubble based on data in B32:E32, but those cells are blank. If you enter (valid) data in those cells, the code should run without problems.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More bubbles (Excel 2000)

    I've tested this at work and it seems to resolve the problem by having sample data. I can then remove the sample data after the macro's done it's job, and I'll do this on my file when I get home. It's strange that I didn't need sample data when I did the same thing for the other Bubble Chart?

    Thanks a lot, Andy.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: More bubbles (Excel 97SR2) !!!!!!

    > It's strange that I didn't need sample data when I did the same thing for the other Bubble Chart?

    I'm struggling with what might be a related problem. <font color=red><big>Excel 97 SR2</big> </font color=red>

    I'm trying to create a bubble chart via VBA, without resorting to a user's Excel range of cells. (FWIW I need to obtain the data from Excel cells, but re-arrange the sequence on the fly, hence I want to load the X and y and size values from arrays).

    I struggled to understand the recorded VBA code (Excel97SR2) and have come to the conclusion that the Bubble type, in 97SR2, was a new thing for MSoft. It seems to me that whenever they add a new thing, it does not adhere to inhouse standards. It's as if the mechanism for creating a bubble chart is a tad different from the standard mechanisms (columns, stacked columns, lines etc.).

    The code below works, but I feel uncomfportable with the original series (ActiveChart.SetSourceData Source:=Sheets(strSheetName).Range("A3:c7"), PlotBy:=xlRows) and its subsequent deletion (ActiveChart.SeriesCollection(3).Delete ActiveChart.SeriesCollection(2).Delete).

    For the time being I have a mechanism to add a bubble chart programatically. i already have the utility code to format charts, fix titles etc., but getting the basic chart up on deck has been a troublesome six hours, partly because I still don't understand the relationship between the different objects floating around in the chart.

    I get various "Illegal Operation" messages when I err, most times I would have expected some kind of VBE disgnostic error. That too leads me to believe that the bubble chart creation code isn't adhering to error-trapping standards of MSoft.

    Basic code
    <pre>Public Function BuildBubbleChart(strXr() As Single, strYr() As Single, strSr() As Single)
    Dim strSheetName As String
    strSheetName = ActiveSheet.Name
    Charts.Add
    ' This works, but I don't really understand why.
    ActiveChart.SetSourceData Source:=Sheets(strSheetName).Range("A3:c7"), PlotBy:=xlRows
    ActiveChart.SeriesCollection(3).Delete
    ActiveChart.SeriesCollection(2).Delete

    ActiveChart.Location Where:=xlLocationAsObject, Name:=strSheetName
    ActiveChart.ChartType = xlBubble
    ActiveChart.SeriesCollection(1).XValues = strXr
    ActiveChart.SeriesCollection(1).Values = strYr
    ActiveChart.SeriesCollection(1).BubbleSizes = strSr
    ActiveChart.ChartType = xlBubble
    End Function
    Sub TESTBuildBubbleChart()
    ' First series, First data points
    Dim strXr() As Single
    ReDim strXr(3)
    strXr(0) = "-10"
    strXr(1) = "-20"
    strXr(2) = "-30"
    strXr(3) = "-40"
    ' First series, second data points
    Dim strYr() As Single
    ReDim strYr(3)
    strYr(0) = "-10"
    strYr(1) = "0"
    strYr(2) = "10"
    strYr(3) = "20"
    ' Bubble sizes
    Dim strSr() As Single
    ReDim strSr(3)
    strSr(0) = ".3"
    strSr(1) = ".5"
    strSr(2) = ".7"
    strSr(3) = ".9"
    Call BuildBubbleChart(strXr, strYr, strSr)
    End Sub
    </pre>


Posting Permissions

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