Height (in points) of data point in chart above x-axis

[Scott P]

Hi,

Is it possible using VBA to determine the height of a given data point in a
Chart worksheet in Excel above the x-axis in terms of points? I can use the
InsideHeight property in VBA to get the height of the plot area in points,
but I am really interested in determining the number of points above the
x-axis a data point sits.

Thank you in advance for any assistance with this matter.

 

[Jon Peltier]

Scott -

What are you trying to do? It may not be necessary to determine the
number of points to get the effect you want. There might be a native
feature you haven't yet discovered, or perhaps you need a trick that
someone else has already worked out.

But if you need the number of points, you can probably use some of the
code on the following web page to get started. The page shows ways to
use drawing objects to embellish a chart in ways that native chart
elements cannot be coerced to do.

http://peltiertech.com/Excel/Charts/VBAdraw.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Scott P]

Thank you for your reply and suggestion, Jon. I am trying to calculate the
aspect ratio of a chart in a Chart worksheet using the InsideWidth property
as the width of the chart and I would like to use the distance between the
max and min y-values in the series being charted as the height of the chart.

Are you aware of any way to determine the "height" of a data point in a
series above/below the x-axis given the y-value of that data point, as
measured in points? Thank you again.

 

[Jon Peltier]

It's all just 8th grade algebra, so go find an 8th grader.

Aspect ratio is just InsideWidth/InsideHeight.

Distance above axis, in points, get the Y value of the point, subtract the "X Axis
Crosses At" value, divide by the Y axis Maximum Scale - Minimum Scale, multiply by
Inside Height.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Scott P]

Thank you for your follow-up, Jon. I am familiar with the algebra/geometry
at work here. Perhaps I should have been more specific in my post -- I am
really just looking for a Property related to a Chart that returns the
vertical distance of a point to the x-axis.

I could not find such a Property so I will use the general approach as
outlined below. Thanks again.

 

[Jon Peltier]

Scott -

You couldn't find the property you were looking for, because it doesn't exist. Hence
the need for roundabout calculations.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______