Screen coordinates of Inside Top Left Corner of excel chart by vba

Question

Screen coordinates of Inside Top Left Corner of excel chart by vba

sd74 (Programmer)

(OP)

9 Jan 21 01:47

I am trying to look for a vba code which can find the screen coordinates of the inside top left corner of the chart in excel. Please help me out.

SkipVought (Programmer) · Answer 1 · 2021-01-09 16:55:35

Hi,

You have two Area Objects to choose from the...

ChartArea Object
PlotArea Object

They both have Top and Left Properties, but the PlotArea Object also has InsideTop and InsideLeft Properties.

https://docs.microsoft.com/en-us/office/vba/api/ex...)

BTW, if none of those are what you need, there's the ChartObject Object, that has Top and Left Properties. The ChartObject Object is how a chart on a sheet is defined as opposed to a chart sheet which is a Chart Object and as such has no Top or Left Properties.

FAQ707-4811: Charts & VBA

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

strongm (MIS) · Answer 2 · 2021-01-10 00:01:22

RE: Screen coordinates of Inside Top Left Corner of excel chart by vba

strongm (MIS) 10 Jan 21 00:01

But none of those are the screen coordinates ...

SkipVought (Programmer) · Answer 3 · 2021-01-10 04:54:13

Quote:
PlotArea.Top property (Excel)
05/09/2019

Syntax
Returns or sets a Double value that represents the distance, in points, from the top edge of the object to the top of row 1 (on a worksheet) or the top of the chart area (on a chart).

https://docs.microsoft.com/en-us/office/vba/api/ex...

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

strongm (MIS) · Answer 4 · 2021-01-10 15:21:19

That's still client coordinates, not screen coordinates, so there is additional calculation to do ...

strongm (MIS) · Answer 5 · 2021-01-21 16:11:25

I had a quiet moment, so, if the OP really was asking for absolute screen coordinates rather than client coordinates:

CODE

Option Explicit

Private Declare Function Rectangle Lib "gdi32" (ByVal hdc As Long, ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long) As Long
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

Private Type RECT
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type


Public Sub Main()
    Dim myRect As RECT
    myRect = GetShapeScreenRect(ActiveSheet.Shapes("Chart 2")) ' we now have bounding rectangle of the Shape ("chart 2" in this case) in absolute screen coordinates
    'Rectangle GetDC(0), myRect.Left, myRect.Top, myRect.Right, myRect.Bottom ' you will only see this rectangle briefly if at all if sheet has the focus since Excel will repaint the application window
End Sub


Private Function GetShapeScreenRect(ByVal myShape As Shape) As RECT
  Dim myWind As Window
  
  Set myWind = Application.Windows(1) ' Use Application window for origin offset, and for Zoom factor (which affects client coordinates used by Excel)
  With myShape
    GetShapeScreenRect.Left = PointsToPixels(.Left * myWind.Zoom / 100) + myWind.PointsToScreenPixelsX(0)
    GetShapeScreenRect.Top = PointsToPixels(.Top * myWind.Zoom / 100) + myWind.PointsToScreenPixelsY(0)
    GetShapeScreenRect.Right = PointsToPixels(.Width * myWind.Zoom / 100) + GetShapeScreenRect.Left
    GetShapeScreenRect.Bottom = PointsToPixels(.Height * myWind.Zoom / 100) + GetShapeScreenRect.Top
  End With
End Function

Private Function PointsToPixels(Points As Single) As Long
  PointsToPixels = Points * 96 / 72 ' 96 = magic number. In reality we should query system for actual pixels per (logical) inch. And should cater for the fact that this value may differ between horizontal and vertical
End Function

Come Join Us!

Posting Guidelines

Screen coordinates of Inside Top Left Corner of excel chart by vba