Re: Placing shapes exactly on charts.

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

The Plot area includes all tick-label properties that exist on the "outside"
of the axes. In effect the Plot-area includes an outer area and inner area.
For your purposes I take it you are looking for the co-ordinates of the
"inner" plot-area. Although you think you have determined the Top of the
plot area, that's probably only because you do not have text in the topmost
ticklabel that extends above the "inner" plot area (try aligning the
ticklabels!).

Unfortunately the co-ord's for this "inner" section are not exposed to VBA.
However, typically it's possible to infer the area from the coordinates of
the X & Y axes. I posted the following about a month ago in the
excel.programming group, the objective was to position data labels.

Create an XY scatter, make a straight line with XY values 0:0, 1:1 etc. Add
data-labels to the line. Ensure minimum scales are at zero and the axes
intersect bottome left corner (as is typical). Now drag some data labels
away from their default positions. Run the following to re-align (in
theory!) top-left corners of the labels to the points.

Option Explicit
Sub test()
Dim i As Long
Dim cht As Chart
Dim sr As Series
Dim aX As Axis, aY As Axis
Dim x0 As Single, y0 As Single, xP As Single, yP As Single
Dim xf As Double, yf As Double
Dim arrX, arrY

Set cht = ActiveSheet.ChartObjects(1).Chart
Set aX = cht.Axes(1)
Set aY = cht.Axes(2)

x0 = aY.Left + aY.Width
y0 = aX.Top + aX.Height

yf = y0 - aY.Top
xf = (aX.Left + aX.Width - x0)

xf = (aX.Left + aX.Width - x0) / (aX.MaximumScale)
yf = (y0 - aY.Top) / aY.MaximumScale

Set sr = cht.SeriesCollection(1)
With sr
arrX = .XValues
arrY = .Values
For i = 1 To .Points.Count
xP = x0 + arrX(i) * xf
yP = y0 - arrY(i) * yf
With .DataLabels(i)
.Left = xP
.Top = yP
End With
Next
End With

End Sub

This is example is highly limited in that it assumes axes intersect at the
bottom left of the plot and the minimum scale for both is zero, IOW it's
only for ideas. I have though adapted the above to cater for more scenarios
- Return an array of arrays of XY point co-ord's in all series
- The Intersection of XY axes can be anywhere (ie not bottom left corner)
- Primary axes can be on the "other" side of the chart
- minimum axes scale not zero
- columns (ie points) between or over category labels

Post back if interested (or off-line), though you'd still need to adapt for
your purposes.

Regards,
Peter T


"Taylor" <taylor.bryant@xxxxxxxxx> wrote in message
news:89874b04-fd83-46af-829f-8f4d3b3d27cf@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hello. I am Using Excel 2002 create a bar graph, which is linked to a
word file (Paste Special>Linked Microsoft Excel Object, left at 100%
scale), which is subsequently printed as postscript file, which is
rendered into a pdf by Acrobat Distiller. The word and excel files are
templates, used to generate many reports with different data
populating the graphs.

I would like to programatically align some vertical lines (category
dividers that go from the top of the PlotArea, through the category
axis, to midway through the vertically oriented category labels) to
the Tickmarks on the x-axis.

(see http://tinyurl.com/6qkgbn [jpg, 129K])

Problems abound, as you can guess. My only priority is that the lines
are not offset in the final pdf, doesn't matter how they appear in
Word or Excel.

I don't seem to have any problem aligning the lines with the top of
the PlotArea. But I do have problems aligning them with the tickmarks.

One potential solution - using Addline to create my own tickmarks,
(and turning off the axis tickmarks) - means I can align the tickmarks
perfectly with my dividing lines, but now I cannot consistently align
the tickmarks with the axis (sometimes they end up floating above,
sometimes below); and aligning them with the left and right edges of
PlotArea - fuhgeddaboutit.

A Rambling List of Issues:

* can't place lines exactly where I want - placement of line:

shp.Left = cht.PlotArea.InsideLeft
results in shp.Left being placed almost at cht.PlotArea.InsideLeft ,
but never quite on the dot. (Say, at 49.94323 instead of 50)

* The zoom percentage of the chart window affects the apparent
coordinates of the chart. My policy has been to leave this at 100%.
But interestingly if I zoom in to 300% and then back out to 100% that
can change the final appearance of the dividing lines/tickmarks.

Ugh. It goes on.

Thanks for any suggestions.

Taylor


.

Relevant Pages

  • Re: Placing shapes exactly on charts.
    ... Don't forget the demo as posted only works if the minimum scale for both ... axes is zero and the axes intersect at the bottom left corner, ... intersect bottome left corner. ... Dim cht As Chart ...
    (microsoft.public.excel.charting)
  • Re: How to make two charts look exactly the same?
    ... inside plot area (defined by the axes), even if the "regular" plot area ... > do is just create one chart that is based on a named range, ...
    (microsoft.public.excel.charting)
  • XY graph axis bug? LV 8.2, Win XP
    ... LV 8.0.1 used to automatically take care of resizing the plot area when axes were added, but I heard that was changed that in 8.2. ...
    (comp.lang.labview)
  • JFreeChart, equal axes
    ... I have an XYPlot where I want the x and y axes on the same ... scale, ... methods to catch the plot area argument, but I think this includes axes ...
    (comp.lang.java.programmer)