UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Create Bubble-Pie Chart    
 
   
larryho
post Aug 25 2005, 09:56 AM
Post#1



Posts: 2
Joined: 23-August 05



Like to create a bubble map with pie-chart inside the bubbles to show several items that are contributed to the size of the bubble (i.e. The size of the bubble represents the total sales revenue. The pie-chart inside the bubble shows 30% of the sales revenue is from vegetables, 20% from drinks, 10% from dry goods and the remaining % is from meat).
Please provide detail step by step procedure to generate such a chart using Excel chart function. If the Macro function is required to be use, please explain how it works. I have never used the Macro function before and I only have the very basic skill in Excel.
Thank you very much.
Larry
Go to the top of the page
 
Luceze
post Aug 25 2005, 10:34 AM
Post#2


UtterAccess VIP
Posts: 2,601
Joined: 30-July 03
From: Dallas, Texas USA


Hi Larry,
artin has a very nice example in the Excel FAQ section. LINK
THe stops by regularly if you have any questions.
HTH,
Go to the top of the page
 
KingMartin
post Aug 25 2005, 04:27 PM
Post#3


Retired Moderator
Posts: 10,959
Joined: 9-October 03
From: Prague,CZ / Kiev,UA


I had some PM exchange with Larry and encouraged him to post a question regarding my FAQ contribution in the ordinary Excel forum... thanks for the nice words though Eric frown.gif
ello Larry,
welcome to UA forums! wink.gif
My method (I really invented it, but I am pretty sure that you find the same or similar approach on the net frown.gif ) uses few tricks.
First of all, there are three charts involved.
One is obvious, bubble chart called "MainChart", that gets its data from the columns A, B, and H of my example.
The second one may be less obvious, it's a bar chart where the axes are invisible and that is laid over the Main chart and serves as a legend. I customized the colors of the data points to correspond with the...
... the third pie chart, called "PieChart", that is invisible in my FAQ post. I made it visible and attach my file again. You can make it visible by running
CODE
Sheets("Chart").Chartobjects("piechart").visible = true

Now, I have commented my code so that it's more obvious what the code does:
CODE
Sub BubblePie()
    Dim cl As Range
    Dim i As Long
    Let i = 1
    Application.ScreenUpdating = False
    With Sheets("Chart")
        For Each cl In .Range(.[A5], .[a65536].End(3))
        'here we fill the PieChart (invisible) with the data in the rows,
        'C5:G5, C6:G6, etc.
        '
            With .ChartObjects("PieChart").Chart
                .SeriesCollection(1).Values = cl(, 3).Resize(, 5)
                '
                'after we filled the chart with data, we copy it as picture...
                .CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
            End With
            '
            '... and copy it as picture back onto the sheet
            .Paste
            '
            'now we apply some 'nice' :-) formatting to the picture we pasted...
            With Selection
                With .ShapeRange.Shadow
                    .Type = msoShadow6
                    .ForeColor.SchemeColor = 8
                    .Visible = msoTrue
                    .IncrementOffsetX 2#
                    .IncrementOffsetY 2#
                End With
                '
                '... copy it as picture again (together with the formatting)...
                .CopyPicture Appearance:=xlScreen, Format:=xlPicture
                '
                '...and delete it, as we don't need it anymore.
                .Delete
            End With
            '
            'Finally, we paste the copied formatted picture as the data point...
            With .ChartObjects("MainChart").Chart
                .SeriesCollection(1).Points(i).Paste
            End With
            '
            '... move to the next data point...
            i = i + 1
            '
            '... and move to next row of the data
        Next cl
    End With
End Sub

Finally, I use a Worksheet_Change event to accomodate the chart when the data in the named range "TheRange" changes. TheRange is defined as C5:G9 in my example.
From your post, I still don't see where are the X-Y coordinates. What you describe is rather an ordinary pie chart (well, several of them). Sure, using my method you could show all the pies within one chart...
If you are still not sure how to use this method, post a sample of your data. I can try to draw something to start with for you...
Best regards and enjoy UA forums,
frown.gif
Martin
Attached File(s)
Attached File  BubblePie.zip ( 179.82K )Number of downloads: 92
 
Go to the top of the page
 
larryho
post Aug 26 2005, 12:26 AM
Post#4



Posts: 2
Joined: 23-August 05



Guys, thank you for your help.
artin, the following is a sample data, would appreciate if you can help me to start the charting.
Location of Shop Sales Revenue
X Y Vegetable Meat Dry Goods Drinks Total
200 200 20 15 35 30 100
50 400 10 25 5 10 50
125 40 100 5 15 50 170
150 300 50 35 100 145 330
100 150 0 0 20 5 25
Oenjoy the UA forums. I just wish that I can have more spare time to go through the FAQs and learn. Thank you again.
Cheers,
Larry
P.S. I spaced out the numerical figures in the sample data and notice that the figures are not formatted in the send message (but is so inside the "edit" window. Is this normal? If so, is there a menthod that I can display the figures in the rows and columns with proper spacing?
Go to the top of the page
 
KingMartin
post Aug 26 2005, 03:52 PM
Post#5


Retired Moderator
Posts: 10,959
Joined: 9-October 03
From: Prague,CZ / Kiev,UA


Hi Larry,
Since you use the same number of points and groups as my original example, this was an easy one frown.gif
I just copied your data into my original file and changed the X and Y axes' maximum to 'Automatic'.
Also, I deleted the picture (map). If you want to add your own, right-click the chart, Selected Object=>Fill Efects=>Picture. Browse for the pic you need as background (and of course, play with the coordinates until the bubbles fit)
Martin
P.S. Yes, the tab doesn't work when trying to post columns. Don't worry about that, your post was perfectly legible (especially after use of Text To Columns, delimiter: Space wink.gif )
Attached File(s)
Attached File  BubblePie_forLarry.zip ( 15.27K )Number of downloads: 95
 
Go to the top of the page
 
cbprado
post Jan 28 2011, 12:31 PM
Post#6



Posts: 1
Joined: 28-January 11



Great to find the solution to my problem in a 6 year old post! However, I can´t seem to find out how to increase/decrease the number of variables. I have over 100 locations, and 6 categories... can anyone help?
Go to the top of the page
 
khegeholz3
post May 10 2019, 07:58 PM
Post#7



Posts: 1
Joined: 10-May 19



I've also been wondering on how to make this variable by column/rows I have few variables 3 within the pie and 20 data points? Let me know if you find a solution!!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd July 2019 - 01:25 AM