Inexact? InsideTop and InsideLeft Method of PlotArea (Excel 97)

I am having a problem of using InsideLeft and InsideTop method of a PlotArea of a PlotChart. It doesn't give out an exact value, always offset by a bit. Fairly annoying, is it a bug of Excel 97?

It might be the difficulty of measuring in points a rectangle that's internally
defined by the pixel density of the screen.

How do you mean it's offset? Offset from what? The plot area dimensions are measured
as the distances from the Chart Area top and left edges, which are offset from the
chart object's top and left edges.

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

jintao wrote:

> I am having a problem of using InsideLeft and InsideTop method of
> a PlotArea of a PlotChart. It doesn't give out an exact value, always
> offset by a bit. Fairly annoying, is it a bug of Excel 97?
>
>

Here is my code to draw a diagonal line across the plotarea of the chart, it does work but the Line does not align with the plotarea exactly. Quite annoying


Sub DrawDiag()
With Worksheets(1)

'Get Chart Size
ChartSizeX = .ChartObjects(1).Chart.PlotArea.InsideWidth
ChartSizeY = .ChartObjects(1).Chart.PlotArea.InsideHeight

'Get Chart Positiion
ChartOffsetX = .ChartObjects(1).Left
ChartOffsetY = .ChartObjects(1).Top
PlotAreaOffsetX = .ChartObjects(1).Chart.PlotArea.InsideLeft
PlotAreaOffsetY = .ChartObjects(1).Chart.PlotArea.InsideTop

ChartPosX = ChartOffsetX + PlotAreaOffsetX
ChartPosY = ChartOffsetY + PlotAreaOffsetY

ChartEndX = ChartSizeX + ChartPosX
ChartEndY = ChartSizeY + ChartPosY

With .Shapes.AddLine(ChartPosX, ChartPosY, ChartEndX, ChartEndY).Line
.Parent.Name = "line"
End With

End With
End Sub

Couple points:

1. In the VB Editor, select Options from the Tools menu, and on the Editor tab,
check Require Variable Declarations. This puts "Option Explicit" at the top of your
modules, and forces you to declare (Dim) your variables.

2. Your code is adding a line to the worksheet, not to the chart. THis makes
positioning even more difficult. My code below adds the line to the chart within the
chart object, so at least it moves with the chart (but won't resize properly).

Sub DrawDiag()
Dim PlotAreaOffsetX As Single
Dim PlotAreaOffsetY As Single
Dim ChartSizeX As Single
Dim ChartSizeY As Single

With Worksheets(1).ChartObjects(1).Chart

'Get Chart Size
ChartSizeX = .PlotArea.InsideWidth
ChartSizeY = .PlotArea.InsideHeight

'Get Chart Position
PlotAreaOffsetX = .PlotArea.InsideLeft
PlotAreaOffsetY = .PlotArea.InsideTop

'Draw the line
With .Shapes.AddLine(PlotAreaOffsetX, PlotAreaOffsetY, _
PlotAreaOffsetX + ChartSizeX, PlotAreaOffsetY + ChartSizeY)
.Line.Parent.Name = "line"
End With

End With
End Sub

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


jintao wrote:

> Here is my code to draw a diagonal line across the plotarea of the
> chart, it does work but the Line does not align with the plotarea
> exactly. Quite annoying
>
>
> Sub DrawDiag()
> With Worksheets(1)
>
> 'Get Chart Size
> ChartSizeX = .ChartObjects(1).Chart.PlotArea.InsideWidth
> ChartSizeY = .ChartObjects(1).Chart.PlotArea.InsideHeight
>
> 'Get Chart Positiion
> ChartOffsetX = .ChartObjects(1).Left
> ChartOffsetY = .ChartObjects(1).Top
> PlotAreaOffsetX = .ChartObjects(1).Chart.PlotArea.InsideLeft
> PlotAreaOffsetY = .ChartObjects(1).Chart.PlotArea.InsideTop
>
> ChartPosX = ChartOffsetX + PlotAreaOffsetX
> ChartPosY = ChartOffsetY + PlotAreaOffsetY
>
> ChartEndX = ChartSizeX + ChartPosX
> ChartEndY = ChartSizeY + ChartPosY
>
> With .Shapes.AddLine(ChartPosX, ChartPosY, ChartEndX,
> ChartEndY).Line
> .Parent.Name = "line"
> End With
>
> End With
> End Sub
>
>

Thank You Alot!!!!!!