0

I'm trying to make some infographics based on a worldmap and bubbles, kind of like this:

Coffee infographics

Each bubble represent a country, and the size of the bubble the volume of coffee production (in this example). The exact position of the country does not really matter, as long as it's in the right regional group, but I can't let the bubbles overlap.

So I'm trying do write a macro that will adjust the X and Y coordinates of each data point, so that they don't overlap.

But for that I need to detect if bubbles overlap. What I'm thinking is simply to check the distances between the bubble centers with Xs and Ys, and compare with the bubble radius.

Hence my question: how can I determine the actual size of the bubbles? (not with the scale, but the actual size of the bubble, given the scale). I noticed they adjust based on the biggest one, which seems to be a fixed proportion of the smallest dimension of the plot area. So far it seems to be R=~ 1.29 x smallest plot area dimension, but is it always the case?

Also, if I need to change the scale, how exactly does it change the bubble size? (a scale of 200 does not seem to correspond to a sqrt(2) bigger radius)

Thanks a lot in advance!

Luc

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
reluck
  • 11
  • 2

1 Answers1

2
Sub y()

Dim c As Chart
Dim s As Series
Dim p As Point

Set c = ActiveSheet.ChartObjects(1).Chart
Set s = c.SeriesCollection(1)
Set p = s.Points(1)

Debug.Print p.Left, p.Top, p.Width, p.Height         '   Will be the same height & width as circle.

End Sub
Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20