-
May 26th, 2009, 11:15 AM
#1
Thread Starter
Lively Member
[RESOLVED] [Excel]Is it possible to fill in areas of overlap in an excel graph?
My situation: I have an XY scatter plot that plots multiple circles. The number of circles, their size and their position is defined at runtime. What I want to do is to colour in the area of overlap where multiple circles intercept. (What I REALLY want to do is to colour areas differently depending how many circles are overlapping, but... baby steps.) Think of something along the lines of a Venn diagram.
I found a few solutions for shading underneath a line, or between two lines, that use a second Area chart under the Scatter chart and some tinkering; however the crux is that I need to be able to do this programmatically with no intervention by the user, and the fine tuning required is beyond my current VB programming abilities.
My third problem is that it needs to be fairly fast. The drawing of the circles already takes noticeable seconds and I'd rather not extend that time considerably for what is essentially a "nice feature" and not a necessary one.
So, Do any of you have any experience with this? Is it possible? Is it possible but not worth the coding effort and processing time? Is it possible but very hard to implement?
Do you have any ideas you want to throw around and discuss? I will gladly answer any questions you have concerning both the details of the program and my current level of experience.
(Just so you know, if you do think you have a solution, a plain English explanation or pseudo-code algorithm is all I'm looking for, I don't require actual coding done.)
edit: Sorry if my terminology throws you off, I'm looking at this from a straight programming perspective, not really an application-enhancing or macro perspective.
Last edited by nanoinfinity; May 26th, 2009 at 11:34 AM.
Reason: Added last sentence
-
May 27th, 2009, 09:24 AM
#2
Thread Starter
Lively Member
Re: [Excel]Is it possible to fill in areas of overlap in an excel graph?
No ideas? Is it not do-able, then?
-
Jul 2nd, 2009, 06:44 AM
#3
Thread Starter
Lively Member
Re: [Excel]Is it possible to fill in areas of overlap in an excel graph?
Bringing this up again in case anyone was interested.
I am sure there are other ways to accomplish this, but they would be way over my level of ability. However, I did find a workable solution - to an extent. It is not ideal for me, but may work for others so I will post it.
It is possible to use the Drawing tools provided by Excel to trace over the series points in a chart and create a shape out of that. You can then set the shape's properties to be different colours, patterns, and transparency. When shapes with a non-zero and non-100 transparency overlap, they create a visual indicator (in the form of a darker colour) of the overlap.
If you have a lot of shapes on top of your chart (even partially transparent ones), you may have to draw another set of shapes on top of those ones, but have the fill 100% transparent and leave just a border to represent your original lines. Shapes float above a chart, not under it, so non-transparent or faintly-transparent shapes will obscure your chart liens.
My issues was that 1) I cannot use the slightly better freeformbuilder method (used in the link where I got my first example) because apparently it is bugged and will crash if given nodes too close together... I believe nodes with a distance between them of > 1, but I haven't tested it fully. My nodes are usually at a distance of 0.0002 (Before alteration by the formula), so you can see it won't work.
2) The shapes do not render very efficiently, so having many shapes with half-transparency made scrolling the page rather buggy, as the shapes redrew themselves every time the page moved significantly. This was not as much an issue with opaque shapes.
3)Generation of the shapes is a bit time consuming, and again, having many shapes (Like I do) can make this solution impractical.
I am now hoping to find some magical way to merge shapes that touch each other together into a single larger shape, but doubt I'll have any luck with that.
vb Code:
'----The button event where we actually draw the chart Private Sub cmdPlot_Click() '---Add series to chart stuff here '---the following is where we fill in the circles For i = 1 To Worksheets(Constants.GRAPH_SHEET).ChartObjects(1).Chart.SeriesCollection.count DrawFilledPolygon(Worksheets(Constants.GRAPH_SHEET).ChartObjects(1).Chart, i, 25, 0.75) Next i End Sub '---Read more: [url]http://www.peltiertech.com/Excel/Charts/VBAdraw.html#FilledPolyCode#ixzz0JvGqwjIa&C[/url] '-----This sub is adapted form the one given above. The one above uses a freeform shape, but it is a '-----known bug (in the community, at least) that a freeform-generated shape will crash if the nodes '-----are too close together (and they will be in this case) '----- '-----When given a chart, it will take the indicated series and draw a filled-in polygon out '-----of the points in that series. Sub DrawFilledPolygon(myChart As Chart, seriesNum As Integer, fillColour As Integer, transVal As Double) Dim myPoly As Object Dim pointsSeries As Series Dim Npts As Integer, Ipts As Integer Dim myShape As Shape Dim Xnode As Double, Ynode As Double Dim Xmin As Double, Xmax As Double Dim Ymin As Double, Ymax As Double Dim Xleft As Double, Ytop As Double Dim Xwidth As Double, Yheight As Double '---Here we collect the various data we need to be able to position the polygon appropriately. With myChart Xleft = .PlotArea.InsideLeft Xwidth = .PlotArea.InsideWidth Ytop = .PlotArea.InsideTop Yheight = .PlotArea.InsideHeight Xmin = .Axes(1).MinimumScale Xmax = .Axes(1).MaximumScale Ymin = .Axes(2).MinimumScale Ymax = .Axes(2).MaximumScale Set pointsSeries = .SeriesCollection(seriesNum) End With Npts = pointsSeries.Points.count '---Set the first node. This creates the drawing at the specified point on the chart. '---I'm not entirely sure what this formula does (it was from the link) but it works so I'm not messing with it. Xnode = Xleft + (pointsSeries.XValues(Npts) - Xmin) * Xwidth / (Xmax - Xmin) Ynode = Ytop + (Ymax - pointsSeries.values(Npts)) * Yheight / (Ymax - Ymin) '---The following creates the polygon. You add vertexes to change the shape. The issue here is that the polygon is '---assumed to be "finished" form the moment of creation, so it is regenerated every time you add a vertex. '---I am assuming that using the FreeformBuilder would be superior in this regard. '---I also had a hard time finding documentation for this method, so don't really know what the parameters represent. Set myPoly = myChart.Drawings.Add(Xnode, Ynode, Xnode, Ynode, False) '---add the rest of the nodes/points in the series. For Ipts = 1 To Npts - 1 Xnode = Xleft + (pointsSeries.XValues(Ipts) - Xmin) * Xwidth / (Xmax - Xmin) Ynode = Ytop + (Ymax - pointsSeries.values(Ipts)) * Yheight / (Ymax - Ymin) myPoly.AddVertex Xnode, Ynode Next '---Apply fill effects With myPoly .ShapeRange.Fill.Visible = msoTrue .ShapeRange.Fill.Solid .ShapeRange.Fill.ForeColor.SchemeColor = fillColour .ShapeRange.Fill.Transparency = transVal .ShapeRange.Line.Visible = msoFalse 'makes the line invisible ' .ShapeRange.Line.ForeColor.SchemeColor = fillColour ' .ShapeRange.Line.Style = msoLineSingle ' .ShapeRange.Line.Weight = 2 End With End Sub
I've attached a before and after picture below. The transparency should be lower and I should have drawn outline shapes on top, but I didn't.
I've marked this thread as resolved because my question WAS resolved - I found a method to indicate areas of overlap, and it WAS too unruly to implement seriously. Although if anyone reading has any questions, feel free to bump up the thread to ask.
Last edited by nanoinfinity; Jul 2nd, 2009 at 12:40 PM.
-
Mar 11th, 2017, 06:50 PM
#4
Lively Member
Re: [RESOLVED] [Excel]Is it possible to fill in areas of overlap in an excel graph?
please sample file workbook excel of this chart.
-
Mar 11th, 2017, 07:41 PM
#5
Re: [RESOLVED] [Excel]Is it possible to fill in areas of overlap in an excel graph?
as this is from 8 years ago, you would be pretty lucky to get a response from the OP
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|