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

  • Thread starter Thread starter jintao
  • Start date Start date
J

jintao

:mad: I am having a problem of using InsideLeft and InsideTop method o
a PlotArea of a PlotChart. It doesn't give out an exact value, alway
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/
_______
 
:( Here is my code to draw a diagonal line across the plotarea of th
chart, it does work but the Line does not align with the plotare
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 Su
 
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/
_______
 
Back
Top