[RESOLVED] [Excel]Is it possible to fill in areas of overlap in an excel graph?

[nanoinfinity]

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.

[nanoinfinity]

No ideas? Is it not do-able, then?

[nanoinfinity]

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.

'----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.

[xman2000]

please sample file workbook excel of this chart.

[westconn1]

as this is from 8 years ago, you would be pretty lucky to get a response from the OP