Hi Peter, thanks for your help. Now allow me to post the ridiculously
sloppy, no-error-handlin' havin' poorly-annotated code that I ended up
using. (Actually, I'll explain it here, and post it after).
I felt like I was getting better accuracy by using the old Excel macro
language; it's not too pretty, but ultimately felt a smidge more
accurate (or at least that's what I was using when it all came
together. It wasn't perfect, so I ended up eyeballing a few constants
here and there to add or subtract a point or two from some
measurements. Some of this I could mentally justify by imagining it
had to do with accounting for the line thickness.
Points to keep in mind, in no particular order:
I am using charts with no category labels (I am actually using data
labels for a series that lines up with my y-axis minimum; this gives
me much better formatting control over the labels and where they break
and how they line up, etc.) So I may be getting slightly different
sizes for PlotArea than if I had labels also.
I decided the best way to get these lines to measure up with tickmarks
was to make my own tickmarks, too.
I think by programatically setting the zoom first, it helped keep some
of the more unexpected effects at bay. Especially what felt like some
distressingly arbitrary placement of the y-values when I printed in
Acrobat distiller.
Also note, that this already assumes the presence of (can be sloppily
drawn) dividing lines. Please eyeball the pic I posted. I suppose it
would be simple to code a little dialog that asks where you want 'em,
too.
Just because someone might find it useful, someday:
<see next post
On Aug 12, 2:48*pm, "Peter T" <peter_t@discussions wrote:
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, a limited
scenario.
However as I mentioned, I extended the basic idea to cover just about all
scenarios of axis scales, locations and intersections and it seemed fine.
As for zoom, things can change slightly, but simply running the code again
after changing zoom straightens everything out.
Regards,
Peter T
"Taylor" wrote in message
...
Using the chart axis is interesting, I have been making similar
calculations with PlotArea.InsideTop/InsideWidth etc. We come out with
the same value for xf, but I need to compare your yf with my
straightforward (and probably wrong) use of
chtPlotAreaInsideBottom = cht.PlotArea.InsideHeight +
cht.PlotArea.InsideTop 'InsideBottom
to calculate where my drawn tick marks should fall. That may be the
key.
Not sure still how the zoom factor affects everything, and how it all
finally looks in pdf format, but this gets me closer. Thanks!
Taylor
On Aug 8, 5:10 am, "Peter T" <peter_t@discussions wrote:
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" wrote in message
....
Hello. I am Using Excel 2002 create a bar graph, which is linked to a
word file (Paste SpecialLinked 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.
(seehttp://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