Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
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).
[blue]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")) [COLOR=green]' we now have bounding rectangle of the Shape ("chart 2" in this case) in absolute screen coordinates[/color]
[COLOR=green]'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[/color]
End Sub
Private Function GetShapeScreenRect(ByVal myShape As Shape) As RECT
Dim myWind As Window
Set myWind = Application.Windows(1) [COLOR=green]' Use Application window for origin offset, and for Zoom factor (which affects client coordinates used by Excel)[/color]
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 [COLOR=green]' 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[/color]
End Function[/blue]