Placing shapes exactly on charts.

[Taylor]

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

 

[Peter T]

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

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

 

[Taylor]

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

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

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.

(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

 

[Peter T]

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


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

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

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.

(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

 

[Taylor]

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>

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


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

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 coordinatesof
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

news:89874b04-fd83-46af-829f-8f4d3b3d27cf@n33g2000pri.googlegroups.com....

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

 

[Taylor]

'Paste this beeyotch into the VBE, it should be clearer that way
'I have a sneaking suspicion this is only useful to me...
'but FWIW, I am trying to make this:
'(Crappy-fixed-width-or-it's-nonsensical-ascii art follows;
' Paste into your favorite Osborne emulator to see as intended)
'__________________________________
' CHART TITLE '
' Grp A Grp B Grp C '
' _____________________________ '
' | | | | '
' | | * | | '
' | | * * | | '
' | * | * * | * | '
' | * * | * * * | * | '
' | * * | * * * | * * | '
' '---'---|---'---'---|---'---' '
' a a | b b b | c c '
' 1 2 | 1 2 3 | 1 2 '
' ' ' '
' [cht legend goes here] '
'_________________________________'
'
' and damned if it wasn't a chore getting line objects
' (the vertical lines dividing the subgroups of data A, B and C)
' to line up with the tickmarks that help differentiate categories
' (a1,a2)(b1,b2,b3),(c1,c2)
' http://img523.imageshack.us/my.php?image=clipboard02copyka0.gif
' shows you the tiny little BS all this comes from, it may seem
inconsequential
' but I felt it was unacceptable and unprofessional to put out a
product like this.
'
' So the main point of this:
' I'm using the exact same methods, variables, values
' To draw and place my home-brewed tick marks
' and my dividing lines; so it doesn't matter if I tell Excel to place
a line on 30
' and it places it on 29.972 (check it out sometime, it's brilliantly
frustrating
' because when I place my tickmarks, it will also land on 29.972,
lining up exactly
' with the mark, instead of having to eyeball it everytime
' (and eyeballing it in Excel, especially zoomed, will get you
nowhere, there's some scary
' quantum mechanics type stuff going on; just the act of observing
the line, zoomed,
' changes the placement of the line in the final pdf document. So
instead, lining them up means
' Tweak the line, save the chart, update the link in word, print the
word document to postscript file
' Use Acrobat distiller to make a pdf, open the pdf, zoom to 600%,
see if the divider lines up with your tickmarks
' No? Start over. Repeat until perfect. Now do this for all forty
charts.
' What's that? You need to add a category to the chart? Please start
over from the beginning.
' What's that? You adjusted the size of the plot area? Please start
over from the beginning.

Option Explicit 'Indicates variable naming convention includes
probable usage of profanity

Dim divLineOrigHorizPos() As Double
Dim divLineHorizontalPos As Double
Dim tckMkHorizontalPos As Double

'The following are all variables to store various chart coordinates,
not sure I even use them all
Dim chtCategoryCount As Double, chtCategorySpacing As Double
'Oh , this just in: I assume a legend placement on the bottom
Dim chtLegendHeight As Double, chtLegendLeft As Double, chtLegendWidth
As Double, chtLegendTop As Double
'More coordinate variables; the "g" prefix was my way of
distinguishing between the normal and the ExecuteExcel4Macro
~~~G~~~ET.CHART.ITEM
'coordinates, while I was experimenting with and comparing both (they
were often 'off' by a half a point)
'Also, interestingly, the .Height/.Width/.Left/.Top methods always
returned an integer, thought that was a bit peculiar
Dim gchtPlotAreaInsideLeft As Double, gchtPlotAreaInsideWidth As
Double, gchtPlotAreaInsideRight As Double
Dim gchtPlotAreaInsideTop As Double, gchtPlotAreaInsideHeight As
Double, gchtPlotAreaInsideBottom As Double
Dim gchtChartAreaLeft As Double, gchtChartAreaWidth As Double,
gchtChartAreaTop As Double, gchtChartAreaHeight As Double


Dim cht As Chart
Dim origChtName As String
Dim shp As Shape

Dim n As Integer 'Dividing Positions
'A chart with 3 category labels has 4 dividing
positions -
'Left side of PlotArea, between 1&2, 2&3 and
Right side of PlotArea
Dim nTot As Integer 'Total number of dividing positions
Dim s As Integer
Dim d As Integer 'Dividing Lines
Dim dTot As Integer 'Total number of dividing lines

Dim tckMkName As String
Dim divLineName As String



Sub zAddTicksAndDividingChartLines()

'I work with chart sheets, not charts on worksheets,
'I'm sure you'd need to tweak this if that's your chart MO
origChtName = ActiveChart.Name 'You have the chart you're working on
already selected
Set cht = Charts(origChtName)
cht.PageSetup.Zoom = 100 'This is some superstitious voodoo to ward
off unpredictable PointsToPixels black magic

cht.Axes(xlCategory).MajorTickMark = xlNone 'Turn 'em off, they'll
only confuse you
cht.Axes(xlCategory).MinorTickMark = xlNone

chtCategoryCount = cht.SeriesCollection(1).Points.Count ' number of
categories; tick marks and dividers fall in between them
chtLegendHeight = cht.Legend.Left 'could have done this with
ExecuteExcel4Macro("GET.CHART.ITEM(1, 7,""Legend"")"), got lazy
chtLegendLeft = cht.Legend.Height 'especially since these are not
vital to my accuracy, I use them to estimate
chtLegendWidth = cht.Legend.Width 'an approximate appropriate length
for my dividing lines
chtLegendTop = cht.Legend.Top
chtLegendHeight = cht.Legend.Height

'Measures from lower Left Corner!!! (Meaning Y-values must be
converted)
gchtChartAreaLeft = ExecuteExcel4Macro("GET.CHART.ITEM(1,
7,""Chart"")") ' (1,7 = Horizontal Coordinate, Lower Left)
gchtChartAreaWidth = ExecuteExcel4Macro("GET.CHART.ITEM(1,
5,""Chart"")")
' don't have to convert, since it's the following two, though they are
y-values, because they are total measurements of chart size
gchtChartAreaTop = ExecuteExcel4Macro("GET.CHART.ITEM(2,
7,""Chart"")") ' (2,7 = Vertical Coordinate, Lower Left)
gchtChartAreaHeight = ExecuteExcel4Macro("GET.CHART.ITEM(2,
1,""Chart"")") '(2,1 = Vertical Coordinate, Upper Left)'don't have to
convert
'Incidentally, if this had not worked, I would've used
ExecuteExcel4Macro("GET.CHART.ITEM... to return the values of
individual plot objects
' and then calculated the middle of them by averaging;
'Find the XY position of the middle top of the third column
'in the data series,
'returned in XLM coordinates
'tckMk3 = (ExecuteExcel4Macro("GET.CHART.ITEM(1,2,""S1P3"")") +
ExecuteExcel4Macro("GET.CHART.ITEM(1,2,""S1P4"")"))/2
'(Series one point 3, horizontal center position)+(series one point 4,
horizontal center)/2 is where the tick should be



'~~~~~~~~~~~~~~~~~~~~~~~
'Notice that "-2" and -"1" snuck in there? That's where you still end
up doing some guesswork and manual tweaking
'"-2" (points) is what it took to exactly line up my first tickmark
with the Y-Axis in practice
'I suppose I could create variables for these tweaks,
'Dim wysiwygMyAss1 As Double, wysiwygMyAss2 As Double
gchtPlotAreaInsideLeft = ExecuteExcel4Macro("GET.CHART.ITEM(1,
7,""Plot"")") - 2 ' (1,7 = Horizontal Coordinate, Lower Left)
gchtPlotAreaInsideRight = ExecuteExcel4Macro("GET.CHART.ITEM(1,
5,""Plot"")") - 1 ' (1,5 = Horizontal Coordinate, Lower Right)
gchtPlotAreaInsideWidth = gchtPlotAreaInsideRight -
gchtPlotAreaInsideLeft
'~~~~~~~~~~~~~~~~~~~~~~~

'Convert the XLM coordinates to Drawing Object coordinates
'The x values are the same, but the Y values need to be
'flipped converting to make measurements originate from upper left
which matches VBA measurements
gchtPlotAreaInsideBottom = gchtChartAreaHeight -
ExecuteExcel4Macro("GET.CHART.ITEM(2, 7,""Plot"")") + 1 '+ 0.75 ' (2,7
= Vertical Coordinate, Lower Left)
gchtPlotAreaInsideTop = gchtChartAreaHeight -
ExecuteExcel4Macro("GET.CHART.ITEM(2, 1,""Plot"")") ' (2,1 = Vertical
Coordinate, Upper Left)
gchtPlotAreaInsideHeight = gchtPlotAreaInsideBottom -
gchtPlotAreaInsideTop

'divides the width of the chart, by the number of cats
'a 100 pt wide chart with ten categories will place your tickmarks
every 10 pts (in theory, look for minor adjustments)
chtCategorySpacing = (gchtPlotAreaInsideWidth) / chtCategoryCount


'The next are a series of functions to help keep this subroutine clean
'I like using "Call" it helps remind me I'm not looking at another
variable

'Dumb thing about this, gotta fix it:
'Should add something that makes a quick copy of the original chart
sheet
'And preserves it until you're certain everything went "as planned"
'Stopping in the middle to troubleshoot can mean losing your
divLineOrigHorizPos() data
'And your original dividing lines

'~~~~~~~~~~~~~~~~~~~~~~~
'1) properly label any dividing lines -
'If I have run the routine before, they'll be named divLineXX,
'where XX is the sequential number of the left-most category
'otherwise, if I have just designed the chart from scratched,
'they'll be renamed from the default Line1, Line2 etc. to my format
Call LabelDivLines
'~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~
'2) Count the number of dividers in the chart by calling this function
'and use that number to assign the proper # in the array
divLineOrigHorizPos()
'divLineOrigHorizPos is an array soon to be populated with the
horizontal (x-value)
'position of all the dividing lines, from 1 to dTot
'Why? I cannot accurately reposition a line (pixel to point weirdness
going on here?),
'but I can delete the old one and create a new one in a more exact (or
replicable) position
dTot = CountDivLines()
ReDim Preserve divLineOrigHorizPos(1 To dTot)
'~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~
'3) Clear old dividers from chart before redraw
Call DelOldDivLines
'~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~
'4) Clear any old 'tickmark' shapes from chart before redraw
Call DelOldTickMarks
'~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~
'5) Add new 'tickmark' shapes
Call AddTickMarks
'~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~~~~~~~~~~~
' 6) Add new dividers
Call AddDividingLines
'~~~~~~~~~~~~~~~~~~~~~~~

End Sub


Function LabelDivLines() As Integer
'In case we haven't named our dividers yet, we go through and rename
n = 50 ' I never have this many real lines, so this is a way to avoid
duplicate naming with my temporarily labeled lines;
'They will be relabeled later with respect to their relative category
divider position.

For Each shp In cht.Shapes
If Left(shp.Name, 4) = "Line" Then 'Anything named "Line"
If Abs((gchtPlotAreaInsideTop) - shp.Top) < 4 Then 'with the top
near the top of the PlotArea
If shp.Height > gchtPlotAreaInsideHeight Then 'with a height
greater than the PlotArea
shp.Name = "divLine" & n 'Is renamed as "divLine" & a
dummy number
n = n + 1
End If
End If
End If
Next shp

n = 0
End Function
Function CountDivLines() As Integer
'Counts the number of dividers
'Dividers are the long vertical lines to divide the category axis into
sub-categories
dTot = 0

For Each shp In cht.Shapes 'Get total of all the dividing for a given
chart
If Left(shp.Name, 7) = "divLine" Then
dTot = dTot + 1
End If
Next shp

CountDivLines = dTot

End Function

Function DelOldDivLines()
'Remove old Dividing lines before redraw
'But note their horizontal position in the array divLineOrigHorizPos()
'So they can be recreated later
d = 1

For Each shp In cht.Shapes
If Left(shp.Name, 7) = "divLine" Then
divLineOrigHorizPos(d) = shp.Left
d = d + 1
shp.Delete
End If
Next shp

End Function

Function DelOldTickMarks()
' If I were to get my act together, I'd select all the tckMks
' and add them into one group; easier to delete, easier to manipulate/
adjust
For Each shp In cht.Shapes
If Left(shp.Name, 5) = "tckMk" Then
shp.Delete
End If
Next shp

End Function

Function AddTickMarks()

For n = 0 To chtCategoryCount ' n = 0 is tick under y-axis;
chtCategoryCount is rightmost tick

' I like it when things will alphabetize,
' This way if I ever use the vars in a list, it goes
' tckMk01, tckMk02 ... tckMk09, tckMk10, tckMk11
' instead of
' tckMk1, tckMk10, tckMk11 ... tckMk19, tckMk2, tckMk20, tckMk21
tckMkName = "tckMk" & Right(n + 100, 2)

' chtCategorySpacing is that 10 pt width of each category
' So if we are at the 6th divider, the tickmark would be located at
' 60 points to the right of the leftmost edge of the PlotArea
tckMkHorizontalPos = ((chtCategorySpacing * n) +
gchtPlotAreaInsideLeft) 'n=0 is the leftmost tickmark, cancels out

'seemed easiest to name it right here, so I could refer to it easily
ActiveChart.Shapes.AddLine(tckMkHorizontalPos,
gchtPlotAreaInsideBottom, _
tckMkHorizontalPos,
gchtPlotAreaInsideBottom + 3).Name = tckMkName
' "+3" was a good facsimile of the length of a regular tickmark (for
my particular chart size)
' but if I wanted to help separate my categories better
'(I usu. use vertically oriented text)
' I'd go ahead and make it longer

cht.Shapes(tckMkName).Placement = xlMove
With cht.Shapes(tckMkName).Line
.DashStyle = msoLineSolid
.Style = msoLineSingle
.Weight = 0.75
.ForeColor.RGB = RGB(0, 0, 0)
End With
Next n

End Function

Function AddDividingLines()
Dim divLineHeight As Double
Dim divLineName As String
'
'Decided that about 3/4 of the way from the bottom of the PlotArea to
the top of the chart legend
'was a good rule of thumb for the height of the line (added to the
height of the plot area)
divLineHeight = ((chtLegendTop - gchtPlotAreaInsideBottom) * 0.75) +
gchtPlotAreaInsideBottom


For d = 1 To dTot
For n = 0 To chtCategoryCount 'would not likely have dividers on
the edges of the plotarea, but it could happen
divLineHorizontalPos = ((chtCategorySpacing * n) +
gchtPlotAreaInsideLeft)

'here's where we guess which category the provisional
dividing line was supposed to belong to.
'in our hypothetical chart, I would have needed to place the
line correctly
' with a +/- 3 pt margin of error, which is reasonably
generous
If Abs(divLineHorizontalPos - divLineOrigHorizPos(d)) <
(chtCategorySpacing / 3) Then
divLineName = "divLine" & Right(n + 100, 2)
ActiveChart.Shapes.AddLine(divLineHorizontalPos,
gchtPlotAreaInsideTop, _
divLineHorizontalPos,
divLineHeight).Name = divLineName

ActiveChart.Shapes(divLineName).Placement = xlMove 'hate
anything that tries to "Size with Chart" - nothing will ruin your day
faster
n = chtCategoryCount
End If
Next n
Next d

End Function

 

[Taylor]

http://img523.imageshack.us/img523/1294/clipboard02copyka0.gif

try that instead

 

[Peter T]

I gave up trying to unwrap and follow the code, but if I follow the
objective is simply to draw some vertical lines to intersect some of your
X-ticks, kind of customized vertical gridlines.

From what I could follow the code appears to make some arbitrary assumptions
in places, I doubt that's necessary. I also doubt it's necessary to use the
old XLM which would limit to the Activechart only. I should qualify that by
saying I haven't followed all you are doing, however my routine to find
co-ords of all series points (irrespective of axes positioning & scales)
which if anything I would think requires more work.

Re your comments about "accuracy", drawing for viewing on a monitor can
never be better than to within one pixel, which typically means to 0.75
points. That shouldn't be a problem, though rounding can throw things off by
a pixel or two. Note the chart may be redrawn for printing and quite how may
depend on your printer settings. If your objective is to get your chart into
a PDF it might be worth first copying the chart as an image (Shift Edit
copypicture..., etc), perhaps experiment.

Regards,
Peter T