HELP WITH VBA & BUBBLE CHARTS IN EXCEL

HELP WITH VBA & BUBBLE CHARTS IN EXCEL

Post by Gary J. Mars » Tue, 19 Aug 1997 04:00:00



I've encountered problems using VBA code to change the data labels
in bubble charts.  Whether or not the VBA code gives an error seems
to be determined by the size of the each individual bubble on the
chart.  

To reproduce the problem and see what I'm talking about, do the
following:

- Using Excel97
1) Fill range A1:B200 using =RAND()*10 and range C1:C200 using
=RAND()*100.  Then convert the equations to values so it doesn't
keep changing on you.
2) Create a bubble chart using column A = X,  B=Y, C=bubble-size
3) Right click on a bubble and choose Format Data Series->
  Options -> Scale bubble size to 30%
4) Create a range of labels (perhaps 1,2,3...) in range D1:D200
and assign the name "labels" to the range
5) Run the following VBA code and you'll see the error I'm getting

    Sub addlabels()
      'assumes labels are in range "labels"
      'select the appropriate worksheet first
      Set LabelRange = Range("labels")
      With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        .ApplyDataLabels
        For i = 1 To .Points.Count
          .DataLabels(i).Text = "=" & LabelRange.Cells(i).Address _
             (ReferenceStyle:=xlR1C1, external:=True)
        Next
      End With
     End Sub

I'm getting a "Unable to set the Text property of the Datalabel class"
message.  However, I only get this error when the VBA code encounters
one of the datapoints with a small bubble size.  The VBA code cruises
right through the bigger bubbles just fine.  Resizing the graph to
occupy more screen space seems to have an effect also.  Changing the
percentage used for bubble scaling seems to have an impace as well.

Is it just me? or is this really weird and unexpected behavior?  Any
explanations, or even better.... solutions to avoid the problem
would be appreciated.

--
Gary J. Marsh
**to reach me via email, change the following names in my
email address
spam  -> gary
be   ->   j
gone ->  marsh

 
 
 

1. Excel97,VBA, Bubble Chart, and Datalabels

We have some bubble charts in Excel97 that show bubble size/color
relative to an oil-well's production rate.  Setting up the
bubble chart hasn't been a problem, except that we'd like for the
bubbles to have datalabels that show an abbreviate wellname.  

Simplified, our datafile looks like:

Well-label   Xcoord   Ycoord     Bubblesize
A1            1.4      2.3         234
A2            4.6      6.2         834
B1            9.3      2.7         340
...

What we have already is a bubble chart that uses the X/Y coordinates
and bubble size based on bubble size.  We'd like to use the labels
from a separate column (e.g. "A1" ) on the bubble map as datalabels.

A co-worker of mine copied the following VBA code out of a PC
magazine's spreadsheet help section.  It is intended to add the
labels as I want, but it doesn't seem to work on my bubble charts.
I'd appreciate any help making this work for bubble charts, or an
explanation as to why it won't work on bubble charts.

Here's the code:
Sub addlabels()
'assumes labels are in range "labels"
'select the appropriate worksheet first
Set LabelRange = Range("labels")
With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
    .ApplyDataLabels
    For i = 1 To .Points.Count
        .DataLabels(i).Text = "=" & LabelRange.Cells(i).Address _
            (ReferenceStyle:=xlR1C1, external:=True)
    Next
End With
End Sub

When I run this code with a "labels" range defined and my bubble
chart selected, I get the following behavior:

1) "Set LabelRange" VBA statement works fine
2) ".ApplyDataLabels" VBA statement works fine
3) At the first value of "i", I get a
     "Run-time error '1004':  Unable to set the Text property
      of the Datalabel class

I suspect that this is because datalabels aren't the same for
bubble charts as they are for x-yscatter plots, but I'm not too
sure about this.

Thanks in advance to everyone who can help!

--
Gary J. Marsh
**to reach me via email, change the following names in my
email address
spam  -> gary
be   ->   j
gone ->  marsh

2. Converting THINK C v5.0 projects

3. Bubble charts in Excel

4. question about hp6200 scsi or usb?

5. Bubble Charts In Excel

6. Computer Name Conflict

7. Bubble Chart in Excel

8. MS flight shop available?

9. Bubble charts or Data Labels in Excel 4

10. Excel 97 VBA Chart Help

11. HELP! VBA code cares about bubble size???

12. Excel 5 - How to use Add-Ins Analysis functions in VBA

13. EXCEL 5 - VBA Add ins (removing)