1

the following code is meant to create a bubble pie chart (a bulbble chart with pie charts as bubbles). It copies a pie chart into the bubble chart recursively. My problem is that with this method the final pie charts look a bit oval- not really round. An issue which I suspect is related to some sort of formatting. 

Sub PieMarkers()

Dim chtMarker As Chart
Dim chtMain As Chart
Dim intPoint As Integer
Dim rngRow As Range
Dim lngPointIndex As Long
Dim thmColor As Long
Dim myTheme As String


Application.ScreenUpdating = False
Set chtMarker = ActiveSheet.ChartObjects("chtMarker").Chart
Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart

Set chtMain = ActiveSheet.ChartObjects("chtMain").Chart
Set rngRow = Range(ThisWorkbook.Names("PieChartValues").RefersTo)

For Each rngRow In Range("PieChartValues").Rows
    chtMarker.SeriesCollection(1).Values = rngRow
    ThisWorkbook.Theme.ThemeColorScheme.Load GetColorScheme(thmColor)
    chtMarker.Parent.CopyPicture xlScreen, xlPicture
    lngPointIndex = lngPointIndex + 1
    chtMain.SeriesCollection(1).Points(lngPointIndex).Paste
    thmColor = thmColor + 1
Next

lngPointIndex = 0

Application.ScreenUpdating = True
End Sub

Function GetColorScheme(i As Long) As String
Const thmColor1 As String = "C:\Program Files\Microsoft Office\Document Themes 15\Theme Colors\Blue Green.xml"
Const thmColor2 As String = "C:\Program Files\Microsoft Office\Document Themes 15\Theme Colors\Orange Red.xml"
    Select Case i Mod 2
        Case 0
            GetColorScheme = thmColor1
        Case 1
            GetColorScheme = thmColor2
    End Select
End Function

I found out that the problem is solvable if double click on the spcific ubble select format data point and then go to fill and stretch options ( only possible if picture fill is selected). The problem is that my data are changing and I would need a dynamic way to implement this into the above mentioned code..is there a way to do this?

I refer to this console here http://s1.directupload.net/file/d/3300/7dlimc3g_png.htm

Undo
  • 25,519
  • 37
  • 106
  • 129
Timon Heinomann
  • 57
  • 1
  • 4
  • 10

1 Answers1

1

I think this might be the problem if your Pie chart is not a perfectly square shape. I can replicate your issue, and even when I check the Fill options, the offsets are all 0%. I can adjust them, but that is not a reliable way to do it. So, the best option I think will be to ensure that your pie chart .Parent is a square shape. To do this, before you CopyPicture, set its Height equal to its Width, like this:

chtMarker.Parent.Height = chtMarker.Parent.Width  '## Ensure the chartObject is a square, so it will not be distorted when pasted.
chtMarker.Parent.CopyPicture xlScreen, xlPicture
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • The question of how to change the `Point` color has been answered on StackOverflow, [here is one method](http://stackoverflow.com/questions/11136194/change-the-point-color-in-chart-excel-vba) and [here is another similar method](http://stackoverflow.com/questions/8495987/changing-color-of-bars-in-a-bar-chart). You will of course need to make separate assignment of color for each of 8 x 3 = 24 points. – David Zemens Jun 28 '13 at 19:32
  • I would probably write a different function. `Points` are simply a `Collection` object, so you can iterate over them just like you iterate over `Worksheets` (which is also a `Collection` object. You need to iterate over the points in each chart, and apply a color based on the point Index and chart Index value. – David Zemens Jun 28 '13 at 20:28
  • Yeah this should be a new question for you. "How do I iterate over the Points in a Series and apply different colors to them?" You should try to at least *start* that part of the code on your own. You will have a better luck receiving answers this way. – David Zemens Jun 28 '13 at 20:53
  • Not here I can't. If you want help, please post the question publicly so that **everyone** on Stack can potentially help you, rather than asking a series of never-ending follow-up questions that will **only be seen by me**. It is not fair to me. These comments are not related to the scope of your original question. Please ask a new question describing the new problem. – David Zemens Jun 28 '13 at 21:06