# Graphical area under a curve in excel

As part of this excel project I'm developing, I'm attempting to intelligently draw circles over a map image. Since, sadly, I don't see anyway to just have excel graph an equation, I've solved the circle equation for Y and have two separate columns which are the solutions.

Now, when I graph these two columns I just get the lines of the curves, but what I'd like is to fill the circles with a shaded region.

Is it possible to somehow fill the area under a curve? After some research I found some very hokey ways that work for just the positive side of the curve (http://peltiertech.com/WordPress/fill-below-an-xy-chart-series-xy-area-combo-chart/), but I'd like something that is a) more robust and b) works for the entire circle.

Any suggestions? I'm open to VBA solutions as well.
LVL 1
###### Who is Participating?

Commented:
Attached version trims the chart and should position them as perfect as is mathematically possible with forms on the Excel Chart. I think it's close enough for the naked eye, especially if you have prepared your charts relatively close to what it should be.

The button: in Excel, open the Control Toolbox menu (right-click menu bar to activate it) and draw a small button on your sheet. Right-Click on it and select View code.

Now you should see an empty sub in which you have to call the Chart procedures with the right arguments.

Private Sub CommandButton2_Click()

End Sub

The chart detection is a bit of trial and error.

To help you, you could use a test module like this, which you run from the worksheet which contains your charts

Sub DetectCharts()
Dim ms As Worksheet
Dim co As ChartObject
Set ms = ActiveSheet

For Each co In ms.ChartObjects
co.Chart.ChartArea.Interior.ColorIndex = 3
Application.Wait Now + TimeValue("00:00:02")
MsgBox "The red chart can be called as ChartObjects(" & co.Index & ") or as ChartObjects(""" & co.Name & """)"
co.Chart.ChartArea.Interior.ColorIndex = 2
Next co

End Sub
ChartMAgic--version-2-.xls
0

Commented:
VBA won't be able to change the intrinsic properties of the charts of Excel.

In my opinion the only way to achieve this is using Shapes through VBA, but I think it's a hell of a job to position it correctly.... I'll give it a quick look.

Could you show the sample worksheet?
0

Microsoft MVP ExcelCommented:
Maybe you could use a bubble chart instead of drawing a circle with an XY? You can exactly position the bubble with the XY coordinates and calculate the size of the bubble from your data. A bubble is easy to fill with color, transparency, etc

cheers, teylyn
0

Commented:
Good thinking teylyn, I like that approach.

Just for demonstration, not for points, attached a sample book showing 4 circles and X, Y & relative size data ranges.

One additional thing: you can change absolute size in the format Data series interface (right-click any bubble) on Tab 'Options' and changing the Scale Bubble size (in %) and other format option are also available (lto you to find them out).

Book4.xls
0

Author Commented:
I was super excited about this concept; it seems like a brilliant and simple solution. Perhaps I'm missing something, but...

I need the bubbles to be precisely defined. That is, I need to be able to have a bubble fill up the entire chart, or have a bubble be itty bitty. Specifically, I'm actually drawing these circles as an overly onto a map, so, if there is a way to indead of this "relative" feature to be able to actually say "the radius is X" then this would entirely solve my problem...

As it stands, I think I'm still stuck with trying to figure out how to fill in the circle. :-/

0

Author Commented:
"..a way 'instead'.." not indead... heh. Wish I could edit posts.
0

Microsoft MVP ExcelCommented:
Hmm, I see what you mean (both with the editing posts and with the failure of the bubble to fill an entire chart. Excel seems to have a size limit for the biggest possible bubble and will just resize the smaller ones if the bubble size of the biggest one is cranked up.

Maybe you'll need a VBA solution with shapes instead. VBA would need to grab the map dimensions, then position the shape above the map, and size it accordingly. Way over my head, unfortunately.

cheers, teylyn
0

Commented:
Working on a possible solution, I've been able to position a circle on the chart left top, tonight I'll try to finish it off.
0

Commented:
Attached what I could come up with.

It's a demo workbook.

It works with random data to fill up an X/Y chart with dots... but it has 8 defined circles in the green cells.

Clicking the button will generate 8 circles with the coordinates X/Y & Diameter according to the table on both Charts (one embedded chart, one Chart Sheet).

The button performs a cleanup first to get rid of the old circles (if you run this a second time, this will remove the old ones and keeps circles up to date with data).

The circles are embedded with the Chart (in other words, moving & scaling the Chart form on the sheet will not influence their position, but proportions might distort).

However, changis X or Y axis minimum & maximum values will result in the circles loosing their position on the right X/Y location, hence the need to redraw them using the ButtoN.

Have a look & play around if this can fulfill your needs.

The book was saved without any charts so you can see them popping up as you click the button.
ChartMAgic--version-1-.xls
0

Commented:
Just for quick viewing this thread, I also have pasted the code for both type of Charts below (not the button code)
``````Sub Chart_In_Worksheet(X As Double, Y As Double, Z As Double)
Dim mb As Workbook
Dim ms As Worksheet
Dim mCh As ChartObject
Dim mCA As ChartArea
Dim mPA As PlotArea
Dim diameter As Double
Dim mShape As Shape
Dim myX As Double, myY As Double, myZ As Double
Dim xScalar As Double, yScalar As Double, DiaScalar As Double
Dim DeltaX As Double, DeltaY As Double, xAxis0 As Double, xAxis1 As Double, yAxis0 As Double, yAxis1 As Double

Set mb = ThisWorkbook
Set ms = Worksheets("Sheet1")
Set mCh = ms.ChartObjects(1)
Set mCA = mCh.Chart.ChartArea
Set mPA = mCh.Chart.PlotArea

xAxis0 = mCh.Chart.Axes(xlCategory).MinimumScale
xAxis1 = mCh.Chart.Axes(xlCategory).MaximumScale
yAxis0 = mCh.Chart.Axes(xlValue).MinimumScale
yAxis1 = mCh.Chart.Axes(xlValue).MaximumScale
DeltaX = xAxis1 - xAxis0
DeltaY = yAxis1 - yAxis0
xScalar = DeltaX / mPA.InsideWidth
yScalar = DeltaY / mPA.InsideHeight
'Trim Chart to be perfectly square

DiaScalar = xScalar * 0.5 + yScalar * 0.5
myX = X / xScalar
myY = mPA.InsideHeight - Y / yScalar
myD = Z / DiaScalar

Set mShape = mCh.Chart.Shapes.AddShape(msoShapeOval, mPA.InsideLeft - myD / 2 + myX, mPA.InsideTop - myD / 2 + myY, myD, myD)
mShape.Fill.ForeColor.SchemeColor = 10
mShape.Fill.Transparency = 0.75
End Sub

Sub Chart_on_Own_Sheet(X As Double, Y As Double, Z As Double)
Dim mb As Workbook
Dim mCh As Chart
Dim mCA As ChartArea
Dim mPA As PlotArea
Dim diameter As Double
Dim mShape As Shape
Dim myX As Double, myY As Double, myZ As Double
Dim xScalar As Double, yScalar As Double, DiaScalar As Double
Dim DeltaX As Double, DeltaY As Double, xAxis0 As Double, xAxis1 As Double, yAxis0 As Double, yAxis1 As Double

Set mb = ThisWorkbook
Set mCh = Sheets("Circles")
Set mCA = mCh.ChartArea
Set mPA = mCh.PlotArea

xAxis0 = mCh.Axes(xlCategory).MinimumScale
xAxis1 = mCh.Axes(xlCategory).MaximumScale
yAxis0 = mCh.Axes(xlValue).MinimumScale
yAxis1 = mCh.Axes(xlValue).MaximumScale
DeltaX = xAxis1 - xAxis0
DeltaY = yAxis1 - yAxis0
xScalar = DeltaX / mPA.InsideWidth
yScalar = DeltaY / mPA.InsideHeight
'Trim Chart to be perfectly square

DiaScalar = xScalar * 0.5 + yScalar * 0.5
myX = X / xScalar
myY = mPA.InsideHeight - Y / yScalar
myD = Z / DiaScalar

Set mShape = mCh.Shapes.AddShape(msoShapeOval, mPA.InsideLeft - myD / 2 + myX, mPA.InsideTop - myD / 2 + myY, myD, myD)
mShape.Fill.ForeColor.SchemeColor = 10
mShape.Fill.Transparency = 0.75
End Sub
``````
0

Author Commented:
Thanks a lot for your extended effort calacuccia, I'll be looking over it today to confirm that it works!
0

Author Commented:
Calacuccia, your worksheet works beautifully in the supplied excel sheet.

However, as I try to transfer the logic onto my project I've run into some confusing problems.

First, how did you create that button on your excel sheet? For some reason I can't seem to emulate it excatly when I try.

Second, how do you know which number the chart is that you're working with? You say "ChartObjects(1)"

Well, I only have 1 chart on my worksheet, but nothing seems to be appearing, and eventually I'll want a couple of different Charts, so is there a quick way to check what my chart "number" is?

Sorry for my delay, and thanks much for all your hard work so far. Too bad there isn't a multiplier greater than A for once this issue is finally nailed down.
0

Author Commented:
After a more careful review of the sheet, it seems that the circles aren't actually drawing correctly... at least not as far as I can tell.

It's really close, definitely on the right track, but it's not 100% accurate.

I know that you say the graphical area needs to be trimmed to a perfect square, but even then the circles aren't aligning themselves properly (an easy check is to put a data point at what should be the origin of the circle should be, and then make the circle something easy to estimate, like 10,10,10).

Frustrating. Perhaps excel is just limited here, but it sure would be nice to make this work. Doesn't need to be perfect just close enough for the naked eye.
0

Commented:
The problem of the non-perfection comes from the trim area, I think. Haven't seen your first comment, I will look at both your comments during the day.
0

Author Commented:
On the code line

"xScalar = DeltaX / mPA.InsideWidth"

It says, "Run-Time error '16':
Error, expression too complex."

Very odd...
0

Commented:
lol, that's the first time I see this error.
Do you have this within my sample workbook?
0

Commented:
The error normally would occur when too many flloating point expressions are in a single expression.

Problem is: this is as simple an expression as you can get it: a = b/c

Did you not have this problem with the first sample? Because this line did not change between sample 1 & 2.
0

Commented:
Maybe this works.

Small additional refresh inside the loop:

Application.ScreenUpdating = True
mCh.Chart.Refresh
DoEvents

This might make your circles flicker a bit, but it should resolve the issue.

Attached workbook where this is implemented.
ChartMAgic--version-3-.xls
0

Author Commented:
No, I still get the same error... :-/
0

Commented:
Could you tell in which part of the macro you are when the error pops up?

This line is situated before the Do loop and inside the Do loop.
I'd be interested to know when it happens.
0

Author Commented:
It's inside the Do While Loop.

I'm wondering if there's some setting I can force which will lower the variable numbers or something.

I have no idea why it would matter but maybe rounding the values, or doing something like 'Option Explicit'...

Anyway, I'll keep looking on my end too.
0

Commented:
Maybe change
Do While xScalar <> yScalar And t > 0.002 And i < 100

to
Do While xScalar <> yScalar And t > 0.002 And i < 50

or even lower. This is just an iteration stop to prevent endless loops.

0

Author Commented:
Okay, all I did was wrap mPA.InsideWidth in a round and it worked fine:

xScalar = DeltaX / Round(mPA.InsideWidth, 20)

Sweet... Thanks much for your help. You definitely answered the question I started with and fully deserve the points.

Now to ask other ones... =]
0

Commented:
Could you tell me if this also solves the problem of the Error Expression too complex?

xScalar = CDbl(DeltaX / mPA.InsideWidth)

I opened another question for this issue, and I just got this suggestion of using CDbl().

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26447486.html#a33599173

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.